Understanding Stored Routines

A stored procedure is a series of SQL statements stored in a MySQL database. For a frequently executed series of commands, this is a time-saving featureyou only need to execute the stored procedure.

A stored function is a stored routine that can return a value to SQL. A useful feature is the capability to define libraries of functions so that you can perform an operation on your database from any client program. Libraries of commonly used functions and procedures help reduce code duplication and make your systems easier to maintain.

Function Libraries

If you use multiple APIs to communicate with your database, you can execute the same function from each language without having to rewrite the function.

Creating a Stored Procedure

To create a new stored procedure, use the CREATE PROCEDURE command, followed by a procedure name and a list of SQL statements that form the procedure body.

The following is a very simple example. The procedure created, named yesterday, takes no arguments; it simply selects and displays the date and time precisely 1 day ago.

 mysql> CREATE PROCEDURE yesterday()     -> SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) as yesterday; Query OK, 0 rows affected (0.01 sec) 

To execute a procedure, use the CALL command. The following shows typical output from the yesterday procedure:

 mysql> CALL yesterday(); +---------------------+ | yesterday           | +---------------------+ | 2006-02-07 03:02:21 | +---------------------+ 1 row in set (0.00 sec) 

Note that both the procedure definition and the CALL command include a set of parentheses after the name of the procedure. The parentheses are used to pass optional arguments to a procedure, but they must be included even if no arguments are required.

The following example creates a new procedure named longdate that displays a given date value in a predefined format:

 mysql> CREATE PROCEDURE longdate (IN date DATE) as long_date     -> SELECT DATE_FORMAT(date, '%W %D %M %Y'); Query OK, 0 rows affected (0.00 sec) 

When you call this procedure, you must supply a date argument in the parentheses, as in the following example:

 mysql> CALL longdate('2006-05-03'); +------------------------+ | long_date              | +------------------------+ | Wednesday 3rd May 2006 | +------------------------+ 1 row in set (0.00 sec) 

Note that procedures do not return a value; to do that, you must use a function. Therefore, you cannot nest these two procedures (for example, to use yesterday as an argument to longdate). Both functions simply perform a SELECT statement, and their output is displayed in the mysql monitor.

The real power of stored routines lies in their capability to execute many different SQL commands in a single procedure call. When a procedure contains more than one SQL statement, you must use the BEGIN and END keywords around the procedure body.

The following example is a procedure that applies a given percentage increase to all the prices in the products table, and then displays the new average price for all products:

 CREATE PROCEDURE price_hike (increase FLOAT) BEGIN   UPDATE products   SET price = price * (1+(increase/100));   SELECT CONCAT('Average price is now ',                AVG(price)) as new_price   FROM products; END 

Before you attempt to create this procedure in MySQL, consider that the semicolon characters within the procedure are intended only to terminate the individual SQL statements, not the CREATE PROCEDURE command. This creates a conflict that must be worked around.

To keep the mysql program from treating the semicolons within a function as terminators for the CREATE PROCEDURE command, you must define a new terminator character using the delimiter, or \d, command.

Delimiter Characters

The delimiter can be any character or series of characters you choose, but you must make sure that it will not otherwise appear in the body of the CREATE PROCEDURE statement.

The following example sets the delimiter to // before creating the new procedure:

 mysql> delimiter // mysql> CREATE PROCEDURE price_hike (increase FLOAT)     -> BEGIN     ->   UPDATE products     ->   SET price = price * (1+(increase/100));     ->      ->   SELECT CONCAT('Average price is now ',     ->                 AVG(price)) as new_price     ->   FROM products;     -> END     -> // Query OK, 0 rows affected (0.00 sec) 

Using Delimiters

Bear in mind that after the delimiter has been redefined, you must use that character until you specify a new delimiter or begin a new mysql session.

Unless otherwise specified, the following examples in this lesson assume that ; is the current statement-terminator character.

Creating a Stored Function

To create a new stored function, use the CREATE FUNCTION command. You must provide a function name, a list of arguments, and the return value data type before giving the list of SQL statements that form the function body.

The following example shows how the yesterday procedure from the previous section can be implemented as a function:

 mysql> CREATE FUNCTION yesterday()     -> RETURNS DATE     -> RETURN date_add(NOW(), interval -1 day); Query OK, 0 rows affected (0.00 sec) 

Functions always return a value, so you must always give a return data type. The RETURN keyword is used to prefix a value or expression for the return value.

The return type for this function is set as DATE, so any SQL statement that calls the function will treat its result as a date value. Therefore, you can use the result from the yesterday() function as an argument to the longdate procedure.

 mysql> CALL longdate(yesterday()); +---------------------------+ | long_date                 | +---------------------------+ | Tuesday 7th February 2006 | +---------------------------+ 1 row in set (0.01 sec) 

Routine Names

Each procedure name must be unique, as must each function name. However, it is possible for a function and a procedure to share the same name: The yesterday() function was successfully created without dropping the yesterday procedure. Because functions are called from SQL statements and procedures are called using CALL, this never presents a conflict in your code. However, you should consider using unique names for all stored routines, to avoid confusion.

Most functions require an argument, specified in parentheses after the function name in the CREATE FUNCTION statement.

