CREATE PROCEDURE

A procedure, also known as a stored procedure, is set of MySQL statements stored on the MySQL server and called as a unit, processing any data it's given in its parameters.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE 
[DEFINER = {'user'@'host'|CURRENT_USER}]
PROCEDURE stored_procedure ([[IN|OUT|INOUT] parameter data_type[,...]])
  [LANGUAGE SQL]
  [NOT] DETERMINISTIC]
  [{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
  [COMMENT 'string']
  [SQL SECURITY {DEFINER|INVOKER}]
routine

Explanation

A procedure, also known as a stored procedure, is set of MySQL 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:

CREATE PROCEDURE procedure_name (IN parameter INT) SQL_statements

The procedure name given can be any MySQL non-reserved 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.

One set of keywords tells 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:

  • CONTAINS SQL
  • The procedure executes SQL statements, but does not read from or write to a table; one example is s procedure that queries server status. This is the default.

  • NO SQL
  • The procedure does not contain any SQL statements.

  • READS SQL DATA
  • The procedure might read data from at least one table, but doesn't write data to any tables.

  • MODIFIES SQL DATA
  • The procedure might write data to at least one table, as well potentially reading data from tables.

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

This MySQL statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and 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 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 to prevent some users from accessing restricted procedures.

Examples

In the following example using this MySQL statement, 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 semi-colon, to a vertical bar. See our MySQL documentation on the BEGIN...END statement for the reasons this is necessary.

Inside the procedure, the MySQL statement, REPLACE 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 changes back to a semi-colon, 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 MySQL statement, CALL that runs the procedure.

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