Database Reference
In-Depth Information
->
DECLARE avg FLOAT;
->
IF user IS NULL
->
THEN # average message size over all users
->
SET avg = (SELECT AVG(size) FROM mail);
->
ELSE # average message size for given user
->
SET avg = (SELECT AVG(size) FROM mail WHERE srcuser = user);
->
END IF;
->
RETURN avg;
->
END;
->
$$
Query OK, 0 rows affected (0.02 sec)
mysql>
delimiter ;
After defining the stored function, invoke it the same way as a built-in function:
mysql>
SELECT avg_mail_size(NULL), avg_mail_size('barb');
+---------------------+-----------------------+
| avg_mail_size(NULL) | avg_mail_size('barb') |
+---------------------+-----------------------+
| 237386.5625 | 52232 |
+---------------------+-----------------------+
9.2. Using Stored Functions to Encapsulate Calculations
Problem
A particular calculation to produce a value must be performed frequently by different
applications, but you don't want to write the expression for it each time it's needed. Or
a calculation is difficult to perform inline within an expression because it requires con‐
ditional or looping logic.
Solution
Use a stored function to hide the ugly details and make the calculation easy to perform.
Discussion
Stored functions enable you to simplify your applications. Write the code that produces
a calculation result once in a function definition, 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 illustrates how stored functions can be useful
in these ways. (Granted, the example is not
that
complex, but the principles used here
apply to writing much more elaborate functions.)
Different states in the US charge different rates for sales tax. If you sell goods to people
from different states, you must charge tax using the rate appropriate for customer state