Only IN arguments are possible with a functiondata is naturally passed out of the function via the return valueso the IN keyword is not required in the argument list.

The following example creates a function named order_total() that takes an order_id argument and returns the total value of the items that make up that order:

 mysql> delimiter // mysql> CREATE FUNCTION order_total(id INT)     -> RETURNS DECIMAL(7,2)     -> BEGIN      ->   DECLARE total_price DECIMAL(7,2);     ->     ->   SELECT SUM(ol.quantity * p.price) INTO total_price     ->   FROM  order_lines ol, products p     ->   WHERE ol.product_code = p.product_code     ->   AND   ol.order_id = id;     ->     ->   RETURN total_price;     -> END     -> // Query OK, 0 rows affected (0.00 sec) 

You can then call order_total with a valid order_id value, as follows:

 mysql> SELECT order_total(5); +----------------+ | order_total(5) | +----------------+ |         259.80 | +----------------+ 1 row in set (0.00 sec) 

Using Variables

The DECLARE instruction in the order_total() function declares a variable named total_price, defined as type INT.

You can use variables to store the result from a SQL queryin order_total(), the result of the query was assigned by a query using SELECT ... INTOor by using SET to assign a value to a variable.

Variables declared within a stored routine have local scope only and cannot be referenced outside the particular procedure or function in which they exist. In order_total(), the variable was required only to return the result of the query from the function.

MySQL also supports session variables, which have global scope for the duration of the current MySQL connection. Session variables do not need to be declared; their names are simply prefixed with the @ symbol whenever they are required.

The following example creates a procedure named store_time that assigns the current time into the session variable @time:

 mysql> CREATE PROCEDURE store_time()     -> SET @time=NOW(); Query OK, 0 rows affected (0.00 sec) 

After you have called store_time in a MySQL session, you can use @time in any SQL statement to reference this value. This variable retains the time that the store_time procedure was last called:

 mysql> CALL store_time(); Query OK, 0 rows affected (0.00 sec) 

 mysql> SELECT @time; +---------------------+ | @time               | +---------------------+ | 2006-02-08 20:25:12 | +---------------------+ 1 row in set (0.00 sec) 

If you disconnect from MySQL and reconnect, the values of any session variables you have set up are lost. The value of @time will be NULL if you have not called store_time in the current session.

Getting Information About Stored Routines

To view the stored procedures and functions in a database, use the SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS commands, respectively. The following example output (in long query format) shows the two sample functions created in the previous section:

 mysql> SHOW FUNCTION STATUS\G ********** 1. row **********            Db: mysql10          Name: order_total          Type: FUNCTION       Definer: root@localhost      Modified: 2006-02-08 17:11:20       Created: 2006-02-08 17:11:20 Security_type: DEFINER       Comment: ********** 2. row **********            Db: mysql10          Name: yesterday           Type: FUNCTION        Definer: root@localhost      Modified: 2006-02-08 17:11:20       Created: 2006-02-08 17:11:20 Security_type: DEFINER       Comment: 2 rows in set (0.00 sec) 

Database-Level Routines

Stored routines exist at the database level, and each routine is associated with a particular database on the MySQL server.

You can call a procedure or function from a database other than the current one by prefixing the routine name with the database name. Any SQL executed by the routine will be performed on the database to which that routine belongs.

To view the body of a procedure or function, use the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION commands. The following shows a sample output:

 mysql> SHOW CREATE FUNCTION order_total \G *************************** 1. row ***************************        Function: order_total        sql_mode: Create Function: CREATE FUNCTION `order_total`(id INT) RETURNS decimal(7,2) BEGIN     DECLARE total_price DECIMAL(7,2);     SELECT SUM(ol.quantity * p.price) INTO total_price     FROM  order_lines ol, products p     WHERE ol.product_code = p.product_code     AND   ol.order_id = id;       RETURN total_price;     END 1 row in set (0.00 sec) 

Dropping a Stored Routine

To drop a stored routine from the database, use the DROP PROCEDURE and DROP FUNCTION commands. As with other DROP commands, you can include the IF EXISTS keywords to avoid an error if you attempt to drop a function that does not exist.

The following statement drops the yesterday procedure, if it exists:

 mysql> DROP PROCEDURE IF EXISTS yesterday; Query OK, 1 rows affected (0.00 sec) 

Privileges and Stored Routines

Stored procedures require the presence of the proc table in the mysql database. This system table is created during installation of MySQL 5.0. If you upgrade from an earlier version of MySQL, you must update the grant tables using the mysql_fix_privilege_tables script.

To create a new routine, a user needs the CREATE ROUTINE privilege. To modify or delete a routine, a user must have the ALTER ROUTINE privilege. These privileges always apply to both procedures and functions.

A user must have the EXECUTE privilege to execute a stored procedure. As the creator of a stored routine, you have this privilege automatically. However, you grant other users access to your routine manually.

You must indicate in the GRANT command whether the routine is a procedure or a function. The following statement allows the user chris@localhost to execute the order_total() function:

 mysql> GRANT EXECUTE ON FUNCTION order_total     -> TO chris@localhost; Query OK, 0 rows affected (0.00 sec)