MySQL Cookbook, 2nd Edition

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 ...