You can use most of the functions that MySQL makes available for use in SQL statements within stored programs. These are fully documented in the MySQL reference manual, and we provide details and examples for most of these functions in Chapter 9. We'll also talk about how you can create your own "stored" functions in the MySQL stored program language in Chapter 10.
The functions that may be used in SQL but not in stored programs
are those involved in group (multiple-row) operators. These include
functions such as
AVG. MySQL accepts these
functions within expressions, but they will return NULL as shown in
Example 3-13. Aggregate functions in stored procedures return NULL
mysql> create procedure functions( ) begin DECLARE a int default 2; declare b int default 3; declare c FLOAT; SET c=SUM(a); select c; end; Query OK, 0 rows affected (0.00 sec) mysql> call functions( ); +------+ | c | +------+ | NULL | +------+ 1 row in set (0.00 sec)
MySQL functions fall into the following categories:
These functions perform operations on string variables. For example, you can concatenate strings, find characters within strings, obtain a substring, and perform other common operations.
These functions perform operations on numbers. For example, you can perform exponentiation (raise to a power), trigonometric functions (sine, cosine, etc.), random number functions, logarithms, and so on.