D.1 Restrictions on Stored Programs

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:

For stored functions (but not stored procedures), the following additional statements or operations are not permitted:

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 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:

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 mysql.proc table. param_list contents can be processed from within a stored routine, unlike the output from SHOW.

The --replicate-wild-do-table=db_name.tbl_name 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 --replicate-*-db options.

There are no stored routine debugging facilities.

Before MySQL 5.0.17, CALL statements cannot be prepared. This true both for server-side prepared statements and for SQL prepared statements.

MySQL does not support UNDO handlers.

MySQL does not support FOR loops.

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: