(mysql.info) routine-restrictions

 
 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 table-maintenance statements `CHECK TABLES' and `OPTIMIZE
      TABLES'.  * This restriction is lifted beginning with MySQL
      5.0.17.
 
    * 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  select-into-statement.
 
    * `FLUSH' statements.
 
    * * Before MySQL 5.0.10, stored functions created with `CREATE
      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 t)',
      and `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 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  stored-procedure-logging.
 
 `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.
Info Catalog
(mysql.info) restrictions
(mysql.info) cursor-restrictions
automatically generated byinfo2html