<< Back to man.ChinaUnix.net

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 statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

Some situations where stored procedures can be particularly useful:

Stored procedures can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server system because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

Stored procedures also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally, for example, by using classes. Using these client application language features is beneficial for the programmer even outside the scope of database use.

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 that the proc table exists. See section 2.10.7 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 routine is either a procedure or a function. A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value. Stored routines may call other stored routines.

As of MySQL 5.0.1, a stored procedure or function is associated with a particular database. This has several implications:

(In MySQL 5.0.0, stored routines are global and not associated with a database. They inherit the default database from the caller. If a USE db_name is executed within the routine, the original default database is restored upon routine exit.)

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

If the routine name is the same as the name of a built-in SQL function, you will need to use a space between the name and the following parenthesis when defining the routine, or a syntax error will occur. This is also true when you invoke the routine later.

The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. 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 24.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 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)

19.1.1.2 ALTER PROCEDURE and ALTER FUNCTION

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

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

19.1.1.3 DROP PROCEDURE and DROP FUNCTION

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.

19.1.1.4 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION

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,'!')

19.1.2 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

This statement is a MySQL extension. It returns characteristics of routines, such as the database, name, type, creator, and 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.

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment: 

19.1.3 CALL Statement

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

The CALL statement is used to invoke a procedure that was defined previously with CREATE PROCEDURE.

CALL can return values through its parameters. It also ``returns'' the number of rows affected, which a client program can obtain at the SQL level by calling the ROW_COUNT() function and from C by calling the mysql_affected_rows() C API function.

19.1.4 BEGIN ... END Compound Statement

[begin_label:] BEGIN
    [statement_list]
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.

Using multiple statements requires that a client is able to send query strings containing the ; statement delimiter. 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 be used only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Cursors must be declared before declaring handlers, and variables must be declared before declaring either cursors or handlers.

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

19.1.6.3 SELECT ... INTO Statement

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;

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

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.

Cursors must be declared before declaring handlers, and variables must be declared before declaring either cursors or handlers.

For example:

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

  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 select_statement

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

The SELECT statement cannot have an INTO clause.

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

19.1.8.4 Cursor CLOSE Statement

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.

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_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 section 12.2 Control Flow Functions.

19.1.9.2 CASE Statement

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

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list ...]
    [ELSE statement_list]
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 section 12.2 Control Flow Functions. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

19.1.9.3 LOOP Statement

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

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

19.1.9.7 WHILE Statement

[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


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