Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of these restrictions apply to stored functions but not to stored procedures.
The restrictions for stored functions also apply to triggers.
Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted:
ALTER VIEW. (Before MySQL 5.0.46, this restriction is enforced only for stored functions.)
SQL prepared statements (
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.
Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Section 13.5, “SQL Syntax for Prepared Statements”.
Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example,
SELECT ... INTOcannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters. See Section 13.5.1, “PREPARE Syntax”.
Within stored programs (stored procedures and functions, and triggers), the parser treats
BEGIN [WORK]as the beginning of a
BEGIN ... ENDblock. Begin a transaction in this context with
For stored functions (but not stored procedures), the following additional statements or operations are not permitted:
Statements that perform 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 permit them.
Statements that return a result set. This includes
SELECTstatements that do not have an
INTOclause and other statements such as
CHECK TABLE. A function can process a result set either with
SELECT ... INTOor by using a cursor and
FETCHstatements. See Section 126.96.36.199, “SELECT ... INTO Syntax”, and Section 13.6.6, “Cursors”.
Before MySQL 5.0.10, stored functions created with
CREATE FUNCTIONmust not contain references to tables, with limited exceptions. They may include some
SETstatements that contain table references, for example
SET a:= (SELECT MAX(id) FROM t), and
SELECTstatements that fetch values directly into variables, for example
SELECT i INTO var1 FROM t.
Stored functions cannot be used recursively.
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
If you refer to a temporary table multiple times in a stored function under different aliases, a
Can't reopen table: 'error occurs, even if the references occur in different statements within the function.
A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. Statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel. In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log. See Section 18.6, “Binary Logging of Stored Programs”.
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, if you use
FLUSH in a stored procedure, that
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 variables take precedence over table columns is nonstandard.
Use of stored routines can cause replication problems. This issue is discussed further in Section 18.6, “Binary Logging of Stored Programs”.
INFORMATION_SCHEMA does not have a
PARAMETERS table until MySQL 5.5, so
applications that need to acquire routine parameter information at
runtime must use workarounds such as parsing the output of
SHOW CREATE statements or the
param_list column of the
contents can be processed from within a stored routine, unlike the
option applies to tables, views, and triggers. It does not apply
to stored procedures and functions, or events. To filter
statements operating on the latter objects, use one or more of the
There are no stored routine debugging facilities.
Before MySQL 5.0.17,
statements cannot be prepared. This true both for server-side
prepared statements and for SQL prepared statements.
MySQL does not support
MySQL does not support
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 restrictions apply:
Triggers are not activated by foreign key actions.
Triggers are not permitted on tables in the
The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger uses a table and the table has changed since the trigger was loaded into the cache, the trigger operates using the outdated metadata.