MySQL Stored Procedure Programming

Built-in Functions

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 SUM, COUNT, MIN, MAX, and AVG. MySQL accepts these functions within expressions, but they will return NULL as shown in Example 3-13.

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:

String functions

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.

Mathematical functions

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.

Date and time functions ...