I.1. Restrictions on Stored Routines and Triggers
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. In addition, triggers currently are not activated by
foreign key actions.
Stored routines cannot contain arbitrary SQL statements. The
following statements are disallowed:
The locking statements LOCK TABLES,
UNLOCK TABLES.
LOAD DATA and LOAD
TABLE.
SQL prepared statements (PREPARE,
EXECUTE, DEALLOCATE
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
INTO var_list
clause and SHOW statements. A function can
process a result set either with SELECT ... INTO
var_list or by using a
cursor and FETCH statements. See
Section 19.2.7.3, “SELECT ... INTO Statement”.
FLUSH statements.
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
trigger.
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 19.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 SHOW
CREATE statements.
There are no stored routine debugging facilities.
CALL statements cannot be prepared. This true
both for server-side prepared statements and for SQL prepared
statements.
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.
The RETURN statement is disallowed in triggers,
which cannot return a value. To exit a trigger immediately, use
the LEAVE statement.
The RENAME DATABASE statement does not migrate
stored routines to the new schema name. See
Section 13.1.9, “RENAME DATABASE Syntax”.
