MySQL Stored Procedure Programming

Calling Stored Functions

A function can be called by specifying its name and parameter list wherever an expression of the appropriate data type may be used. To show how stored functions can be called, we’ll use the simple stored function shown in Example 10-6.

Example 10-6. Simple stored function

CREATE FUNCTION isodd(input_number int)
       RETURNS int
BEGIN
        DECLARE v_isodd INT;

        IF MOD(input_number,2)=0 THEN
                SET v_isodd=FALSE;
        ELSE
                SET v_isodd=TRUE;
        END IF;

        RETURN(v_isodd);

END ;

From the MySQL command line, we can invoke our simple stored function in a number of ways. Example 10-7 shows how to call the stored function from a SET statement and from a SELECT statement.

Example 10-7. Calling a stored function from the MySQL command line

mysql> SET @x=isodd(42);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 0    |
+------+
1 row in set (0.02 sec)

mysql> SELECT isodd(42)
    -> ;
+-----------+
| isodd(42) |
+-----------+
|         0 |
+-----------+

From within a stored procedure, we can invoke the function both within a SET clause and within a variety of flow control statements. Example 10-8 shows how to call a stored function from within a SET statement, as well as from an IF statement.

Example 10-8. Calling a stored function from within a stored procedure

SET l_isodd=isodd(aNumber);

IF (isodd(aNumber)) THEN
    SELECT CONCAT(aNumber," is odd") as isodd;
ELSE
    SELECT CONCAT(aNumber," is even") AS isodd;
END IF;

Programming languages support a variety of methods for calling a stored ...