Limitations on stored programs

This topic applies to all stored routines, namely stored procedures and stored functions. Some limitations are specific to stored functions and not stored procedures. The limitations on stored functions also apply to triggers. Triggers have their own specific limitations.

Limitations on SQL statements

Limitations of stored programs on SQL statements:

• Stored routines cannot use the LOAD DATA statement.

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

• Stored programs cannot use the statements that are not allowed in prepared SQL statements. However, exception handling statements such as SIGNAL, RESIGNAL, and GET DIAGNOSTICS, which cannot be used as prepared statements, can be used in stored programs.

• A local variable takes effect only when the stored program is executed. Therefore, it cannot be referenced by a prepared statement created in the stored program. The prepared statement takes effect only for the current session instead of the stored program. Therefore, this statement can be executed after the program ends. At this time, the local variable is beyond the effective scope.

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

Limitations on stored functions

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

• Statements for performing an explicit or implicit commit or rollback. The SQL standard does not require support for such statements. The standard stipulates that each DBMS vendor can decide whether to restrict them.

• Statements that return a result set, such as the SELECT and SHOW statements without the INTO var_list clause, EXPLAIN statement, and CHECK TABLE statement. Functions can use the SELECT INTO clause or use a cursor and the FETCH statement to process a result set.

• FLUSH statement.

• Stored functions cannot be used in recursions.

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

The preceding statements can be used in stored procedures except those called from stored functions or triggers. For example, if you use the FLUSH statement in a stored procedure, you cannot call this stored procedure from a stored function or trigger.

Limitations on triggers

Take note of the following limitations on triggers:

• A foreign key action cannot fire a trigger.

• A trigger cannot return a value and therefore cannot contain the RETURN statement. To immediately exit a trigger, use the LEAVE statement.

Name conflicts in stored routines

A routine parameter, local variable, and table column can share the same identifier. The same local variable name can also be used in nested blocks. The following rules apply in such scenarios:

• A local variable takes precedence over a routine parameter or a table column.

• A routine parameter takes precedence over a table column.

• A local variable in an internal block takes precedence over a local variable in an external block.