Table of Contents
CREATE PROCEDURE
and CREATE FUNCTION
SyntaxALTER PROCEDURE
and ALTER FUNCTION
SyntaxDROP PROCEDURE
and DROP FUNCTION
SyntaxCALL
Statement SyntaxBEGIN ... END
Compound Statement SyntaxDECLARE
Statement SyntaxStored routines (procedures and functions) are supported in MySQL 5.1. 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 routines can be particularly useful:
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.
Stored routines 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 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 routines 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 routines, which is also used by IBM's DB2.
The MySQL implementation of stored routines is still in progress. All syntax described in this chapter is supported and any limitations and extensions are documented where appropriate. Further discussion of restrictions on use of stored routines is given in Section I.1, “Restrictions on Stored Routines and Triggers”.
Binary logging for stored routines takes place as described in Section 19.4, “Binary Logging of Stored Routines and Triggers”.
Stored routines require the proc
table in the
mysql
database. This table is created during
the MySQL 5.1 installation procedure. If you are
upgrading to MySQL 5.1 from an earlier version, be
sure to update your grant tables to make sure that the
proc
table exists. See
Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
The server manipulates the mysql.proc
table in
response to statements that create, alter, or drop stored
routines. It is not supported that the server will notice manual
manipulation of this table.
The MySQL grant system takes stored routines into account as follows:
The CREATE ROUTINE
privilege is needed to
create stored routines.
The ALTER ROUTINE
privilege is needed to
alter or drop stored routines. This privilege is granted
automatically to the creator of a routine.
The EXECUTE
privilege is required to
execute stored routines. However, this privilege is granted
automatically to the creator of a routine. Also, the default
SQL SECURITY
characteristic for a routine
is DEFINER
, which enables users who have
access to the database with which the routine is associated to
execute the routine.
CREATE PROCEDURE
and CREATE FUNCTION
SyntaxALTER PROCEDURE
and ALTER FUNCTION
SyntaxDROP PROCEDURE
and DROP FUNCTION
SyntaxCALL
Statement SyntaxBEGIN ... END
Compound Statement SyntaxDECLARE
Statement Syntax
A stored routine is either a procedure or a function. Stored
routines 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. 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.
A stored procedure or function is associated with a particular database. This has several implications:
When the routine is invoked, an implicit USE
is performed (and
undone when the routine terminates). db_name
USE
statements within stored routines are disallowed.
You can qualify routine names with the database name. This can
be used to refer to a routine that is not in the current
database. For example, to invoke a stored procedure
p
or function f
that is
associated with the test
database, you can
say CALL test.p()
or
test.f()
.
When a database is dropped, all stored routines associated with it are dropped as well.
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.
The client should also specify the
CLIENT_MULTI_STATEMENTS
option when it
connects. For C programs, this can be done with the
mysql_real_connect()
C API function (see
Section 25.2.3.51, “mysql_real_connect()
”).
The following sections describe the syntax used to create, alter, drop, and invoke stored procedures and functions.
CREATE [DEFINER = {user
| CURRENT_USER }] PROCEDUREsp_name
([proc_parameter
[,...]]) [characteristic
...]routine_body
CREATE [DEFINER = {user
| CURRENT_USER }] FUNCTIONsp_name
([func_parameter
[,...]]) RETURNStype
[characteristic
...]routine_body
proc_parameter
: [ IN | OUT | INOUT ]param_name
type
func_parameter
:param_name
type
type
:Any valid MySQL data type
characteristic
: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string
'routine_body
:Valid SQL procedure statement
These statements create stored routines. To use them, it is
necessary to have the CREATE ROUTINE
privilege. If binary logging is enabled, the CREATE
FUNCTION
statement might may also require the
SUPER
privilege, as described in
Section 19.4, “Binary Logging of Stored Routines and Triggers”. MySQL automatically
grants the ALTER ROUTINE
and
EXECUTE
privileges to the routine creator.
By default, the routine is associated with the default 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 must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-using the names of existing SQL functions for your own stored routines.
The IGNORE_SPACE
SQL mode applies to built-in
functions, not to stored routines. it is always allowable to
have spaces after a routine name, regardless of whether
IGNORE_SPACE
is enabled.
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.
Note: Specifying a parameter as
IN
, OUT
, or
INOUT
is valid only for a
PROCEDURE
. (FUNCTION
parameters are always regarded as IN
parameters.)
Each parameter can be declared to use any valid data type,
except that the COLLATE
attribute cannot be
used.
The RETURNS
clause may be specified only for
a FUNCTION
, for which it is mandatory. It
indicates the return type of the function, and the function body
must contain a RETURN
statement.
value
The routine_body
consists of a valid
SQL procedure statement. This can be a simple statement such as
SELECT
or INSERT
, or it
can be a compound statement written using
BEGIN
and END
. Compound
statement syntax is described in Section 19.2.5, “BEGIN ... END
Compound Statement Syntax”.
Compound statements can contain declarations, loops, and other
control structure statements. The syntax for these statements is
described later in this chapter. See, for example,
Section 19.2.6, “DECLARE
Statement Syntax”, and
Section 19.2.10, “Flow Control Constructs”. Some statements are
not allowed in stored routines; see
Section I.1, “Restrictions on Stored Routines and Triggers”.
The CREATE FUNCTION
statement was used in
earlier versions of MySQL to support UDFs (user-defined
functions). See Section 27.3, “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 procedure or function is considered
“deterministic” if it always produces the same
result for the same input parameters, and “not
deterministic” otherwise. If neither
DETERMINISTIC
nor NOT
DETERMINISTIC
is given in the routine definition, the
default is NOT DETERMINISTIC
.
For replication purposes, use of the NOW()
function (or its synonyms) or RAND()
does not
necessarily make a routine non-deterministic. For
NOW()
, the binary log includes the timestamp
and replicates correctly. RAND()
also
replicates correctly as long as it is invoked only once within a
routine. (You can consider the routine execution timestamp and
random number seed as implicit inputs that are identical on the
master and slave.)
Currently, the DETERMINISTIC
characteristic
is accepted, but not yet used by the optimizer. However, if
binary logging is enabled, this characteristic affects which
routine definitions MySQL accepts. See
Section 19.4, “Binary Logging of Stored Routines and Triggers”.
Several characteristics provide information about the nature of
data use by the routine. CONTAINS SQL
indicates that the routine does not contain statements that read
or write data. NO SQL
indicates that the
routine contains no SQL statements. READS SQL
DATA
indicates that the routine contains statements
that read data, but not statements that write data.
MODIFIES SQL DATA
indicates that the routine
contains statements that may write data. CONTAINS
SQL
is the default if none of these characteristics is
given explicitly. These characteristics are advisory only. The
server does not use them to constrain what kinds of statements a
routine will be allowed to execute.
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. It is necessary to have the
EXECUTE
privilege to be able to execute the
routine. The user that must have this privilege is either the
definer or invoker, depending on how the SQL
SECURITY
characteristic is set.
The optional DEFINER
clause specifies the
MySQL account to be used when checking access privileges at
routine execution time for routines that have the SQL
SECURITY DEFINER
characteristic. The
DEFINER
clause was added in MySQL 5.1.8.
If a user
value is given, it should
be a MySQL account in
'
format (the same format used in the user_name
'@'host_name
'GRANT
statement). The user_name
and
host_name
values both are required.
CURRENT_USER
also can be given as
CURRENT_USER()
. The default
DEFINER
value is the user who executes the
CREATE PROCEDURE
or CREATE
FUNCTION
or statement. (This is the same as
DEFINER = CURRENT_USER
.)
If you specify the DEFINER
clause, you cannot
set the value to any account but your own unless you have the
SUPER
privilege. These rules determine the
legal DEFINER
user values:
If you do not have the SUPER
privilege,
the only legal user
value is your
own account, either specified literally or by using
CURRENT_USER
. You cannot set the definer
to some other account.
If you have the SUPER
privilege, you can
specify any syntactically legal account name. If the account
does not actually exist, a warning is generated.
Although it is possible to create routines with a
non-existent DEFINER
value, an error
occurs if the routine executes with definer privileges but
the definer does not exist at execution time.
MySQL stores the sql_mode
system variable
setting that is in effect at the time a routine is created, and
always executes the routine with this setting in force.
When the routine is invoked, an implicit USE
is performed (and
undone when the routine terminates). db_name
USE
statements within stored routines are disallowed.
The server uses the data type of a routine parameter or function
return value as follows. These rules also apply to local routine
variables created with the DECLARE
statement
(Section 19.2.7.1, “DECLARE
Local Variables”).
Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict mode.
For character data types, if there is a CHARACTER
SET
clause in the declaration, the specified
character set and its default collation are used. If there
is no such clause, the database character set and collation
are used. (These are given by the values of the
character_set_database
and
collation_database
system variables.)
Only scalar values can be assigned to parameters or
variables. For example, a statement such as SET x =
(SELECT 1, 2)
is invalid.
The COMMENT
clause is a MySQL extension, and
may be used to describe the stored routine. 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
. MySQL
also allows stored procedures (but not stored functions) to
contain SQL transaction statements such as
COMMIT
. Stored functions may not contain
statements that do explicit or implicit commit or rollback.
Support for these statements is not required by the SQL
standard, which states that each DBMS vendor may decide whether
to allow them.
Stored routines cannot use LOAD DATA INFILE
.
Statements that return a result set cannot be used within a
stored function. This includes SELECT
statements that do not use INTO
to fetch
column values into variables, SHOW
statements, and other statements such as
EXPLAIN
. For statements that can be
determined at function definition time to return a result set, a
Not allowed to return a result set from a
function
error occurs
(ER_SP_NO_RETSET_IN_FUNC
). For statements
that can be determined only at runtime to return a result set, a
PROCEDURE %s can't return a result set in the given
context
error occurs
(ER_SP_BADSELECT
).
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)
When using the delimiter
command, you should
avoid the use of the backslash
(‘\
’) character because that is
the escape character for MySQL.
The following is an example of a function that takes a
parameter, performs an operation using an SQL function, and
returns the result. In this case, it is unnecessary to use
delimiter
because the function definition
contains no internal ;
statement delimiters:
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)
A stored function returns a value of the data type specified in
its RETURNS
clause. If the
RETURN
statement returns a value of a
different type, the value is coerced to the proper type. For
example, if a function returns an ENUM
or
SET
value, but the RETURN
statement returns an integer, the value returned from the
function is the string for the corresponding
ENUM
member of set of SET
members.
ALTER {PROCEDURE | FUNCTION}sp_name
[characteristic
...]characteristic
: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string
'
This statement can be used to change the characteristics of a
stored procedure or function. You must have the ALTER
ROUTINE
privilege for the routine. (That privilege is
granted automatically to the routine creator.) If binary logging
is enabled, the ALTER FUNCTION
statement
might also require the SUPER
privilege, as
described in Section 19.4, “Binary Logging of Stored Routines and Triggers”.
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. You
must have the ALTER ROUTINE
privilege for the
routine. (That privilege is granted automatically to the routine
creator.)
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
.
CALLsp_name
([parameter
[,...]])
The CALL
statement invokes a procedure that
was defined previously with CREATE PROCEDURE
.
CALL
can pass back values to its caller using
parameters that are declared as OUT
or
INOUT
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_label
:] BEGIN [statement_list
] END [end_label
]
BEGIN ... END
syntax is used for writing
compound statements, which can appear within stored routines and
triggers. A compound statement can contain multiple statements,
enclosed by the BEGIN
and
END
keywords.
statement_list
represents a list of
one or more statements. Each statement within
statement_list
must be terminated by
a semicolon (;
) statement delimiter. Note
that statement_list
is optional,
which means that the empty compound statement (BEGIN
END
) is legal.
Use of multiple statements requires that a client is able to
send statement strings containing the ;
statement delimiter. This is handled in the
mysql command-line client with the
delimiter
command. Changing the
;
end-of-statement delimiter (for example, to
//
) allows ;
to be used in
a routine body. For an example, see
Section 19.2.1, “CREATE PROCEDURE
and CREATE FUNCTION
Syntax”.
A compound statement can be labeled.
end_label
cannot be given unless
begin_label
also is present. If both
are present, they must be the same.
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.
The DECLARE
statement is used to define
various items local to a routine:
Local variables. See Section 19.2.7, “Variables in Stored Routines”.
Conditions and handlers. See Section 19.2.8, “Conditions and Handlers”.
Cursors. See Section 19.2.9, “Cursors”.
The SIGNAL
and RESIGNAL
statements are not currently supported.
DECLARE
is allowed only inside a
BEGIN ... END
compound statement and must be
at its start, before any other statements.
Declarations must follow a certain order. Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.
You may declare and use variables within a routine.
DECLAREvar_name
[,...]type
[DEFAULTvalue
]
This statement is used to declare local variables. To provide
a default value for the variable, include a
DEFAULT
clause. The value can be specified
as an expression; it need not be a constant. If the
DEFAULT
clause is missing, the initial
value is NULL
.
Local variables are treated like routine parameters with
respect to data type and overflow checking. See
Section 19.2.1, “CREATE PROCEDURE
and CREATE FUNCTION
Syntax”.
The scope of a local variable is within the BEGIN ...
END
block where it is declared. The variable can be
referred to in blocks nested within the declaring block,
except those blocks that declare a variable with the same
name.
SETvar_name
=expr
[,var_name
=expr
] ...
The SET
statement in stored routines is an
extended version of the general SET
statement. Referenced variables may be ones declared inside a
routine, or global system variables.
The SET
statement in stored routines 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 and global and session server variables)
can be mixed. This also allows combinations of local variables
and some options that make sense only for system variables; in
that case, the options are recognized but ignored.
SELECTcol_name
[,...] INTOvar_name
[,...]table_expr
This SELECT
syntax stores selected columns
directly into variables. Therefore, only a single row may be
retrieved.
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
User variable names are not case sensitive. See Section 9.3, “User-Defined Variables”.
Important: SQL variable names
should not be the same as column names. If an SQL statement,
such as a SELECT ... INTO
statement,
contains a reference to a column and a declared local variable
with the same name, MySQL currently interprets the reference
as the name of a variable. For example, in the following
statement, xname
is interpreted as a
reference to the xname
variable rather than the
xname
column:
CREATE PROCEDURE sp1 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname; SELECT newname; END;
When this procedure is called, the newname
variable returns the value 'bob'
regardless
of the value of the table1.xname
column.
See also Section I.1, “Restrictions on Stored Routines and Triggers”.
Certain conditions may require specific handling. These conditions can relate to errors, as well as to general flow control inside a routine.
DECLAREcondition_name
CONDITION FORcondition_value
condition_value
: SQLSTATE [VALUE]sqlstate_value
|mysql_error_code
This statement specifies conditions that 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.2.8.2, “DECLARE
Handlers”.
A condition_value
can be an
SQLSTATE value or a MySQL error code.
DECLAREhandler_type
HANDLER FORcondition_value
[,...]statement
handler_type
: CONTINUE | EXIT | UNDOcondition_value
: SQLSTATE [VALUE]sqlstate_value
|condition_name
| SQLWARNING | NOT FOUND | SQLEXCEPTION |mysql_error_code
The DECLARE ... HANDLER
statement specifies
handlers that each may deal with one or more conditions. If
one of these conditions occurs, the specified
statement
is executed.
statement
can be a simple statement
(for example, SET
), or it can be a
compound statement written using var_name
= value
BEGIN
and
END
(see Section 19.2.5, “BEGIN ... END
Compound Statement Syntax”).
For a CONTINUE
handler, execution of the
current routine continues after execution of the handler
statement. For an EXIT
handler, execution
terminates for the BEGIN ... END
compound
statement in which the handler is declared. (This is true even
if the condition occurs in an inner block.) The
UNDO
handler type statement is not yet
supported.
If a condition occurs for which no handler has been declared,
the default action is EXIT
.
A condition_value
can be any of the
following values:
An SQLSTATE value or a MySQL error code.
A condition name previously specified with
DECLARE ... CONDITION
. See
Section 19.2.8.1, “DECLARE
Conditions”.
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
.
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)
The example associates a handler with SQLSTATE 23000, which
occurs for a duplicate-key error. 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 path (EXIT
) after the second
INSERT
failed due to the PRIMARY
KEY
constraint, and SELECT @x
would have returned 2
.
If you want to ignore a condition, you can declare a
CONTINUE
handler for it and associate it
with an empty block. For example:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
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 and conditions must be declared before declaring either cursors or handlers.
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
DECLAREcursor_name
CURSOR FORselect_statement
This statement declares a cursor. Multiple cursors may be declared in a routine, but each cursor in a given block must have a unique name.
The SELECT
statement cannot have an
INTO
clause.
The IF
, CASE
,
LOOP
, WHILE
,
REPLACE
ITERATE
, and
LEAVE
constructs are fully implemented.
Many of these constructs contain other statements, as indicated
by the grammer specifications in the following sections. Such
constructs may be nested. For example, an IF
statement might contain a WHILE
loop, which
itself contains a CASE
statement.
FOR
loops are not currently supported.
IFsearch_condition
THENstatement_list
[ELSEIFsearch_condition
THENstatement_list
] ... [ELSEstatement_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. Each
statement_list
consists of one or
more statements.
Note: There is also an
IF()
function, which
differs from the IF
statement described here. See
Section 12.2, “Control Flow Functions”.
CASEcase_value
WHENwhen_value
THENstatement_list
[WHENwhen_value
THENstatement_list
] ... [ELSEstatement_list
] END CASE
Or:
CASE WHENsearch_condition
THENstatement_list
[WHENsearch_condition
THENstatement_list
] ... [ELSEstatement_list
] END CASE
The CASE
statement for stored routines
implements a complex conditional construct. If a
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. Each
statement_list
consists of one or
more statements.
Note: The syntax of the
CASE
statement shown
here for use inside stored routines 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
.
[begin_label
:] LOOPstatement_list
END LOOP [end_label
]
LOOP
implements a simple loop construct,
enabling repeated execution of the statement list, which
consists of one or more statements. The statements within the
loop are repeated until the loop is exited; usually this is
accomplished with a LEAVE
statement.
A LOOP
statement can be labeled.
end_label
cannot be given unless
begin_label
also is present. If
both are present, they must be the same.
LEAVE label
This statement is used to exit any labeled flow control
construct. It can be used within BEGIN ...
END
or loop constructs (LOOP
,
REPEAT
, WHILE
).
ITERATE label
ITERATE
can appear only within
LOOP
, REPEAT
, and
WHILE
statements.
ITERATE
means “do the loop
again.”
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
:] REPEATstatement_list
UNTILsearch_condition
END REPEAT [end_label
]
The statement list within a REPEAT
statement is repeated until the
search_condition
is true. Thus, a
REPEAT
always enters the loop at least
once. statement_list
consists of
one or more statements.
A REPEAT
statement can be labeled.
end_label
cannot be given unless
begin_label
also is present. If
both are present, they must be the same.
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
:] WHILEsearch_condition
DOstatement_list
END WHILE [end_label
]
The statement list within a WHILE
statement
is repeated as long as the
search_condition
is true.
statement_list
consists of one or
more statements.
A WHILE
statement can be labeled.
end_label
cannot be given unless
begin_label
also is present. If
both are present, they must be the same.
Example:
CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE; END
Do MySQL 5.1 stored procedures and functions work with replication?
Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 19.4, “Binary Logging of Stored Routines and Triggers”.
Are stored procedures and functions created on a master server replicated to a slave?
Yes, creation of stored procedures and functions carried out
through normal DDL statements on a master server are
replicated to a slave, so the objects will exist on both
servers. ALTER
and DROP
statements for stored procedures and functions are also
replicated.
How are actions that take place inside stored procedures and functions replicated?
MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.
Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.
Are there special security requirements for using stored procedures and functions together with replication?
Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:
Any user wishing to create stored functions must be
granted the SUPER
privilege.
Alternatively, a DBA can set the
log_bin_trust_function_creators
system
variable to 1, which enables anyone with the standard
CREATE ROUTINE
privilege to create
stored functions.
What limitations exist for replicating stored procedure and function actions?
Non-deterministic (random) or time-based actions embedded in
stored procedures may not replicate properly. By their very
nature, randomly produced results are not predictable and
cannot be exactly reproduced, and therefore, random actions
replicated to a slave will not mirror those performed on a
master. Note that declaring stored functions to be
DETERMINISTIC
or setting the
log_bin_trust_function_creators
system
variable to 0 will not allow random-valued operations to be
invoked.
In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.
Finally, non-transactional tables for which errors occur
during large DML actions (such as bulk inserts) may experience
replication issues in that a master may be partially updated
from DML activity, but no updates are done to the slave
because of the errors that occurred. A workaround is for a
function's DML actions to be carried out with the
IGNORE
keyword so that updates on the
master that cause errors are ignored and updates that do not
cause errors are replicated to the slave.
Do the preceding limitations affect MySQL's ability to do point-in-time recovery?
The same limitations that affect replication do affect point-in-time recovery.
What will MySQL do to correct the aforementioned limitations?
As of MySQL 5.1.5, you can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier. For additional information, see Section 6.3, “Row-Based Replication”.
Do triggers work with replication?
Triggers and replication in MySQL 5.1 work the same as in most other database engines: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.
How are actions carried out through triggers on a master replicated to a slave?
First, the triggers that exist on a master must be re-created
on the slave server. Once this is done, the replication flow
works as any other standard DML statement that participates in
replication. For example, consider a table
EMP
that has an AFTER
insert trigger, which exists on a master MySQL server. The
same EMP
table and AFTER
insert trigger exist on the slave server as well. The
replication flow would be:
An INSERT
statement is made to
EMP
.
The AFTER
trigger on
EMP
activates.
The INSERT
statement is written to the
binary log.
The replication slave picks up the
INSERT
statement to
EMP
and executes it.
The AFTER
trigger on
EMP
that exists on the slave activates.
The binary log contains information about SQL statements that modify database contents. This information is stored in the form of “events” that describe the modifications. The binary log has two important purposes:
For replication, the master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 6.2, “Replication Implementation Overview”.
Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 5.9.2.2, “Using Backups for Recovery”.
This section describes how MySQL 5.1 handles binary logging for stored routines (procedures and functions) and triggers. It also states the current conditions that the implementation places on the use of stored routines, and then provides additional information about the reasons for these conditions.
In general, the issues described here result from the fact that binary logging occurs at the SQL statement level. A future MySQL release is expected to implement row-level binary logging, which specifies the changes to make to individual rows as a result of executing SQL statements.
Unless noted otherwise, the remarks here assume that you have
enabled binary logging by starting the server with the
--log-bin
option. (See
Section 5.11.4, “The Binary Log”.) If the binary log is not enabled,
replication is not possible, nor is the binary log available for
data recovery.
The current conditions on the use of stored functions in MySQL 5.1 can be summarized as follows. These conditions do not apply to stored procedures and they do not apply unless binary logging is enabled.
To create or alter a stored function, you must have the
SUPER
privilege, in addition to the
CREATE ROUTINE
or ALTER
ROUTINE
privilege that is normally required.
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
To relax the preceding conditions on function creation (that
you must have the SUPER
privilege and that
a function must be declared deterministic or to not modify
data), set the global
log_bin_trust_function_creators
system
variable to 1. By default, this variable has a value of 0, but
you can change it like this:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators
option when
starting the server.
If binary logging is not enabled,
log_bin_trust_function_creators
does not
apply and SUPER
is not required for
function creation.
Triggers are similar to stored functions, so the preceding remarks
regarding functions also apply to triggers with the following
exception: CREATE TRIGGER
does not have an
optional DETERMINISTIC
characteristic, so
triggers are assumed to be always deterministic. However, this
assumption might in some cases be invalid. For example, the
UUID()
function is non-deterministic (and does
not replicate). You should be careful about using such functions
in triggers.
Triggers can update tables, so error messages similar to those for
stored functions occur with CREATE TRIGGER
if
you do not have the SUPER
privilege and
log_bin_trust_function_creators
is 0.
The following discussion provides additional detail about the logging implementation and its implications.
The server writes CREATE PROCEDURE
,
CREATE FUNCTION
, ALTER
PROCEDURE
, ALTER FUNCTION
,
DROP PROCEDURE
, and DROP
FUNCTION
statements to the binary log.
A stored function invocation is logged as a
SELECT
statement if the function changes
data and occurs within a statement that would not otherwise be
logged. This prevents non-replication of data changes that
result from use of stored functions in non-logged statements.
For example, SELECT
statements are not
written to the binary log, but a SELECT
might invoke a stored function that makes changes. To handle
this, a SELECT
statement is
written to the binary log when the given function makes a
change. Suppose that the following statements are executed on
the master:
func_name
()
CREATE FUNCTION f1(a INT) RETURNS INT BEGIN IF (a < 3) THEN INSERT INTO t2 VALUES (a); END IF; END; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1;
When the SELECT
statement executes, the
function f1()
is invoked three times. Two
of those invocations insert a row, and MySQL logs a
SELECT
statement for each of them. That is,
MySQL writes the following statements to the binary log:
SELECT f1(1); SELECT f1(2);
The server also logs a SELECT
statement for
a stored function invocation when the function invokes a
stored procedure that causes an error. In this case, the
server writes the SELECT
statement to the
log along with the expected error code. On the slave, if the
same error occurs, that is the expected result and replication
continues. Otherwise, replication stops.
Note: Before MySQL 5.1.7, you will see these SELECT
statements
logged as func_name
()DO
. The change
to func_name
()SELECT
was made because use of
DO
was found to yield insufficient control
over error code checking.
Logging stored function invocations rather than the statements executed by a function has a security implication for replication, which arises from two factors:
It is possible for a function to follow different execution paths on master and slave servers.
Statements executed on a slave are processed by the slave SQL thread which has full privileges.
The implication is that although a user must have the
CREATE ROUTINE
privilege to create a
function, the user can write a function containing a dangerous
statement that will execute only on the slave where the
statement is processed by the SQL thread that has full
privileges. For example, if the master and slave servers have
server ID values of 1 and 2, respectively, a user on the
master server could create and invoke an unsafe function
unsafe_func()
as follows:
mysql>delimiter //
mysql>CREATE FUNCTION unsafe_func () RETURNS INT
->BEGIN
->IF @@server_id=2 THEN
->dangerous_statement
; END IF;RETURN 1;
->END;
->//
mysql>delimiter ;
mysql>INSERT INTO t VALUES(unsafe_func());
The CREATE FUNCTION
and
INSERT
statements are written to the binary
log, so the slave will execute them. Because the slave SQL
thread has full privileges, it will execute the dangerous
statment. Thus, the function invocation has different effects
on the master and slave and is not replication-safe.
To guard against this danger for servers that have binary
logging enabled, stored function creators must have the
SUPER
privilege, in addition to the usual
CREATE ROUTINE
privilege that is required.
Similarly, to use ALTER FUNCTION
, you must
have the SUPER
privilege in addition to the
ALTER ROUTINE
privilege. Without the
SUPER
privilege, an error will occur:
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
If you do not want to require function creators to have the
SUPER
privilege (for example, if all users
with the CREATE ROUTINE
privilege on your
system are experienced application developers), set the global
log_bin_trust_function_creators
system
variable to 1. You can also set this variable by using the
--log-bin-trust-function-creators
option when
starting the server. If binary logging is not enabled,
log_bin_trust_function_creators
does not
apply and SUPER
is not required for
function creation.
If a function that performs updates is non-deterministic, it is not repeatable. This can have two undesirable effects:
It will make a slave different from the master.
Restored data will be different from the original data.
To deal with these problems, MySQL enforces the following requirement: On a master server, creation and alteration of a function is refused unless you declare the function to be deterministic or to not modify data. Two sets of function characteristics apply here:
The DETERMINISTIC
and NOT
DETERMINISTIC
characteristics indicate whether a
function always produces the same result for given inputs.
The default is NOT DETERMINISTIC
if
neither characteristic is given, so you must specify
DETERMINISTIC
explicitly to declare
that a function is deterministic.
Use of the NOW()
function (or its
synonyms) or RAND()
does not
necessarily make a function non-deterministic. For
NOW()
, the binary log includes the
timestamp and replicates correctly.
RAND()
also replicates correctly as
long as it is invoked only once within a function. (You
can consider the function execution timestamp and random
number seed as implicit inputs that are identical on the
master and slave.)
The CONTAINS SQL
, NO
SQL
, READS SQL DATA
, and
MODIFIES SQL DATA
characteristics
provide information about whether the function reads or
writes data. Either NO SQL
or
READS SQL DATA
indicates that a
function does not change data, but you must specify one of
these explicitly because the default is CONTAINS
SQL
if no characteristic is given.
By default, for a CREATE FUNCTION
statement
to be accepted, DETERMINISTIC
or one of
NO SQL
and READS SQL
DATA
must be specified explicitly. Otherwise an
error occurs:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
If you set log_bin_trust_function_creators
to 1, the requirement that functions be deterministic or not
modify data is dropped.
Assessment of the nature of a function is based on the
“honesty” of the creator: MySQL does not check
that a function declared DETERMINISTIC
contains no statements that produce non-deterministic results.
Stored procedure calls are logged at the statement level
rather than at the CALL
level. That is, the
server does not log the CALL
statement, it
logs those statements within the procedure that actually
execute. As a result, the same changes that occur on the
master will be observed on slave servers. This prevents
problems that could result from a procedure having different
execution paths on different machines.
In general, statements executed within a stored procedure are written to the binary log using the same rules that would apply were the statements to be executed in standalone fashion. Some special care is taken when logging procedure statements because statement execution within procedures is not quite the same as in non-procedure context:
A statement to be logged might contain references to local procedure variables. These variables do not exist outside of stored procedure context, so a statement that refers to such a variable cannot be logged literally. Instead, each reference to a local variable is replaced by this construct for logging purposes:
NAME_CONST(var_name
,var_value
)
var_name
is the local variable
name, and var_value
is a
constant indicating the value that the variable has at the
time the statement is logged.
NAME_CONST()
has a value of
var_value
, and a
“name” of
var_name
. Thus, if you invoke
this function directly, you get a result like this:
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+
NAME_CONST()
allows a logged standalone
statement to be executed on a slave with the same effect
as the original statement that was executed on the master
within a stored procedure.
A statement to be logged might contain references to
user-defined variables. To handle this, MySQL writes a
SET
statement to the binary log to make
sure that the variable exists on the slave with the same
value as on the master. For example, if a statement refers
to a variable @my_var
, that statement
will be preceded in the binary log by the following
statement, where value
is the
value of @my_var
on the master:
SET @my_var = value
;
Procedure calls can occur within a committed or
rolled-back transaction. Previously,
CALL
statements were logged even if
they occurred within a rolled-back transaction. As of
MySQL 5.0.12, transactional context is accounted for so
that the transactional aspects of procedure execution are
replicated correctly. That is, the server logs those
statements within the procedure that actually execute and
modify data, and also logs BEGIN
,
COMMIT
, and ROLLBACK
statements as necessary. For example, if a procedure
updates only transactional tables and is executed within a
transaction that is rolled back, those updates are not
logged. If the procedure occurs within a committed
transaction, BEGIN
and
COMMIT
statements are logged with the
updates. For a procedure that executes within a
rolled-back transaction, its statements are logged using
the same rules that would apply if the statements were
executed in standalone fashion:
Updates to transactional tables are not logged.
Updates to non-transactional tables are logged because rollback does not cancel them.
Updates to a mix of transactional and
non-transactional tables are logged surrounded by
BEGIN
and
ROLLBACK
so that slaves will make
the same changes and rollbacks as on the master.
A stored procedure call is not written to
the binary log at the statement level if the procedure is
invoked from within a stored function. In that case, the only
thing logged is the statement that invokes the function (if it
occurs within a statement that is logged) or a
DO
statement (if it occurs within a
statement that is not logged). For this reason, care should be
exercised in the use of stored functions that invoke a
procedure, even if the procedure is otherwise safe in itself.