O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required