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