Database Reference
In-Depth Information
of residence. To handle tax computations, use a table that lists the sales tax rate for each
state, and a stored function that looks up the tax rate given a state.
To set up the
sales_tax_rate
table, use the
sales_tax_rate.sql
script in the
tables
di‐
rectory of the
recipes
distribution. The table has two columns:
state
(a two-letter
abbreviation), and
tax_rate
(a
DECIMAL
value rather than a
FLOAT
, to preserve accuracy).
Define the rate-lookup function,
sales_tax_rate()
, as follows:
CREATE
FUNCTION
sales_tax_rate
(
state_code
CHAR
(
2
))
RETURNS
DECIMAL
(
3
,
2
)
READS
SQL
DATA
BEGIN
DECLARE
rate
DECIMAL
(
3
,
2
);
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
rate
=
0
;
SELECT
tax_rate
INTO
rate
FROM
sales_tax_rate
WHERE
state
=
state_code
;
RETURN
rate
;
END
;
Suppose that the tax rates for Vermont and New York are 1 and 9 percent, respectively.
Try the function to check whether the tax rate is returned correctly:
mysql>
SELECT sales_tax_rate('VT'), sales_tax_rate('NY');
+----------------------+----------------------+
| sales_tax_rate('VT') | sales_tax_rate('NY') |
+----------------------+----------------------+
| 0.01 | 0.09 |
+----------------------+----------------------+
If you take sales from a location not listed in the table, the function cannot determine
the rate for it. In this case, the function assumes a tax rate of 0 percent:
mysql>
SELECT sales_tax_rate('ZZ');
+----------------------+
| sales_tax_rate('ZZ') |
+----------------------+
| 0.00 |
+----------------------+
The function handles states not listed using a
CONTINUE
handler for
NOT
FOUND
, which
executes if a No Data condition occurs: if there is no row for the given
state_param
value, the
SELECT
statement fails to find a sales tax rate, the
CONTINUE
handler sets the
rate to 0, and continues execution with the next statement after the
SELECT
. (This handler
is an example of stored routine logic not available in inline expressions.
Recipe 9.10
discusses handlers further.)
To compute sales tax for a purchase, multiply the purchase price by the tax rate. For
example, for Vermont and New York, tax on a $150 purchase is:
mysql>
SELECT 150*sales_tax_rate('VT'), 150*sales_tax_rate('NY');
