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.
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:
USE
db_name is performed (and undone when the routine terminates).
USE statements within stored routines are disallowed.
p or function f
that is associated with the test database, you can say
CALL test.p() or test.f().
(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.
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)
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.
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.
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,'!')
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:
CALL StatementCALL 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.
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.
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.
You may declare and use variables within a routine.
DECLARE Local VariablesDECLARE 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 StatementSET 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 ... INTO StatementSELECT 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;
Certain conditions may require specific handling. These conditions can relate to errors, as well as general flow control inside a routine.
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.
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.
SQLWARNING is shorthand for all SQLSTATE codes that begin with 01.
NOT FOUND is shorthand for all SQLSTATE codes that begin with 02.
SQLEXCEPTION is shorthand for all SQLSTATE codes not caught by
SQLWARNING or NOT FOUND.
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.
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
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.
OPEN StatementOPEN cursor_name
This statement opens a previously declared cursor.
FETCH StatementFETCH 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 StatementCLOSE 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.
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.
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.
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.
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.
LEAVE StatementLEAVE label
This statement is used to exit any flow control construct.
ITERATE StatementITERATE 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
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)
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.