Stored program usage restrictions

This topic applies to all stored routines, that is, stored procedures and stored functions. Some restrictions apply only to stored functions and not to stored procedures. Restrictions on stored functions also apply to triggers, but triggers have their own restrictions.

Restrictions on SQL statements

The following restrictions apply to 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 cannot use SQL prepared statements that are restricted in 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. Therefore, they cannot be referenced by prepared statements created within the stored program. Prepared statements are only valid for the current session and not for the stored program itself. As a result, the statement can be executed after the program has finished, at which point the variable is 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.

Restrictions on stored functions

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

• Statements that perform explicit or implicit commits or rollbacks. SQL standards do not require support for these statements, and each DBMS vendor can 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.

The above restrictions 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.

Restrictions on triggers

Triggers have the following additional restrictions:

• Foreign key operations do not activate triggers.

• Triggers cannot return values, so they cannot contain the 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.