Limitations on stored programs

The limitations described in this topic apply to all stored routines, that is, stored procedures and stored functions. Some limitations apply only to stored functions and not to stored procedures. The limitations of stored functions also apply to triggers, but triggers have their own limitations.

Limitations on the use of SQL statements

The following limitations apply to the use of SQL statements in stored programs:

• Stored routines cannot use the LOAD DATA statement.

• SQL prepared statements (PREPARE, EXECUTE, and DEALLOCATE PREPARE) can be used in stored procedures but not in stored functions or triggers. Therefore, stored functions and triggers cannot use dynamic SQL.

• Stored programs also cannot use SQL prepared statements that are restricted in the use of prepared statements. However, the exception handling statements SIGNAL, RESIGNAL, and GET DIAGNOSTICS are allowed in stored programs, even though they cannot be used as prepared statements.

• Local variables are only effective during the execution of the stored program, so they cannot be referenced by prepared statements created within the stored program. Prepared statements are only valid for the current session, not the stored program they are in, so they can be executed after the program has finished, at which point the variables are no longer in scope.

• In all stored programs (stored procedures and functions, and triggers), the parser treats BEGIN[WORK] as a BEGIN...END block. To start a transaction in this context, use START TRANSACTION.

Limitations on stored functions

The following additional statements and operations are not allowed in stored functions:

• Statements that perform an explicit or implicit commit or rollback. SQL standards do not require support for these statements, and the standard allows each DBMS vendor to decide whether to restrict them.

• Statements that return result sets, such as SELECT statements without an INTO var_list clause, and SHOW, EXPLAIN, and CHECK TABLE. Functions can use the SELECT INTO clause or use cursors and FETCH statements to handle result sets.

• The FLUSH statement.

• Stored functions cannot be used recursively.

• If a table exists in the calling statement of the function or trigger (used for reading or writing), the stored function or trigger cannot modify the table.

These limitations apply to statements in stored procedures, but not to stored procedures called from stored functions or triggers. For example, if a stored procedure uses the FLUSH statement, it cannot be called from a stored function or trigger.

Limitations on triggers

Triggers have the following additional limitations:

• Foreign key operations do not activate triggers.

• Triggers cannot return values, so they cannot contain a RETURN statement. To exit a trigger immediately, use the LEAVE statement.

Name conflicts in stored routines

Routine parameters, local variables, and table column names can use the same identifier, and the same local variable name can be used in nested blocks. In cases of ambiguous identifiers, the following priority rules apply:

• Local variables generally take precedence over routine parameters or table column names.

• Routine parameters take precedence over table column names.

• Local variables in an inner block take precedence over local variables in an outer block.