Table of Contents for

MySQL in a Nutshell, 2nd Edition

A procedure, also known as a stored procedure, is a set of SQL statements stored on the server and called as a unit, processing any data it’s given in its parameters. A procedure may communicate results back to the user by placing the values it generates in user variables that can then be retrieved in various ways.

The basic, minimum syntax is something like this:

CREATE PROCEDURE procedure_name (IN parameter INT) SQL_statements

The procedure name given can be any nonreserved name, and is case-insensitive. Within parentheses, give a comma-separated list of the parameters that will take data in (IN), return data (OUT), or do both (INOUT). For each parameter, specify the data type to be used (INT, CHAR, etc.).

You may provide special parameters to indicate the characteristics of the stored procedure. Several may be given in any order, in a space-separated list. You can specify the language used as SQL with the LANGUAGE SQL parameter, but this is the default and usually unnecessary.

A procedure that returns the same results each time for the same given parameters is considered deterministic. You can save processing time on the server by specifying this property through the DETERMINISTIC parameter. NOT DETERMINISTIC is the default.

The following keywords may be used to tell the server how the procedure will interact with it, allowing the server to optimize the procedure. The server does not enforce the restrictions on the procedure, however:

With the COMMENT clause, you can add a comment describing the procedure for future reference.

This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a routine, by default. With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the procedure. Related to this clause is the SQL SECURITY keyword, which instructs MySQL to use either the user account of the creator (DEFINER) of the procedure or the account that’s executing the procedure (INVOKER). This can help prevent some users from accessing restricted procedures.

In the following example, we create a simple procedure that copies all of the data from the students table to a backup table with the same schema. The table also includes an extra column in which the user can add a comment or reference note:

DELIMITER |

CREATE PROCEDURE students_copy_proc (IN ref_note VARCHAR(255))
BEGIN
REPLACE INTO students_backup
SELECT *, ref_note FROM students;
END|

DELIMITER ;

SET @ref_note = '2008 Spring Roster';

CALL students_copy_proc(@ref_note);

The first statement changes the terminating character for an SQL statement from its default, a semicolon, to a vertical bar. See the BEGIN...END statement earlier in this chapter for the reasons this is necessary.

Inside the procedure, the REPLACE statement selects all columns from students along with the value of the ref_note variable. Thus, every row of students is inserted, along with the value of the variable, into a new row in students_backup.

After the procedure is defined and the delimiter is changed back to a semicolon, the example sets a variable called ref_note that contains a note the user wants added to each row of data in the new table. This variable is passed to the CALL statement that runs the procedure.

To change an existing stored procedure, use the ALTER PROCEDURE statement. The DROP PROCEDURE statement removes a procedure.