MySQL in a Nutshell, 2nd Edition

Name

CREATE PROCEDURE

Synopsis

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

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