Table of Contents
The discussion here describes restrictions that apply to the use of MySQL features such as subqueries or views.
Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.
All of the restrictions for stored functions also apply to triggers.
Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:
The table-maintenance statements
Note: This restriction is
lifted beginning with MySQL 5.0.17.
The locking statements
LOAD DATA and
SQL prepared statements (
PREPARE). Implication: You cannot use dynamic SQL
within stored routines (where you construct dynamically
statements as strings and then execute them). This restriction
is lifted as of MySQL 5.0.13 for stored procedures; it still
applies to stored functions and triggers.
For stored functions (but not stored procedures), the following additional statements or operations are disallowed:
Statements that do explicit or implicit commit or rollback.
Statements that return a result set. This includes
SELECT statements that do not have an
SHOW statements. A function can
process a result set either with
SELECT ... INTO
or by using a
FETCH statements. See
Section 126.96.36.199, “
SELECT ... INTO Statement”.
Note: Before MySQL 5.0.10,
stored functions created with
FUNCTION must not contain references to tables, with
limited exceptions. They may include some
SET statements that contain table
references, for example
SET a:= (SELECT MAX(id) FROM
SELECT statements that
fetch values directly into variables, for example
SELECT i INTO var1 FROM t.
Recursive statements. That is, stored functions cannot be used recursively.
Within a stored function or trigger, it is not allowable to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
Note that although some restrictions normally apply to stored
functions and triggers but not to stored procedures, those
restrictions do apply to stored procedures if they are invoked
from within a stored function or trigger. For example, although
you can use
FLUSH in a stored procedure, such a
stored procedure cannot be called from a stored function or
It is possible for the same identifier to be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:
CREATE PROCEDURE p (i INT) BEGIN DECLARE i INT DEFAULT 0; SELECT i FROM t; BEGIN DECLARE i INT DEFAULT 1; SELECT i FROM t; END; END;
In such cases the identifier is ambiguous and the following precedence rules apply:
A local variable takes precedence over a routine parameter or table column
A routine parameter takes precedence over a table column
A local variable in an inner block takes precedence over a local variable in an outer block
The behavior that table columns do not take precedence over variables is non-standard.
Use of stored routines can cause replication problems. This issue is discussed further in Section 17.4, “Binary Logging of Stored Routines and Triggers”.
INFORMATION_SCHEMA does not yet have a
PARAMETERS table, so applications that need to
acquire routine parameter information at runtime must use
workarounds such as parsing the output of
There are no stored routine debugging facilities.
CALL statements cannot be prepared. This true
both for server-side prepared statements and for SQL prepared
UNDO handlers are not supported.
FOR loops are not supported.
To prevent problems of interaction between server threads, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. This means that while the statement executes, it will not see changes to routines performed by other threads.
For triggers, the following additional statements or operations are disallowed:
Triggers currently are not activated by foreign key actions.
RETURN statement is disallowed in
triggers, which cannot return a value. To exit a trigger
immediately, use the
Triggers are not allowed on tables in the