CREATE PROCEDURE sp_name ([parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])
    [RETURNS type]
    [characteristic ...] routine_body

parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statements or statements

By default, the routine is associated with the current database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it.

The RETURNS clause may be specified only for a FUNCTION. It is used to indicate the return type of the function, and the function body must contain a RETURN value statement.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name. Specifying IN, OUT, or INOUT is only valid for a PROCEDURE.

The CREATE FUNCTION statement is used in earlier versions of MySQL to support UDFs (User Defined Functions). See the section called “Adding New Functions to MySQL”. UDFs continue to be supported, even with the existence of stored functions. A UDF can be regarded as an external stored function. However, do note that stored functions share their namespace with UDFs.

A framework for external stored procedures will be introduced in the near future. This will allow you to write stored procedures in languages other than SQL. Most likely, one of the first languages to be supported will be PHP because the core PHP engine is small, thread-safe, and can easily be embedded. Because the framework will be public, it is expected that many other languages will also be supported.

A function is considered “deterministic” if it always returns the same result for the same input parameters, and “not deterministic” otherwise. Currently, the DETERMINISTIC characteristic is accepted, but not yet used by the optimizer.

The SQL SECURITY characteristic can be used to specify whether the routine should be executed using the permissions of the user who creates the routine or the user who invokes it. The default value is DEFINER. This feature is new in SQL:2003. The creator or invoker must have permission to access the database with which the routine is associated.

MySQL does not yet use the GRANT EXECUTE privilege.

MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and will always execute the routine with this setting in force.

The COMMENT clause is a MySQL extension, and may be used to describe the stored procedure. This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

MySQL allows routines to contain DDL statements (such as CREATE and DROP) and SQL transaction statements (such as COMMIT). This is not required by the standard and is therefore implementation-specific.

Note: Currently, stored functions created with CREATE FUNCTION may not contain references to tables. Please note that this includes some SET statements, but excludes some SELECT statements. This limitation will be lifted as soon as possible.

The following is an example of a simple stored procedure that uses an OUT parameter. The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result:

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:
    NAME new_name
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

This statement can be used to rename a stored procedure or function, and to change its characteristics. More than one change may be specified in an ALTER PROCEDURE or ALTER FUNCTION statement.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

This statement is used to drop a stored procedure or function. That is, the specified routine is removed from the server.

The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the procedure or function does not exist. A warning is produced that can be viewed with SHOW WARNINGS.

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

This statement is a MySQL extension. Similar to SHOW CREATE TABLE, it returns the exact string that can be used to re-create the named routine.

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')

DECLARE var_name[,...] type [DEFAULT value]

This statement is used to declare local variables. The scope of a variable is within the BEGIN ... END block.

SET var_name = expr [, var_name = expr] ...

The SET statement in stored procedures is an extended version of the general SET statement. Referenced variables may be ones declared inside a routine, or global server variables.

The SET statement in stored procedures is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, server variables, and global and session server variables) can be mixed. This also allows combinations of local variables and some options that make sense only for global/system variables; in that case, the options are accepted but ignored.

SELECT col_name[,...] INTO var_name[,...] table_expr

This SELECT syntax stores selected columns directly into variables. Therefore, only a single row may be retrieved. This statement is also extremely useful when used in combination with cursors.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

This statement specifies conditions that will need specific handling. It associates a name with a specified error condition. The name can subsequently be used in a DECLARE HANDLER statement. See the section called “DECLARE Handlers”.

In addition to SQLSTATE values, MySQL error codes are also supported.

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

This statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed.

For a CONTINUE handler, execution of the current routine continues after execution of the handler statement. For an EXIT handler, execution of the current BEGIN...END compound statement is terminated. The UNDO handler type statement is not yet supported.

In addition to SQLSTATE values, MySQL error codes are also supported.

For example:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

Notice that @x is 3, which shows that MySQL executed to the end of the procedure. If the line DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; had not been present, MySQL would have taken the default (EXIT) path after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2.

DECLARE cursor_name CURSOR FOR sql_statement

Multiple cursors may be defined in a routine, but each must have a unique name.

OPEN cursor_name

This statement opens a previously declared cursor.

FETCH cursor_name INTO var_name [, var_name] ...

This statement fetches the next row (if a row exists) using the specified open cursor, and advances the cursor pointer.

CLOSE cursor_name

This statement closes a previously opened cursor.

If not closed explicitly, a cursor is closed at the end of the compound statement in which it was declared.

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]
    ...
    [ELSE statement_list]
END IF

IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement list is executed. If no search_condition matches, the statement list in the ELSE clause is executed. statement_list can consist of one or more statements.

Please note that there is also an IF() function. See the section called “Control Flow Functions”.

CASE case_value
    WHEN when_value THEN statement
    [WHEN when_value THEN statement ...]
    [ELSE statement]
END CASE

Or:

CASE
    WHEN search_condition THEN statement
    [WHEN search_condition THEN statement ...]
    [ELSE statement]
END CASE

The CASE statement for stored procedures implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement is executed. If no search condition matches, the statement in the ELSE clause is executed.

Note: The syntax of a CASE statement shown here for use inside a stored procedure differs slightly from that of the SQL CASE expression described in the section called “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP implements a simple loop construct, enabling repeated execution of a particular statement or statements. The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement.

begin_label and end_label must be the same, if both are specified.

LEAVE label

This statement is used to exit any flow control construct.

ITERATE label

ITERATE can only appear within LOOP, REPEAT, and WHILE statements. ITERATE means “do the loop iteration again.”

For example:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

The statement or statements within a REPEAT statement are repeated until the search_condition is true.

begin_label and end_label must be the same, if both are specified.

For example:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

The statement or statements within a WHILE statement are repeated as long as the search_condition is true.

begin_label and end_label must be the same, if both are specified.

For example:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END