<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 17. Stored Procedures and Functions</title><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="spatial-extensions-in-mysql.html" title="Chapter 16. Spatial Extensions in MySQL"><link rel="next" href="triggers.html" title="Chapter 18. Triggers"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 17. Stored Procedures and Functions</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="spatial-extensions-in-mysql.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="triggers.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="stored-procedures"></a>Chapter 17. Stored Procedures and Functions</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="stored-procedures.html#stored-procedure-privileges">17.1. Stored Procedures and the Grant Tables</a></span></dt><dt><span class="section"><a href="stored-procedures.html#stored-procedure-syntax">17.2. Stored Procedure Syntax</a></span></dt><dd><dl><dt><span class="section"><a href="stored-procedures.html#create-procedure">17.2.1. <code class="literal">CREATE PROCEDURE</code> and <code class="literal">CREATE FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#alter-procedure">17.2.2. <code class="literal">ALTER PROCEDURE</code> and <code class="literal">ALTER FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#drop-procedure">17.2.3. <code class="literal">DROP PROCEDURE</code> and <code class="literal">DROP FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#show-create-procedure">17.2.4. <code class="literal">SHOW CREATE PROCEDURE</code> and <code class="literal">SHOW CREATE FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#show-procedure-status">17.2.5. <code class="literal">SHOW PROCEDURE STATUS</code> and <code class="literal">SHOW FUNCTION STATUS</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#call">17.2.6. <code class="literal">CALL</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#begin-end">17.2.7. <code class="literal">BEGIN ... END</code> Compound Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#declare">17.2.8. <code class="literal">DECLARE</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#variables-in-stored-procedures">17.2.9. Variables in Stored Procedures</a></span></dt><dt><span class="section"><a href="stored-procedures.html#conditions-and-handlers">17.2.10. Conditions and Handlers</a></span></dt><dt><span class="section"><a href="stored-procedures.html#cursors">17.2.11. Cursors</a></span></dt><dt><span class="section"><a href="stored-procedures.html#flow-control-constructs">17.2.12. Flow Control Constructs</a></span></dt></dl></dd><dt><span class="section"><a href="stored-procedures.html#stored-procedure-replication-faq">17.3. Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions</a></span></dt><dt><span class="section"><a href="stored-procedures.html#stored-procedure-logging">17.4. Binary Logging of Stored Routines and Triggers</a></span></dt></dl></div><a class="indexterm" name="id3048321"></a><a class="indexterm" name="id3048328"></a><p>
Stored procedures and functions are supported 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.
</p><p>
Some situations where stored procedures can be particularly useful:
</p><div class="itemizedlist"><ul type="disc"><li><p>
When multiple client applications are written in different
languages or work on different platforms, but need to perform
the same database operations.
</p></li><li><p>
When security is paramount. Banks, for example, use stored
procedures for all common operations. This provides a consistent
and secure environment, and procedures can ensure that each
operation is properly logged. In such a setup, applications and
users would not get any access to the database tables directly,
but can only execute specific stored procedures.
</p></li></ul></div><p>
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.
</p><p>
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.
</p><p>
MySQL follows the SQL:2003 syntax for stored procedures, which is
also used by IBM's DB2.
</p><p>
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. Further
discussion of restrictions on use of stored procedures is given in
<a href="restrictions.html" title="Appendix I. Feature Restrictions">Appendix I, <i>Feature Restrictions</i></a>.
</p><p>
Binary logging for stored routines is done as described in
<a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="stored-procedure-privileges"></a>17.1. Stored Procedures and the Grant Tables</h2></div></div></div><p>
Stored procedures require the <code class="literal">proc</code> table in the
<code class="literal">mysql</code> 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
<code class="literal">proc</code> table exists. See
<a href="installing.html#upgrading-grant-tables" title="2.10.3. Upgrading the Grant Tables">Section 2.10.3, “Upgrading the Grant Tables”</a>.
</p><p>
Beginning with MySQL 5.0.3, the grant system has been modified to
take stored routines into account as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">CREATE ROUTINE</code> privilege is needed to
create stored routines.
</p></li><li><p>
The <code class="literal">ALTER ROUTINE</code> privilege is needed to
alter or drop stored routines. This privilege is granted
automatically to the creator of a routine.
</p></li><li><p>
The <code class="literal">EXECUTE</code> privilege is required to
execute stored routines. However, this privilege is granted
automatically to the creator of a routine. Also, the default
<code class="literal">SQL SECURITY</code> characteristic for a routine
is <code class="literal">DEFINER</code>, which allows users who have
access to the database with which the routine is associated to
execute the routine.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="stored-procedure-syntax"></a>17.2. Stored Procedure Syntax</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="stored-procedures.html#create-procedure">17.2.1. <code class="literal">CREATE PROCEDURE</code> and <code class="literal">CREATE FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#alter-procedure">17.2.2. <code class="literal">ALTER PROCEDURE</code> and <code class="literal">ALTER FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#drop-procedure">17.2.3. <code class="literal">DROP PROCEDURE</code> and <code class="literal">DROP FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#show-create-procedure">17.2.4. <code class="literal">SHOW CREATE PROCEDURE</code> and <code class="literal">SHOW CREATE FUNCTION</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#show-procedure-status">17.2.5. <code class="literal">SHOW PROCEDURE STATUS</code> and <code class="literal">SHOW FUNCTION STATUS</code></a></span></dt><dt><span class="section"><a href="stored-procedures.html#call">17.2.6. <code class="literal">CALL</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#begin-end">17.2.7. <code class="literal">BEGIN ... END</code> Compound Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#declare">17.2.8. <code class="literal">DECLARE</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#variables-in-stored-procedures">17.2.9. Variables in Stored Procedures</a></span></dt><dt><span class="section"><a href="stored-procedures.html#conditions-and-handlers">17.2.10. Conditions and Handlers</a></span></dt><dt><span class="section"><a href="stored-procedures.html#cursors">17.2.11. Cursors</a></span></dt><dt><span class="section"><a href="stored-procedures.html#flow-control-constructs">17.2.12. Flow Control Constructs</a></span></dt></dl></div><p>
Stored procedures and functions are routines that are created with
<code class="literal">CREATE PROCEDURE</code> and <code class="literal">CREATE
FUNCTION</code> statements. A routine is either a procedure or
a function. A procedure is invoked using a <code class="literal">CALL</code>
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.
</p><p>
As of MySQL 5.0.1, a stored procedure or function is associated
with a particular database. This has several implications:
</p><div class="itemizedlist"><ul type="disc"><li><p>
When the routine is invoked, an implicit <code class="literal">USE
<em class="replaceable"><code>db_name</code></em></code> is performed (and
undone when the routine terminates). <code class="literal">USE</code>
statements within stored routines are disallowed.
</p></li><li><p>
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
<code class="literal">p</code> or function <code class="literal">f</code> that is
associated with the <code class="literal">test</code> database, you can
say <code class="literal">CALL test.p()</code> or
<code class="literal">test.f()</code>.
</p></li><li><p>
When a database is dropped, all stored routines associated
with it are dropped as well.
</p></li></ul></div><p>
(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 <code class="literal">USE
<em class="replaceable"><code>db_name</code></em></code> is executed within
the routine, the original default database is restored upon
routine exit.)
</p><p>
MySQL supports the very useful extension that allows the use of
regular <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code> 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.
</p><p>
The following sections describe the syntax used to create, alter,
drop, and query stored procedures and functions.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="create-procedure"></a>17.2.1. <code class="literal">CREATE PROCEDURE</code> and <code class="literal">CREATE FUNCTION</code></h3></div></div></div><a class="indexterm" name="id3048680"></a><a class="indexterm" name="id3048689"></a><pre class="programlisting">CREATE PROCEDURE <em class="replaceable"><code>sp_name</code></em> ([<em class="replaceable"><code>proc_parameter</code></em>[,...]])
[<em class="replaceable"><code>characteristic</code></em> ...] <em class="replaceable"><code>routine_body</code></em>
CREATE FUNCTION <em class="replaceable"><code>sp_name</code></em> ([<em class="replaceable"><code>func_parameter</code></em>[,...]])
RETURNS <em class="replaceable"><code>type</code></em>
[<em class="replaceable"><code>characteristic</code></em> ...] <em class="replaceable"><code>routine_body</code></em>
<em class="replaceable"><code>proc_parameter</code></em>:
[ IN | OUT | INOUT ] <em class="replaceable"><code>param_name</code></em> <em class="replaceable"><code>type</code></em>
<em class="replaceable"><code>func_parameter</code></em>:
<em class="replaceable"><code>param_name</code></em> <em class="replaceable"><code>type</code></em>
<em class="replaceable"><code>type</code></em>:
<em class="replaceable"><code>Any valid MySQL data type</code></em>
<em class="replaceable"><code>characteristic</code></em>:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT '<em class="replaceable"><code>string</code></em>'
<em class="replaceable"><code>routine_body</code></em>:
<em class="replaceable"><code>Valid SQL procedure statement or statements</code></em>
</pre><p>
These statements create stored routines. As of MySQL 5.0.3, to
create a routine, it is necessary to have the <code class="literal">CREATE
ROUTINE</code> privilege, and the <code class="literal">ALTER
ROUTINE</code> and <code class="literal">EXECUTE</code> privileges are
granted automatically to its creator. If binary logging is
enabled, you may also need the <code class="literal">SUPER</code>
privilege, as described in
<a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><p>
By default, the routine is associated with the current database.
To associate the routine explicitly with a given database,
specify the name as <em class="replaceable"><code>db_name.sp_name</code></em>
when you create it.
</p><p>
If the routine name is the same as the name of a built-in SQL
function, you need to 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 — even though it is possible to do
so — we suggest that it is better to avoid re-using the
names of existing SQL functions for your own stored routines.
</p><p>
The parameter list enclosed within parentheses must always be
present. If there are no parameters, an empty parameter list of
<code class="literal">()</code> should be used. Each parameter is an
<code class="literal">IN</code> parameter by default. To specify otherwise
for a parameter, use the keyword <code class="literal">OUT</code> or
<code class="literal">INOUT</code> before the parameter name.
</p><p>
<span class="bold"><strong>Note</strong></span>: Specifying a parameter as
<code class="literal">IN</code>, <code class="literal">OUT</code>, or
<code class="literal">INOUT</code> is valid only for a
<code class="literal">PROCEDURE</code>. (<code class="literal">FUNCTION</code>
parameters are always regarded as <code class="literal">IN</code>
parameters.)
</p><p>
The <code class="literal">RETURNS</code> clause may be specified only for
a <code class="literal">FUNCTION</code>, for which it is mandatory. It is
used to indicate the return type of the function, and the
function body must contain a <code class="literal">RETURN value</code>
statement.
</p><p>
The <em class="replaceable"><code>routine_body</code></em> consists of valid
SQL procedure statements. Compound statement syntax can be used,
as described in <a href="stored-procedures.html#begin-end" title="17.2.7. BEGIN ... END Compound Statement">Section 17.2.7, “<code class="literal">BEGIN ... END</code> Compound Statement”</a>. 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, <a href="stored-procedures.html#declare" title="17.2.8. DECLARE Statement">Section 17.2.8, “<code class="literal">DECLARE</code> Statement”</a> and
<a href="stored-procedures.html#flow-control-constructs" title="17.2.12. Flow Control Constructs">Section 17.2.12, “Flow Control Constructs”</a>
</p><p>
The <code class="literal">CREATE FUNCTION</code> statement was used in
earlier versions of MySQL to support UDFs (User Defined
Functions). See <a href="extending-mysql.html#adding-functions" title="24.2. Adding New Functions to MySQL">Section 24.2, “Adding New Functions to MySQL”</a>. 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.
</p><p>
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 is PHP because the core PHP engine is
small, thread-safe, and can easily be embedded. Because the
framework is public, it is expected that many other languages
can also be supported.
</p><p>
A procedure or function is considered
“<span class="quote">deterministic</span>” if it always produces the same
result for the same input parameters, and “<span class="quote">not
deterministic</span>” otherwise. If neither
<code class="literal">DETERMINISTIC</code> nor <code class="literal">NOT
DETERMINISTIC</code> is given, the default is <code class="literal">NOT
DETERMINISTIC</code>.
</p><p>
For replication purposes, use of the <code class="literal">NOW()</code>
function (or its synonyms) or <code class="literal">RAND()</code> does not
necessarily make a routine non-deterministic. For
<code class="literal">NOW()</code>, the binary log includes the timestamp
and replicates correctly. <code class="literal">RAND()</code> 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.)
</p><p>
Currently, the <code class="literal">DETERMINISTIC</code> characteristic
is accepted, but not yet used by the optimizer. However, if
binary logging is enabled, this characteristic affects whether
MySQL accepts routine definitions. See
<a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><p>
Several characteristics provide information about the nature of
data use by the routine. <code class="literal">CONTAINS SQL</code>
indicates that the routine does not contain statements that read
or write data. <code class="literal">NO SQL</code> indicates the the
routine contains no SQL statements. <code class="literal">READS SQL
DATA</code> indicates that the routine contains statements
that read data, but not statements that write data.
<code class="literal">MODIFIES SQL DATA</code> indicates that the routine
contains statements that may write data. <code class="literal">CONTAINS
SQL</code> is the default if none of these characteristics is
given explicitly.
</p><p>
The <code class="literal">SQL SECURITY</code> 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 <code class="literal">DEFINER</code>.
This feature is new in SQL:2003. The creator or invoker must
have permission to access the database with which the routine is
associated. As of MySQL 5.0.3, it is necessary to have the
<code class="literal">EXECUTE</code> 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 <code class="literal">SQL
SECURITY</code> characteristic is set.
</p><p>
MySQL stores the <code class="literal">sql_mode</code> system variable
setting that is in effect at the time a routine is created, and
always executes the routine with this setting in force.
</p><p>
The <code class="literal">COMMENT</code> clause is a MySQL extension, and
may be used to describe the stored procedure. This information
is displayed by the <code class="literal">SHOW CREATE PROCEDURE</code> and
<code class="literal">SHOW CREATE FUNCTION</code> statements.
</p><p>
MySQL allows routines to contain DDL statements, such as
<code class="literal">CREATE</code> and <code class="literal">DROP</code>. MySQL
also allows stored procedures (but not stored functions) to
contain SQL transaction statements such as
<code class="literal">COMMIT</code>. 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.
</p><p>
Stored routines cannot use <code class="literal">LOAD DATA INFILE</code>.
</p><p>
Statements that return a result set cannot be used within a
stored function. This includes <code class="literal">SELECT</code>
statements that do not use <code class="literal">INTO</code> to fetch
column values into variables, <code class="literal">SHOW</code>
statements, and other statements such as
<code class="literal">EXPLAIN</code>. For statements that can be
determined at function definition time to return a result set, a
<code class="literal">Not allowed to return a result set from a
function</code> error occurs
(<code class="literal">ER_SP_NO_RETSET_IN_FUNC</code>). For statements
that can be determined only at runtime to return a result set, a
<code class="literal">PROCEDURE %s can't return a result set in the given
context</code> error occurs
(<code class="literal">ER_SP_BADSELECT</code>).
</p><p>
<span class="bold"><strong>Note</strong></span>: Before MySQL 5.0.10,
stored functions created with <code class="literal">CREATE FUNCTION</code>
must not contain references to tables, with limited execeptions.
They may include some <code class="literal">SET</code> statements that
contain table references, for example <code class="literal">SET a:= (SELECT
MAX(id) FROM t)</code>, and <code class="literal">SELECT</code>
statements that fetch values directly into variables, for
example <code class="literal">SELECT i INTO var1 FROM t</code>.
</p><p>
The following is an example of a simple stored procedure that
uses an <code class="literal">OUT</code> parameter. The example uses the
<span><strong class="command">mysql</strong></span> client <code class="literal">delimiter</code>
command to change the statement delimiter from
<code class="literal">;</code> to <code class="literal">//</code> while the
procedure is being defined. This allows the <code class="literal">;</code>
delimiter used in the procedure body to be passed through to the
server rather than being interpreted by <span><strong class="command">mysql</strong></span>
itself.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE PROCEDURE simpleproc (OUT param1 INT)</code></strong>
-> <strong class="userinput"><code>BEGIN</code></strong>
-> <strong class="userinput"><code>SELECT COUNT(*) INTO param1 FROM t;</code></strong>
-> <strong class="userinput"><code>END</code></strong>
-> <strong class="userinput"><code>//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>delimiter ;</code></strong>
mysql> <strong class="userinput"><code>CALL simpleproc(@a);</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @a;</code></strong>
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
</pre><p>
When using the <code class="literal">delimiter</code> command, you should
avoid the use of the backslash
(‘<code class="literal">\</code>’) character because that is
the escape character for MySQL.
</p><p>
The following is an example of a function that takes a
parameter, performs an operation using an SQL function, and
returns the result:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)</code></strong>
-> <strong class="userinput"><code>RETURN CONCAT('Hello, ',s,'!');</code></strong>
-> <strong class="userinput"><code>//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>delimiter ;</code></strong>
mysql> <strong class="userinput"><code>SELECT hello('world');</code></strong>
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
</pre><p>
If the <code class="literal">RETURN</code> statement in a stored function
returns a value that has a different type than is specified in
the function's <code class="literal">RETURNS</code> clause, the return
value is coerced to the proper type. For example, if a function
returns an <code class="literal">ENUM</code> or <code class="literal">SET</code>
value, but the <code class="literal">RETURN</code> statement returns an
integer, the value returned from the function is the string for
the corresponding <code class="literal">ENUM</code> member of set of
<code class="literal">SET</code> members.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="alter-procedure"></a>17.2.2. <code class="literal">ALTER PROCEDURE</code> and <code class="literal">ALTER FUNCTION</code></h3></div></div></div><a class="indexterm" name="id3049413"></a><a class="indexterm" name="id3049422"></a><pre class="programlisting">ALTER {PROCEDURE | FUNCTION} <em class="replaceable"><code>sp_name</code></em> [<em class="replaceable"><code>characteristic</code></em> ...]
<em class="replaceable"><code>characteristic</code></em>:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT '<em class="replaceable"><code>string</code></em>'
</pre><p>
This statement can be used to change the characteristics of a
stored procedure or function. You must have the <code class="literal">ALTER
ROUTINE</code> privilege for the routine as of MySQL 5.0.3.
This privilege is granted automatically to the routine creator.
If binary logging is enabled, you may also need the
<code class="literal">SUPER</code> privilege, as described in
<a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><p>
More than one change may be specified in an <code class="literal">ALTER
PROCEDURE</code> or <code class="literal">ALTER FUNCTION</code>
statement.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drop-procedure"></a>17.2.3. <code class="literal">DROP PROCEDURE</code> and <code class="literal">DROP FUNCTION</code></h3></div></div></div><a class="indexterm" name="id3049509"></a><a class="indexterm" name="id3049518"></a><pre class="programlisting">DROP {PROCEDURE | FUNCTION} [IF EXISTS] <em class="replaceable"><code>sp_name</code></em>
</pre><p>
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 <code class="literal">ALTER ROUTINE</code> privilege for the
routine as of MySQL 5.0.3. This privilege is granted
automatically to the routine creator.
</p><p>
The <code class="literal">IF EXISTS</code> 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
<code class="literal">SHOW WARNINGS</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="show-create-procedure"></a>17.2.4. <code class="literal">SHOW CREATE PROCEDURE</code> and <code class="literal">SHOW CREATE FUNCTION</code></h3></div></div></div><a class="indexterm" name="id3049578"></a><a class="indexterm" name="id3049587"></a><pre class="programlisting">SHOW CREATE {PROCEDURE | FUNCTION} <em class="replaceable"><code>sp_name</code></em>
</pre><p>
This statement is a MySQL extension. Similar to <code class="literal">SHOW
CREATE TABLE</code>, it returns the exact string that can be
used to re-create the named routine.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CREATE FUNCTION test.hello\G</code></strong>
*************************** 1. row ***************************
Function: hello
sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="show-procedure-status"></a>17.2.5. <code class="literal">SHOW PROCEDURE STATUS</code> and <code class="literal">SHOW FUNCTION STATUS</code></h3></div></div></div><a class="indexterm" name="id3049642"></a><a class="indexterm" name="id3049651"></a><pre class="programlisting">SHOW {PROCEDURE | FUNCTION} STATUS [LIKE '<em class="replaceable"><code>pattern</code></em>']
</pre><p>
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.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW FUNCTION STATUS LIKE 'hello'\G</code></strong>
*************************** 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:
</pre><p>
You can also get information about stored routines from the
<code class="literal">ROUTINES</code> table in
<code class="literal">INFORMATION_SCHEMA</code>. See
<a href="information-schema.html#routines-table" title="20.1.14. The INFORMATION_SCHEMA ROUTINES Table">Section 20.1.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="call"></a>17.2.6. <code class="literal">CALL</code> Statement</h3></div></div></div><a class="indexterm" name="id3049720"></a><pre class="programlisting">CALL <em class="replaceable"><code>sp_name</code></em>([<em class="replaceable"><code>parameter</code></em>[,...]])
</pre><p>
The <code class="literal">CALL</code> statement invokes a procedure that
was defined previously with <code class="literal">CREATE PROCEDURE</code>.
</p><p>
<code class="literal">CALL</code> can pass back values to its caller using
parameters that are declared as <code class="literal">OUT</code> or
<code class="literal">INOUT</code> parameters. It also
“<span class="quote">returns</span>” the number of rows affected, which a
client program can obtain at the SQL level by calling the
<code class="literal">ROW_COUNT()</code> function and from C by calling
the <code class="literal">mysql_affected_rows()</code> C API function.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="begin-end"></a>17.2.7. <code class="literal">BEGIN ... END</code> Compound Statement</h3></div></div></div><a class="indexterm" name="id3049796"></a><a class="indexterm" name="id3049805"></a><pre class="programlisting">[<em class="replaceable"><code>begin_label</code></em>:] BEGIN
[<em class="replaceable"><code>statement_list</code></em>]
END [<em class="replaceable"><code>end_label</code></em>]
</pre><p>
Stored routines may contain multiple statements, using a
<code class="literal">BEGIN ... END</code> compound statement.
<em class="replaceable"><code>statement_list</code></em> represents a list of
one or more statements. Each statement within
<em class="replaceable"><code>statement_list</code></em> must be terminated by
a semicolon character (<code class="literal">;</code>).
</p><p>
A compound statement can be labeled.
<em class="replaceable"><code>end_label</code></em> cannot be given unless
<em class="replaceable"><code>begin_label</code></em> also is present, and if
both are present, they must be the same.
</p><p>
Please note that the optional <code class="literal">[NOT] ATOMIC</code>
clause is not yet supported. This means that no transactional
savepoint is set at the start of the instruction block and the
<code class="literal">BEGIN</code> clause used in this context has no
effect on the current transaction.
</p><p>
Using multiple statements requires that a client is able to send
query strings containing the <code class="literal">;</code> statement
delimiter. This is handled in the <span><strong class="command">mysql</strong></span>
command-line client with the <code class="literal">delimiter</code>
command. Changing the <code class="literal">;</code> end-of-query
delimiter (for example, to <code class="literal">//</code>) allows
<code class="literal">;</code> to be used in a routine body.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="declare"></a>17.2.8. <code class="literal">DECLARE</code> Statement</h3></div></div></div><a class="indexterm" name="id3049922"></a><p>
The <code class="literal">DECLARE</code> statement is used to define
various items local to a routine: local variables (see
<a href="stored-procedures.html#variables-in-stored-procedures" title="17.2.9. Variables in Stored Procedures">Section 17.2.9, “Variables in Stored Procedures”</a>), conditions
and handlers (see <a href="stored-procedures.html#conditions-and-handlers" title="17.2.10. Conditions and Handlers">Section 17.2.10, “Conditions and Handlers”</a>) and
cursors (see <a href="stored-procedures.html#cursors" title="17.2.11. Cursors">Section 17.2.11, “Cursors”</a>).
<code class="literal">SIGNAL</code> and <code class="literal">RESIGNAL</code>
statements are not currently supported.
</p><p>
<code class="literal">DECLARE</code> may be used only inside a
<code class="literal">BEGIN ... END</code> compound statement and must be
at its start, before any other statements.
</p><p>
Cursors must be declared before declaring handlers, and
variables and conditions must be declared before declaring
either cursors or handlers.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="variables-in-stored-procedures"></a>17.2.9. Variables in Stored Procedures</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="stored-procedures.html#declare-local-variables">17.2.9.1. <code class="literal">DECLARE</code> Local Variables</a></span></dt><dt><span class="section"><a href="stored-procedures.html#set-statement">17.2.9.2. Variable <code class="literal">SET</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#select-into-statement">17.2.9.3. <code class="literal">SELECT ... INTO</code> Statement</a></span></dt></dl></div><p>
You may declare and use variables within a routine.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="declare-local-variables"></a>17.2.9.1. <code class="literal">DECLARE</code> Local Variables</h4></div></div></div><pre class="programlisting">DECLARE <em class="replaceable"><code>var_name</code></em>[,...] <em class="replaceable"><code>type</code></em> [DEFAULT <em class="replaceable"><code>value</code></em>]
</pre><p>
This statement is used to declare local variables. To provide
a default value for the variable, include a
<code class="literal">DEFAULT</code> clause. The value can be specified
as an expression; it need not be a constant. If the
<code class="literal">DEFAULT</code> clause is missing, the initial
value is <code class="literal">NULL</code>.
</p><p>
The scope of a local variable is within the <code class="literal">BEGIN ...
END</code> block where it is declared. It can be used in
nested blocks except those that declare a variable with the
same name.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="set-statement"></a>17.2.9.2. Variable <code class="literal">SET</code> Statement</h4></div></div></div><a class="indexterm" name="id3050072"></a><pre class="programlisting">SET <em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em> [, <em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em>] ...
</pre><p>
The <code class="literal">SET</code> statement in stored procedures is
an extended version of the general <code class="literal">SET</code>
statement. Referenced variables may be ones declared inside a
routine, or global server variables.
</p><p>
The <code class="literal">SET</code> statement in stored procedures is
implemented as part of the pre-existing <code class="literal">SET</code>
syntax. This allows an extended syntax of <code class="literal">SET a=x,
b=y, ...</code> 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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="select-into-statement"></a>17.2.9.3. <code class="literal">SELECT ... INTO</code> Statement</h4></div></div></div><a class="indexterm" name="id3050149"></a><pre class="programlisting">SELECT <em class="replaceable"><code>col_name</code></em>[,...] INTO <em class="replaceable"><code>var_name</code></em>[,...] <em class="replaceable"><code>table_expr</code></em>
</pre><p>
This <code class="literal">SELECT</code> syntax stores selected columns
directly into variables. Therefore, only a single row may be
retrieved.
</p><pre class="programlisting">SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
</pre><p>
Note that user variable names are <span class="emphasis"><em>not</em></span>
case sensitive in MySQL 5.0. See
<a href="language-structure.html#variables" title="9.3. User Variables">Section 9.3, “User Variables”</a>.
</p><p>
<span class="bold"><strong>Important</strong></span>: SQL variable names
should not be the same as column names. If an SQL statement,
such as a <code class="literal">SELECT ... INTO</code> 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, <code class="literal">xname</code> is interpreted as a
reference to the <code class="literal">xname</code>
<span class="emphasis"><em>variable</em></span> rather than the
<code class="literal">xname</code> <span class="emphasis"><em>column</em></span>:
</p><pre class="programlisting">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;
</pre><p>
When this procedure is called, the <code class="literal">newname</code>
variable will return the value <code class="literal">'bob'</code>
regardless of the value of the <code class="literal">table1.xname</code>
column.
</p><p>
See also <a href="restrictions.html#routine-restrictions" title="I.1. Restrictions on Stored Routines and Triggers">Section I.1, “Restrictions on Stored Routines and Triggers”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="conditions-and-handlers"></a>17.2.10. Conditions and Handlers</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="stored-procedures.html#declare-conditions">17.2.10.1. <code class="literal">DECLARE</code> Conditions</a></span></dt><dt><span class="section"><a href="stored-procedures.html#declare-handlers">17.2.10.2. <code class="literal">DECLARE</code> Handlers</a></span></dt></dl></div><p>
Certain conditions may require specific handling. These
conditions can relate to errors, as well as general flow control
inside a routine.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="declare-conditions"></a>17.2.10.1. <code class="literal">DECLARE</code> Conditions</h4></div></div></div><a class="indexterm" name="id3050302"></a><pre class="programlisting">DECLARE <em class="replaceable"><code>condition_name</code></em> CONDITION FOR <em class="replaceable"><code>condition_value</code></em>
<em class="replaceable"><code>condition_value</code></em>:
SQLSTATE [VALUE] <em class="replaceable"><code>sqlstate_value</code></em>
| <em class="replaceable"><code>mysql_error_code</code></em>
</pre><p>
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
<code class="literal">DECLARE HANDLER</code> statement. See
<a href="stored-procedures.html#declare-handlers" title="17.2.10.2. DECLARE Handlers">Section 17.2.10.2, “<code class="literal">DECLARE</code> Handlers”</a>.
</p><p>
In addition to SQLSTATE values, MySQL error codes are also
supported.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="declare-handlers"></a>17.2.10.2. <code class="literal">DECLARE</code> Handlers</h4></div></div></div><a class="indexterm" name="id3050366"></a><pre class="programlisting">DECLARE <em class="replaceable"><code>handler_type</code></em> HANDLER FOR <em class="replaceable"><code>condition_value</code></em>[,...] <em class="replaceable"><code>sp_statement</code></em>
<em class="replaceable"><code>handler_type</code></em>:
CONTINUE
| EXIT
| UNDO
<em class="replaceable"><code>condition_value</code></em>:
SQLSTATE [VALUE] <em class="replaceable"><code>sqlstate_value</code></em>
| <em class="replaceable"><code>condition_name</code></em>
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| <em class="replaceable"><code>mysql_error_code</code></em>
</pre><p>
This statement specifies handlers that each may deal with one
or more conditions. If one of these conditions occurs, the
specified statement is executed.
</p><p>
For a <code class="literal">CONTINUE</code> handler, execution of the
current routine continues after execution of the handler
statement. For an <code class="literal">EXIT</code> handler, execution
of the current <code class="literal">BEGIN...END</code> compound
statement is terminated. The <code class="literal">UNDO</code> handler
type statement is not yet supported.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">SQLWARNING</code> is shorthand for all
SQLSTATE codes that begin with <code class="literal">01</code>.
</p></li><li><p>
<code class="literal">NOT FOUND</code> is shorthand for all SQLSTATE
codes that begin with <code class="literal">02</code>.
</p></li><li><p>
<code class="literal">SQLEXCEPTION</code> is shorthand for all
SQLSTATE codes not caught by <code class="literal">SQLWARNING</code>
or <code class="literal">NOT FOUND</code>.
</p></li></ul></div><p>
In addition to SQLSTATE values, MySQL error codes are also
supported.
</p><p>
For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE test.t (s1 int,primary key (s1));</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE PROCEDURE handlerdemo ()</code></strong>
-> <strong class="userinput"><code>BEGIN</code></strong>
-> <strong class="userinput"><code>DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;</code></strong>
-> <strong class="userinput"><code>SET @x = 1;</code></strong>
-> <strong class="userinput"><code>INSERT INTO test.t VALUES (1);</code></strong>
-> <strong class="userinput"><code>SET @x = 2;</code></strong>
-> <strong class="userinput"><code>INSERT INTO test.t VALUES (1);</code></strong>
-> <strong class="userinput"><code>SET @x = 3;</code></strong>
-> <strong class="userinput"><code>END;</code></strong>
-> <strong class="userinput"><code>//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>CALL handlerdemo()//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @x//</code></strong>
+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
</pre><p>
Notice that <code class="literal">@x</code> is <code class="literal">3</code>,
which shows that MySQL executed to the end of the procedure.
If the line <code class="literal">DECLARE CONTINUE HANDLER FOR SQLSTATE
'23000' SET @x2 = 1;</code> had not been present, MySQL
would have taken the default (<code class="literal">EXIT</code>) path
after the second <code class="literal">INSERT</code> failed due to the
<code class="literal">PRIMARY KEY</code> constraint, and <code class="literal">SELECT
@x</code> would have returned <code class="literal">2</code>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="cursors"></a>17.2.11. Cursors</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="stored-procedures.html#declare-cursors">17.2.11.1. Declaring Cursors</a></span></dt><dt><span class="section"><a href="stored-procedures.html#open">17.2.11.2. Cursor <code class="literal">OPEN</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#fetch">17.2.11.3. Cursor <code class="literal">FETCH</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#close">17.2.11.4. Cursor <code class="literal">CLOSE</code> Statement</a></span></dt></dl></div><a class="indexterm" name="id3050613"></a><p>
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.
</p><p>
Cursors must be declared before declaring handlers, and
variables and conditions must be declared before declaring
either cursors or handlers.
</p><p>
For example:
</p><pre class="programlisting">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
</pre><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="declare-cursors"></a>17.2.11.1. Declaring Cursors</h4></div></div></div><pre class="programlisting">DECLARE <em class="replaceable"><code>cursor_name</code></em> CURSOR FOR <em class="replaceable"><code>select_statement</code></em>
</pre><p>
This statement declares a cursor. Multiple cursors may be
defined in a routine, but each cursor in a block must have a
unique name.
</p><p>
The <code class="literal">SELECT</code> statement cannot have an
<code class="literal">INTO</code> clause.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="open"></a>17.2.11.2. Cursor <code class="literal">OPEN</code> Statement</h4></div></div></div><a class="indexterm" name="id3050709"></a><pre class="programlisting">OPEN <em class="replaceable"><code>cursor_name</code></em>
</pre><p>
This statement opens a previously declared cursor.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="fetch"></a>17.2.11.3. Cursor <code class="literal">FETCH</code> Statement</h4></div></div></div><a class="indexterm" name="id3050744"></a><pre class="programlisting">FETCH <em class="replaceable"><code>cursor_name</code></em> INTO <em class="replaceable"><code>var_name</code></em> [, <em class="replaceable"><code>var_name</code></em>] ...
</pre><p>
This statement fetches the next row (if a row exists) using
the specified open cursor, and advances the cursor pointer.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="close"></a>17.2.11.4. Cursor <code class="literal">CLOSE</code> Statement</h4></div></div></div><a class="indexterm" name="id3050789"></a><pre class="programlisting">CLOSE <em class="replaceable"><code>cursor_name</code></em>
</pre><p>
This statement closes a previously opened cursor.
</p><p>
If not closed explicitly, a cursor is closed at the end of the
compound statement in which it was declared.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="flow-control-constructs"></a>17.2.12. Flow Control Constructs</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="stored-procedures.html#if-statement">17.2.12.1. <code class="literal">IF</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#case-statement">17.2.12.2. <code class="literal">CASE</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#loop-statement">17.2.12.3. <code class="literal">LOOP</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#leave-statement">17.2.12.4. <code class="literal">LEAVE</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#iterate-statement">17.2.12.5. <code class="literal">ITERATE</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#repeat-statement">17.2.12.6. <code class="literal">REPEAT</code> Statement</a></span></dt><dt><span class="section"><a href="stored-procedures.html#while-statement">17.2.12.7. <code class="literal">WHILE</code> Statement</a></span></dt></dl></div><p>
The <code class="literal">IF</code>, <code class="literal">CASE</code>,
<code class="literal">LOOP</code>, <code class="literal">WHILE</code>,
<code class="literal">ITERATE</code>, and <code class="literal">LEAVE</code>
constructs are fully implemented.
</p><p>
These constructs may each contain either a single statement, or
a block of statements using the <code class="literal">BEGIN ... END</code>
compound statement. Constructs may be nested.
</p><p>
<code class="literal">FOR</code> loops are not currently supported.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="if-statement"></a>17.2.12.1. <code class="literal">IF</code> Statement</h4></div></div></div><a class="indexterm" name="id3050884"></a><pre class="programlisting">IF <em class="replaceable"><code>search_condition</code></em> THEN <em class="replaceable"><code>statement_list</code></em>
[ELSEIF <em class="replaceable"><code>search_condition</code></em> THEN <em class="replaceable"><code>statement_list</code></em>] ...
[ELSE <em class="replaceable"><code>statement_list</code></em>]
END IF
</pre><p>
<code class="literal">IF</code> implements a basic conditional
construct. If the <em class="replaceable"><code>search_condition</code></em>
evaluates to true, the corresponding SQL statement list is
executed. If no <em class="replaceable"><code>search_condition</code></em>
matches, the statement list in the <code class="literal">ELSE</code>
clause is executed. <em class="replaceable"><code>statement_list</code></em>
can consist of one or more statements.
</p><p>
Please note that there is also an <code class="literal">IF()</code>
<span class="emphasis"><em>function</em></span>, which differs from the
<code class="literal">IF</code> <span class="emphasis"><em>statement</em></span> described
here.. See <a href="functions.html#control-flow-functions" title="12.2. Control Flow Functions">Section 12.2, “Control Flow Functions”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="case-statement"></a>17.2.12.2. <code class="literal">CASE</code> Statement</h4></div></div></div><a class="indexterm" name="id3050979"></a><pre class="programlisting">CASE <em class="replaceable"><code>case_value</code></em>
WHEN <em class="replaceable"><code>when_value</code></em> THEN <em class="replaceable"><code>statement_list</code></em>
[WHEN <em class="replaceable"><code>when_value</code></em> THEN <em class="replaceable"><code>statement_list</code></em>] ...
[ELSE <em class="replaceable"><code>statement_list</code></em>]
END CASE
</pre><p>
Or:
</p><pre class="programlisting">CASE
WHEN <em class="replaceable"><code>search_condition</code></em> THEN <em class="replaceable"><code>statement_list</code></em>
[WHEN <em class="replaceable"><code>search_condition</code></em> THEN <em class="replaceable"><code>statement_list</code></em>] ...
[ELSE <em class="replaceable"><code>statement_list</code></em>]
END CASE
</pre><p>
The <code class="literal">CASE</code> statement for stored procedures
implements a complex conditional construct. If a
<em class="replaceable"><code>search_condition</code></em> evaluates to true,
the corresponding SQL statement is executed. If no search
condition matches, the statement in the
<code class="literal">ELSE</code> clause is executed.
</p><p>
<span class="bold"><strong>Note</strong></span>: The syntax of a
<code class="literal">CASE</code> <span class="emphasis"><em>statement</em></span> shown
here for use inside a stored procedure differs slightly from
that of the SQL <code class="literal">CASE</code>
<span class="emphasis"><em>expression</em></span> described in
<a href="functions.html#control-flow-functions" title="12.2. Control Flow Functions">Section 12.2, “Control Flow Functions”</a>. The
<code class="literal">CASE</code> statement cannot have an <code class="literal">ELSE
NULL</code> clause, and it is terminated with <code class="literal">END
CASE</code> instead of <code class="literal">END</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="loop-statement"></a>17.2.12.3. <code class="literal">LOOP</code> Statement</h4></div></div></div><a class="indexterm" name="id3051121"></a><pre class="programlisting">[<em class="replaceable"><code>begin_label</code></em>:] LOOP
<em class="replaceable"><code>statement_list</code></em>
END LOOP [<em class="replaceable"><code>end_label</code></em>]
</pre><p>
<code class="literal">LOOP</code> 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
<code class="literal">LEAVE</code> statement.
</p><p>
A <code class="literal">LOOP</code> statement can be labeled.
<em class="replaceable"><code>end_label</code></em> cannot be given unless
<em class="replaceable"><code>begin_label</code></em> also is present, and if
both are present, they must be the same.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="leave-statement"></a>17.2.12.4. <code class="literal">LEAVE</code> Statement</h4></div></div></div><a class="indexterm" name="id3051191"></a><pre class="programlisting">LEAVE <em class="replaceable"><code>label</code></em>
</pre><p>
This statement is used to exit any labeled flow control
construct. It can be used with <code class="literal">BEGIN ...
END</code> or loops.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="iterate-statement"></a>17.2.12.5. <code class="literal">ITERATE</code> Statement</h4></div></div></div><a class="indexterm" name="id3051230"></a><pre class="programlisting">ITERATE <em class="replaceable"><code>label</code></em>
</pre><p>
<code class="literal">ITERATE</code> can only appear within
<code class="literal">LOOP</code>, <code class="literal">REPEAT</code>, and
<code class="literal">WHILE</code> statements.
<code class="literal">ITERATE</code> means “<span class="quote">do the loop
again.</span>”
</p><p>
For example:
</p><pre class="programlisting">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
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="repeat-statement"></a>17.2.12.6. <code class="literal">REPEAT</code> Statement</h4></div></div></div><a class="indexterm" name="id3051296"></a><a class="indexterm" name="id3051305"></a><pre class="programlisting">[<em class="replaceable"><code>begin_label</code></em>:] REPEAT
<em class="replaceable"><code>statement_list</code></em>
UNTIL <em class="replaceable"><code>search_condition</code></em>
END REPEAT [<em class="replaceable"><code>end_label</code></em>]
</pre><p>
The statement or statements within a <code class="literal">REPEAT</code>
statement are repeated until the
<em class="replaceable"><code>search_condition</code></em> is true.
</p><p>
A <code class="literal">REPEAT</code> statement can be labeled.
<em class="replaceable"><code>end_label</code></em> cannot be given unless
<em class="replaceable"><code>begin_label</code></em> also is present, and if
both are present, they must be the same.
</p><p>
For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE PROCEDURE dorepeat(p1 INT)</code></strong>
-> <strong class="userinput"><code>BEGIN</code></strong>
-> <strong class="userinput"><code>SET @x = 0;</code></strong>
-> <strong class="userinput"><code>REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;</code></strong>
-> <strong class="userinput"><code>END</code></strong>
-> <strong class="userinput"><code>//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>CALL dorepeat(1000)//</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @x//</code></strong>
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="while-statement"></a>17.2.12.7. <code class="literal">WHILE</code> Statement</h4></div></div></div><a class="indexterm" name="id3051423"></a><pre class="programlisting">[<em class="replaceable"><code>begin_label</code></em>:] WHILE <em class="replaceable"><code>search_condition</code></em> DO
<em class="replaceable"><code>statement_list</code></em>
END WHILE [<em class="replaceable"><code>end_label</code></em>]
</pre><p>
The statement or statements within a <code class="literal">WHILE</code>
statement are repeated as long as the
<em class="replaceable"><code>search_condition</code></em> is true.
</p><p>
A <code class="literal">WHILE</code> statement can be labeled.
<em class="replaceable"><code>end_label</code></em> cannot be given unless
<em class="replaceable"><code>begin_label</code></em> also is present, and if
both are present, they must be the same.
</p><p>
For example:
</p><pre class="programlisting">CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END
</pre></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="stored-procedure-replication-faq"></a>17.3. Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions</h2></div></div></div><div class="itemizedlist"><ul type="disc"><li><p>
Do MySQL 5.0 stored procedures and functions work
with replication?
</p><p>
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 <a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p></li><li><p>
Are stored procedures and functions created on a master server
replicated to a slave?
</p><p>
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. <code class="literal">ALTER</code> and <code class="literal">DROP</code>
statements for stored procedures and functions are also
replicated.
</p></li><li><p>
How are actions that take place inside stored procedures and
functions replicated?
</p><p>
MySQL records each DML event that occurs in a stored procedure
and function and replicates those individual actions to a
slave server. The actual calls made to execute stored
procedures and functions are not replicated.
</p></li><li><p>
Are there special security requirements for using stored
procedures, functions and replication together?
</p><p>
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 procedures and 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:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Any user wishing to create stored procedures must be
granted the <code class="literal">SUPER</code> privilege.
</p></li><li><p>
Alternatively, a DBA can set the
<code class="literal">log_bin_trust_routine_creators</code> system
variable to 1, which will allow anyone with the standard
<code class="literal">CREATE ROUTINE</code> privilege to create
stored procedures and functions.
</p></li></ul></div></li><li><p>
What limitations exist for replicating stored procedure and
function actions?
</p><p>
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 cloned, and therefore, random actions
replicated to a slave will not mirror those produced on a
master. Note that declaring stored procedures or functions to
be <code class="literal">DETERMINISTIC</code> or setting the
<code class="literal">log_bin_trust_routine_creators</code> system
variable to 0 will not allow random-valued operations to be
invoked.
</p><p>
In addition, time-based actions cannot be reproduced on a
slave as the timing of such actions in a stored procedure is
not reproducible through the binary log used for replication
as it only records DML events and does not factor in timing
constraints.
</p><p>
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
<code class="literal">IGNORE</code> keyword so that updates on the
master that cause errors are ignored and updates that do not
cause errors are replicated to the slave.
</p></li><li><p>
Do the limitations described above affect MySQL's ability to
do point-in-time recovery?
</p><p>
The same limitations that affect replication do affect
point-in-time recovery.
</p></li><li><p>
What will MySQL do to correct the aforementioned limitations?
</p><p>
A future release of MySQL is expected to feature a choice in
how replication should be handled:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Statement-based replication (current implementation).
</p></li><li><p>
Row-level replication (that will solve all the limitations
described earlier).
</p></li></ul></div></li><li><p>
Do triggers work with replication?
</p><p>
Triggers and replication in MySQL 5.0 work the
same as in most other database engines in that 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 tables that
exist on any MySQL slave servers so that the triggers activate
on the slaves as well as the master.
</p></li><li><p>
How are actions carried out through triggers on a master
replicated to a slave?
</p><p>
First, the triggers that exist on a master must be recreated
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
<code class="literal">EMP</code> that has an <code class="literal">AFTER</code>
insert trigger, which exists on a master MySQL server. The
same <code class="literal">EMP</code> table and <code class="literal">AFTER</code>
insert trigger exist on the slave server as well. The
replication flow would be:
</p></li></ul></div><div class="orderedlist"><ol type="1"><li><p>
An <code class="literal">INSERT</code> statement is made to
<code class="literal">EMP</code>.
</p></li><li><p>
The <code class="literal">AFTER</code> trigger on <code class="literal">EMP</code>
activates.
</p></li><li><p>
The <code class="literal">INSERT</code> statement is written to the
binary log.
</p></li><li><p>
The replication slave picks up the <code class="literal">INSERT</code>
statement to <code class="literal">EMP</code> and executes it on the
slave.
</p></li><li><p>
The <code class="literal">AFTER</code> trigger on <code class="literal">EMP</code>
that exists on the slave activates.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="stored-procedure-logging"></a>17.4. Binary Logging of Stored Routines and Triggers</h2></div></div></div><p>
This section describes how MySQL 5.0 handles stored
routines (procedures and functions) with respect to binary
logging. This section also applies to triggers.
</p><p>
The binary log contains information about SQL statements that
modify database contents. This information is stored in the form
of “<span class="quote">events</span>” that describe the modifications.
</p><p>
The binary log has two important purposes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The basis for replication is that 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
<a href="replication.html#replication-implementation" title="6.2. Replication Implementation Overview">Section 6.2, “Replication Implementation Overview”</a>.
</p></li><li><p>
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 <a href="database-administration.html#backup-recovery" title="5.9.2.2. Using Backups for Recovery">Section 5.9.2.2, “Using Backups for Recovery”</a>.
</p></li></ul></div><p>
Stored routine logging differs before and after MySQL 5.0.6.
Before MySQL 5.0.6, statements that create and use stored routines
are not written to the binary log, but statements invoked within
stored routines are logged. Suppose that you issue the following
statements:
</p><pre class="programlisting">CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
</pre><p>
For this example, only the <code class="literal">INSERT</code> statement
will appear in the binary log. The <code class="literal">CREATE
PROCEDURE</code> and <code class="literal">CALL</code> statements will
not appear. The absence of routine-related statements in the
binary log means that stored routines are not replicated
correctly. It also means that for a data recovery operation,
re-executing events in the binary log does not recover stored
routines.
</p><p>
To address these replication and data recovery concerns, binary
logging for stored routines was changed in MySQL 5.0.6. However,
this change also raises new issues, which are presented in the
following discussion for your information.
</p><p>
Except as noted otherwise, these remarks assume that you have
enabled binary logging by starting the server with the
<code class="option">--log-bin</code> option. (If the binary log is not
enabled, replication is not possible, nor is the binary log
available for data recovery.) See <a href="database-administration.html#binary-log" title="5.11.3. The Binary Log">Section 5.11.3, “The Binary Log”</a>.
</p><p>
The characteristics of binary logging for stored routine
statements are described in the following list. Some of the items
indicate problems of which you should be aware, but in some cases,
there are server settings that you can modify or workarounds that
you can use to deal with them.
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">CREATE PROCEDURE</code>, <code class="literal">CREATE
FUNCTION</code>, <code class="literal">ALTER PROCEDURE</code>, and
<code class="literal">ALTER FUNCTION</code> statements are written to
the binary log, as are <code class="literal">CALL</code>, <code class="literal">DROP
PROCEDURE</code>, and <code class="literal">DROP FUNCTION</code>.
</p><p>
However, there is a security implication for replication: To
create a routine, a user must have the <code class="literal">CREATE
ROUTINE</code> privilege, but a user who has this privilege
could write a routine to perform any action on a slave server
because the SQL thread on the slave runs with 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 a procedure as follows:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE PROCEDURE mysp ()</code></strong>
-> <strong class="userinput"><code>BEGIN</code></strong>
-> <strong class="userinput"><code>IF @@server_id=2 THEN DROP DATABASE accounting; END IF;</code></strong>
-> <strong class="userinput"><code>END;</code></strong>
-> <strong class="userinput"><code>//</code></strong>
mysql> <strong class="userinput"><code>delimiter ;</code></strong>
mysql> <strong class="userinput"><code>CALL mysp();</code></strong>
</pre><p>
The <code class="literal">CREATE PROCEDURE</code> and
<code class="literal">CALL</code> statements will be written to the
binary log, so the slave will execute them. Because the slave
SQL thread has full privileges, it will drop the
<code class="literal">accounting</code> database.
</p><p>
To avoid this danger for servers that have binary logging
enabled, MySQL 5.0.6 introduces the requirement that stored
procedure and function creators must have the
<code class="literal">SUPER</code> privilege, in addition to the usual
<code class="literal">CREATE ROUTINE</code> privilege that is required.
Similarly, to use <code class="literal">ALTER PROCEDURE</code> or
<code class="literal">ALTER FUNCTION</code>, you must have the
<code class="literal">SUPER</code> privilege in addition to the
<code class="literal">ALTER ROUTINE</code> privilege. Without the
<code class="literal">SUPER</code> privilege, an error will occur:
</p><pre class="programlisting">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_routine_creators variable)
</pre><p>
You might not want to enforce the requirement on routine
creators that they must have the <code class="literal">SUPER</code>
privilege. For example, all users with the <code class="literal">CREATE
ROUTINE</code> privilege on your system might be
experienced application developers. To disable the requirement
for <code class="literal">SUPER</code>, set the global
<code class="literal">log_bin_trust_routine_creators</code> system
variable to 1. By default, this variable has a value of 0, but
you can change it like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET GLOBAL log_bin_trust_routine_creators = 1;</code></strong>
</pre><p>
You can also enable this variable by using the
<code class="option">--log-bin-trust-routine-creators</code> option when
starting the server.
</p><p>
If binary logging is not enabled,
<code class="literal">log_bin_trust_routine_creators</code> does not
apply and <code class="literal">SUPER</code> is not required for routine
creation.
</p></li><li><p>
A non-deterministic routine that performs updates is not
repeatable, which can have two undesirable effects:
</p><div class="itemizedlist"><ul type="circle"><li><p>
It will make a slave different from the master.
</p></li><li><p>
Restored data will be different from the original data.
</p></li></ul></div><p>
To deal with these problems, MySQL enforces the following
requirement: On a master server, creation and alteration of a
routine is refused unless the routine is declared to be
deterministic or to not modify data. This means that when you
create a routine, you must declare either that it is
deterministic or that it does not change data. Two sets of
routine characteristics apply here:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">DETERMINISTIC</code> and <code class="literal">NOT
DETERMINISTIC</code> indicate whether a routine always
produces the same result for given inputs. The default is
<code class="literal">NOT DETERMINISTIC</code> if neither
characteristic is given, so you must specify
<code class="literal">DETERMINISTIC</code> explicitly to declare
that a routine is deterministic.
</p><p>
Use of the <code class="literal">NOW()</code> function (or its
synonyms) or <code class="literal">RAND()</code> does not
necessarily make a routine non-deterministic. For
<code class="literal">NOW()</code>, the binary log includes the
timestamp and replicates correctly.
<code class="literal">RAND()</code> 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.)
</p></li><li><p>
<code class="literal">CONTAINS SQL</code>, <code class="literal">NO
SQL</code>, <code class="literal">READS SQL DATA</code>, and
<code class="literal">MODIFIES SQL</code> data provide information
about whether the routine reads or writes data. Either
<code class="literal">NO SQL</code> or <code class="literal">READS SQL
DATA</code> indicates that a routine does not change
data, but you must specify one of these explicitly because
the default is <code class="literal">CONTAINS SQL</code> if none of
these characteristics is given.
</p></li></ul></div><p>
By default, for a <code class="literal">CREATE PROCEDURE</code> or
<code class="literal">CREATE FUNCTION</code> statement to be accepted,
<code class="literal">DETERMINISTIC</code> or one of <code class="literal">NO
SQL</code> and <code class="literal">READS SQL DATA</code> must be
explicitly specified. Otherwise an error occurs:
</p><pre class="programlisting">ERROR 1418 (HY000): This routine 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_routine_creators
variable)
</pre><p>
If you set <code class="literal">log_bin_trust_routine_creators</code>
to 1, the requirement that routines be deterministic or not
modify data is dropped.
</p><p>
Note that assessment of the nature of a routine is based on
the “<span class="quote">honesty</span>” of the creator: MySQL does not
check that a routine declared <code class="literal">DETERMINISTIC</code>
contains no statements that produce non-deterministic results.
</p></li><li><p>
A <code class="literal">CALL</code> statement is written to the binary
log if the routine returns no error, but not otherwise. When a
routine that modifies data fails, you get this warning:
</p><pre class="programlisting">ERROR 1417 (HY000): A routine failed and has neither NO SQL nor
READS SQL DATA in its declaration and binary logging is enabled; if
non-transactional tables were updated, the binary log will miss their
changes
</pre><p>
This logging behavior has the potential to cause problems. If
a routine partly modifies a non-transactional table (such as a
<code class="literal">MyISAM</code> table) and returns an error, the
binary log will not reflect these changes. To protect against
this, you should use transactional tables in the routine and
modify the tables within transactions.
</p><p>
If you use the <code class="literal">IGNORE</code> keyword with
<code class="literal">INSERT</code>, <code class="literal">DELETE</code>, or
<code class="literal">UPDATE</code> to ignore errors within a routine, a
partial update might occur but no error will result. Such
statements are logged and replicate normally.
</p></li><li><p>
If a stored function is invoked within a statement such as
<code class="literal">SELECT</code> that does not modify data, execution
of the function will not be written to the binary log, even if
the function itself modifies data. This logging behavior has
the potential to cause problems. Suppose that a function
<code class="literal">myfunc()</code> is defined as follows:
</p><pre class="programlisting">CREATE FUNCTION myfunc () RETURNS INT
BEGIN
INSERT INTO t (i) VALUES(1);
RETURN 0;
END;
</pre><p>
Given that definition, the following statement modifies the
table <code class="literal">t</code> because <code class="literal">myfunc()</code>
modifies <code class="literal">t</code>, but the statement is not
written to the binary log because it is a
<code class="literal">SELECT</code>:
</p><pre class="programlisting">SELECT myfunc();
</pre><p>
A workaround for this problem is to invoke functions that do
updates within statements that do updates. Note that although
the <code class="literal">DO</code> statement sometimes is executed for
the side effect of evaluating an expression,
<code class="literal">DO</code> is not a workaround here because it is
not written to the binary log.
</p></li><li><p>
The statements executed within a routine are not written to
the binary log. Suppose that you issue the following
statements:
</p><pre class="programlisting">CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
</pre><p>
For this example, the <code class="literal">CREATE PROCEDURE</code> and
<code class="literal">CALL</code> statements appear in the binary log,
but the <code class="literal">INSERT</code> statement does not appear.
This corrects the problem that occurred before MySQL 5.0.6 in
which the <code class="literal">CREATE PROCEDURE</code> and
<code class="literal">CALL</code> statements were not logged and the
<code class="literal">INSERT</code> was logged.
</p></li><li><p>
On slave servers, the following limitation is applied when
determining which events from the master replicate:
<code class="option">--replicate-*-table</code> rules don't apply to
<code class="literal">CALL</code> statements or to statements within
routines: The rules in these cases always return “<span class="quote">do
replicate!</span>”
</p></li></ul></div><p>
Triggers are similar to stored functions, so the preceding remarks
also apply to triggers with the following exception:
<code class="literal">CREATE TRIGGER</code> does not have an optional
<code class="literal">DETERMINISTIC</code> characteristic, so triggers are
assumed to be always deterministic. However, this assumption might
in some cases be invalid. For example, the
<code class="literal">UUID()</code> function is non-deterministic (and does
not replicate). You should be careful about using such functions
in triggers.
</p><p>
Triggers currently can't update tables, but they will in the
future. For this reason, error messages similar to those for
stored routines occur with <code class="literal">CREATE TRIGGER</code> if
you do not have the <code class="literal">SUPER</code> privilege and
<code class="literal">log_bin_trust_routine_creators</code> is 0.
</p><p>
The issues described in this section 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
occurs at a more fine-grained level and specifies which changes to
make to individual records as a result of executing SQL
statements.
</p></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="spatial-extensions-in-mysql.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="triggers.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 16. Spatial Extensions in MySQL </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 18. Triggers</td></tr></table></div></body></html>
