These limitations apply to all stored routines, namely stored procedures and stored functions. Some limitations are specific to stored functions rather than stored procedures. The limitations on stored functions also apply to triggers, but triggers have their own specific limitations as well.
Limitations on SQL statements
Stored programs have the following limitations on SQL statements:
The storage routine does not support the
LOAD DATAstatement.SQL prepared statements (
PREPARE,EXECUTE, andDEALLOCATE 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 statements that are restricted in SQL prepared statements. However, exception handling statements
SIGNAL,RESIGNAL, andGET DIAGNOSTICSare exceptions. Although they are not allowed as prepared statements, they are permitted in stored programs.Local variables are only valid during the execution of a stored program and cannot be referenced by prepared statements created within that stored program. Prepared statements are valid for the current session, not for the stored program they are in, so they can be executed after the program ends, by which time the variables are no longer in scope.
In all stored programs (stored procedures and functions, triggers), the parser treats
BEGIN[WORK]as aBEGIN...ENDblock. To start a transaction in this context, useSTART TRANSACTION.
Limitations on stored functions
The following additional statements or operations are not allowed in a stored function:
Statements for explicit or implicit commit or rollback. The SQL standard does not require support for these statements; it allows each DBMS vendor to decide whether to restrict them.
Statements that return result sets, such as
SELECTstatements without theINTO var_listclause, andSHOW,EXPLAIN, andCHECK TABLEstatements. Functions can use theSELECT INTOclause or cursors andFETCHstatements to process result sets.The
FLUSHstatement.Stored functions cannot be used recursively.
If a table is referenced in the call statement (for reading or writing) of a function or trigger, the stored function or trigger cannot modify that table.
The preceding statements can be used in stored procedures, except for those called from stored functions or triggers. For example, if you use the FLUSH statement in a stored procedure, you cannot call that stored procedure from a stored function or trigger.
Trigger limitations
Triggers have the following additional limitations:
Trigger activation is not triggered by foreign key operations.
Triggers cannot return values, so the
RETURNstatement is not allowed. To immediately exit a trigger, use theLEAVEstatement.
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 also be used in nested blocks. In cases where the identifier is ambiguous, the following precedence rules apply:
Local variables generally take precedence over routine parameters or table column names.
Procedure parameters take precedence over table column names.
Local variables in an inner block take precedence over those in an outer block.