Recipe 16.2. Using a Stored Function to Encapsulate a Calculation


Problem

A particular calculation to produce a value must be performed frequently by different applications, but you don't want to write out the expression for it each time it's needed. Or a calculation is difficult to perform inline within an expression because it requires conditional or looping logic.

Solution

Use a stored function to hide all the ugly details of the calculation and make it easy to perform.

Discussion

Stored functions enable you to simplify your applications because you can write out the code that produces a calculation result once in the function definition, and then simply invoke the function whenever you need to perform the calculation. Stored functions also enable you to use more complex algorithmic constructs than are available when you write a calculation inline within an expression. This section shows an example that illustrates how stored functions can be useful in these ways. Granted, the example is not actually that complex, but you can apply the same principles used here to write functions that are much more elaborate.

Different states in the U.S. charge different rates for sales tax. If you sell goods to people from different states and must charge tax using the rate appropriate for customer state of residence, tax computation is something you'll need to do for every sale. You can handle this with a table that lists the sales tax rate for each state, and a stored function that calculates amount of tax given the amount of a sale and a state.

To set up the table, use the sales_tax_rate.sql script in the tables directory of the recipes distribution. The sales_tax_rate table has two columns: state (a two-letter abbreviation), and tax_rate (a DECIMAL value rather than a FLOAT, to preserve accuracy).

The stored function, sales_tax⁠(⁠ ⁠ ⁠) can be defined as follows:

CREATE FUNCTION sales_tax(state_param CHAR(2), amount_param DECIMAL(10,2)) RETURNS DECIMAL(10,2) READS SQL DATA BEGIN   DECLARE rate_var DECIMAL(3,2);   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET rate_var = 0;   SELECT tax_rate INTO rate_var     FROM sales_tax_rate WHERE state = state_param;   RETURN amount_param * rate_var; END; 

The function looks up the tax rate for the given state, and returns the tax as the product of the sale amount and the tax rate.

Suppose that the tax rates for Vermont and New York are 1 and 9 percent, respectively. Try the function to see whether the tax is computed correctly for a sales amount of $100:

mysql> SELECT sales_tax('VT',100.00), sales_tax('NY',100.00); +------------------------+------------------------+ | sales_tax('VT',100.00) | sales_tax('NY',100.00) | +------------------------+------------------------+ |                   1.00 |                   6.00 | +------------------------+------------------------+ 

For a location not listed in the tax rate table, the function should fail to determine a rate and compute a tax of zero:

mysql> SELECT sales_tax('ZZ',100.00); +------------------------+ | sales_tax('ZZ',100.00) | +------------------------+ |                   0.00 | +------------------------+ 

Obviously, if you take sales from locations not listed in the table, the function cannot determine the rate for those locations. In this case, the function assumes a tax rate is 0 percent. This is done by means of a CONTINUE handler, which kicks in if a No Data condition (SQLSTATE value 02000) occurs. That is, if there is no row for the given state_param value, the SELECT statement fails to find a sales tax rate. In that case, the CONTINUE handler sets the rate to 0 and continues execution with the next statement after the SELECT. (This handler is an example of the kind of logic that you can use in a stored routine that is not available in inline expressions.)




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net