Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page