Go to the first, previous, next, last section, table of contents.


19 Stored Procedures and Functions

Stored procedures and functions are a new feature in MySQL version 5.0. A stored procedure is a set of SQL commands that can be stored in the server. Once this has been done, clients don't need to keep re-issuing the individual commands but can refer to the stored procedure instead.

Stored procedures can provide improved performance as less information needs to be sent between the server and the client. The trade-off is that this does increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. And often, there are multiple client machines (such as Web servers) but only one or a few database servers.

Stored procedures also allow you to have libraries of functions in the database server. However, modern application languages already allow such design internally with for instance classes, and using these client application language features is beneficial for the programmer even outside the scope of database use.

Some situations where stored procedures can be particularly useful:

MySQL follows the SQL:2003 syntax for stored procedures, which is also used by IBM's DB2.

The MySQL implementation of stored procedures is still in progress. All syntax described in this chapter is supported and any limitations and extensions are documented where appropriate.

Stored procedures require the proc table in the mysql database. This table is created during the MySQL 5.0 installation procedure. If you are upgrading to MySQL 5.0 from an earlier version, be sure to update your grant tables to make sure the proc table exists. See section 2.5.8 Upgrading the Grant Tables.

19.1 Stored Procedure Syntax

Stored procedures and functions are routines that are created with CREATE PROCEDURE and CREATE FUNCTION statements. A procedure is invoked using a CALL statement, and can only pass back values using output variables. Functions may return a scalar value and can be called from inside a statement just like any other function (that is, by invoking the function's name). Stored routines may call other stored routines. A routine is either a procedure or a function.

At present, MySQL only preserves context for the default database. That is, if you say USE db_name within a procedure, the original default database is restored upon routine exit. A routine inherits the default database from the caller, so generally routines should either issue a USE db_name statement, or specify all tables with an explicit database reference, for example, db_name.tbl_name.

MySQL supports the very useful extension that allows the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1.

This following section describes the syntax used to create, alter, drop, and query stored procedures and functions.

19.1.1 Maintaining Stored Procedures

19.1.1.1 CREATE PROCEDURE and CREATE FUNCTION

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 statement(s)

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 section 22.2 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, as the core PHP engine is small, thread-safe, and can easily be embedded. As 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.

MySQL does not yet use the GRANT EXECUTE privilege.

MySQL stores the sql_mode settings in effect at the time a routine is created, and will always execute routines with these settings 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 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 prior to defining the procedure. 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> 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> SELECT hello('world')//
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

19.1.1.2 ALTER PROCEDURE and ALTER FUNCTION

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

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

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

19.1.1.3 DROP PROCEDURE and DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

This command is used to delete 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.

19.1.1.4 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

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

19.1.2 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]

This command is a MySQL extension. It returns characteristics of routines, such as the name, type, creator, creation and modification dates. If no pattern is specified, the information for all stored procedures or all stored functions is listed, depending on which statement you use.

19.1.3 CALL

CALL sp_name([parameter[,...]])

The CALL command is used to invoke a routine that was defined previously with CREATE PROCEDURE.

19.1.4 BEGIN ... END Compound Statement

[begin_label:] BEGIN
 statement(s)
END [end_label]

Stored routines may contain multiple statements, using a BEGIN ... END compound statement.

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

Please note that the optional [NOT] ATOMIC clause is not yet supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction.

Multiple statements requires that a client is able to send query strings containing `;'. This is handled in the mysql command-line client with the delimiter command. Changing the `;' end-of-query delimiter (for example, to `|') allows `;' to be used in a routine body.

19.1.5 DECLARE Statement

The DECLARE statement is used to define various items local to a routine: local variables (see section 19.1.6 Variables in Stored Procedures), conditions and handlers (see section 19.1.7 Conditions and Handlers) and cursors (see section 19.1.8 Cursors). SIGNAL and RESIGNAL statements are not currently supported.

DECLARE may only be used inside a BEGIN ... END compound statement and must be at its start, before any other statements.

19.1.6 Variables in Stored Procedures

You may declare and use variables within a routine.

19.1.6.1 DECLARE Local Variables

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

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

19.1.6.2 Variable SET Statement

SET variable = expression [,...]

The SET statement in stored procedures is an extended version of the general SET command. 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 only make sense for global/system variables; in that case the options are accepted but ignored.

19.1.6.3 SELECT ... INTO Statement

SELECT column[,...] INTO variable[,...] table_expression

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;

19.1.7 Conditions and Handlers

Certain conditions may require specific handling. These conditions can relate to errors, as well as general flow control inside a routine.

19.1.7.1 DECLARE Conditions

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 section 19.1.7.2 DECLARE Handlers.

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

19.1.7.2 DECLARE Handlers

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

19.1.8 Cursors

Simple cursors are supported inside stored procedures and functions. The syntax is as in embedded SQL. Cursors are currently asensitive, read-only, and non-scrolling. Asensitive means that the server may or may not make a copy of its result table.

For example:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE a CHAR(16);
  DECLARE b,c INT;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

19.1.8.1 Declaring Cursors

DECLARE cursor_name CURSOR FOR sql_statement

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

19.1.8.2 Cursor OPEN Statement

OPEN cursor_name

This statement opens a previously declared cursor.

19.1.8.3 Cursor FETCH Statement

FETCH cursor_name

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

19.1.8.4 Cursor CLOSE Statement

CLOSE cursor_name

This statement closes a previously opened cursor.

19.1.9 Flow Control Constructs

The IF, CASE, LOOP, WHILE, ITERATE, and LEAVE constructs are fully implemented.

These constructs may each contain either a single statement, or a block of statements using the BEGIN ... END compound statement. Constructs may be nested.

FOR loops are not currently supported.

19.1.9.1 IF Statement

IF search_condition THEN statement(s)
[ELSEIF search_condition THEN statement(s)]
...
[ELSE statement(s)]
END IF

IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement is executed. If no search_condition matches, the statement in the ELSE clause is executed.

Please note that there is also an IF() function. See section 13.2 Control Flow Functions.

19.1.9.2 CASE Statement

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

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

Please note that the syntax of a CASE statement inside a stored procedure differs slightly from that of the SQL CASE expression. The CASE statement can not have an ELSE NULL clause, and the construct is terminated with END CASE instead of END. See section 13.2 Control Flow Functions.

19.1.9.3 LOOP Statement

[begin_label:] LOOP
  statement(s)
END LOOP [end_label]

LOOP implements a simple loop construct, enabling repeated execution of a particular statement or group of 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.

19.1.9.4 LEAVE Statement

LEAVE label

This statement is used to exit any flow control construct.

19.1.9.5 ITERATE Statement

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

19.1.9.6 REPEAT Statement

[begin_label:] REPEAT
  statement(s)
UNTIL search_condition
END REPEAT [end_label]

The 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)

19.1.9.7 WHILE Statement

[begin_label:] WHILE search_condition DO
  statement(s)
END WHILE [end_label]

The 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


Go to the first, previous, next, last section, table of contents.