Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of these restrictions apply to stored functions but not to stored procedures.
The restrictions for stored functions also apply to triggers. There are also some restrictions specific to triggers.
The restrictions for stored procedures also apply to the
DO
clause of Event Scheduler event
definitions. There are also some restrictions specific to events.
Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:
The locking statements LOCK
TABLES
and
UNLOCK
TABLES
.
LOAD DATA
and LOAD
TABLE
.
SQL prepared statements
(PREPARE
,
EXECUTE
,
DEALLOCATE PREPARE
) can be used
in stored procedures, but not stored functions or triggers.
Implication: You cannot use dynamic SQL within stored
functions or triggers (where you construct dynamically
statements as strings and then execute them).
In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. See Section 12.6, “SQL Syntax for Prepared Statements”, for a list of statements supported as prepared statements. Statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines unless noted otherwise in Section 18.2, “Using Stored Routines (Procedures and Functions)”.
Inserts cannot be delayed. INSERT
DELAYED
syntax is accepted but the statement is
handled as a normal INSERT
.
Within all stored programs (stored procedures and functions,
triggers, and events), the parser treats
BEGIN [WORK]
as the beginning of a
BEGIN ...
END
block. Begin a transaction in this context with
START
TRANSACTION
instead.
For stored functions (but not stored procedures), the following additional statements or operations are disallowed:
Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to allow them.
Statements that return a result set. This includes
SELECT
statements that do not
have an INTO
clause and other
statements such as var_list
SHOW
,
EXPLAIN
, and
CHECK TABLE
. A function can
process a result set either with SELECT ... INTO
or by using a
cursor and var_list
FETCH
statements.
See Section 12.7.3.3, “SELECT ... INTO
Statement”.
FLUSH
statements.
Stored functions cannot be used recursively.
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
If you refer to a temporary table multiple times in a stored
function under different aliases, a Can't reopen
table:
'
error occurs, even if the references occur in different
statements within the function.
tbl_name
'
A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. When statement-based binary logging is used, statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel. In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log even for statement-based binary logging. See Section 18.6, “Binary Logging of Stored Programs”.
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, if you use
FLUSH
in a stored procedure, that
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 variables take precedence over table columns is nonstandard.
Use of stored routines can cause replication problems. This issue is discussed further in Section 18.6, “Binary Logging of Stored Programs”.
The
--replicate-wild-do-table=
option applies to tables, views, and triggers. It does not apply
to stored functions and procedures, or events. To filter
statements operating on the latter objects, use one or more of the
db_name.tbl_name
--replicate-*-db
options.
There are no stored routine debugging facilities.
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.
For triggers, the following additional statements or operations are disallowed:
Triggers currently are not activated by foreign key actions.
When using row-based replication, triggers on the slave are not activated by statements originating on the master. This does not apply when using statement-based replication. For more information, see Section 16.4.1.29, “Replication and Triggers”.
The RETURN
statement is
disallowed in triggers, which cannot return a value. To exit a
trigger immediately, use the
LEAVE
statement.
Triggers are not allowed on tables in the
mysql
database.
The following limitations are specific to the Event Scheduler:
Event names are handled in case-insensitive fashion. For
example, this means that you cannot have two events in the
same database with the names anEvent
and
AnEvent
.
An event may not be created, altered, or dropped by a stored routine, trigger, or another event. An event also may not create, alter, or drop stored routines or triggers. (Bug#16409, Bug#18896)
Event timings using the intervals
YEAR
,
QUARTER
, MONTH
, and
YEAR_MONTH
are resolved in months; those
using any other interval are resolved in seconds. There is no
way to cause events scheduled to occur at the same second to
execute in a given order. In addition — due to rounding,
the nature of threaded applications, and the fact that a
nonzero length of time is required to create events and to
signal their execution — events may be delayed by as
much as 1 or 2 seconds. However, the time shown in the
INFORMATION_SCHEMA.EVENTS
table's
LAST_EXECUTED
column or the
mysql.event
table's
last_executed
column is always accurate to
within one second of the actual event execution time. (See
also Bug#16522.)
Each execution of the statements contained in the body of an
event takes place in a new connection; thus, these statements
has no effect in a given user session on the server's
statement counts such as Com_select
and
Com_insert
that are displayed by
SHOW STATUS
. However, such
counts are updated in the global scope.
(Bug#16422)
Events do not support times later than the end of the Unix Epoch; this is approximately the beginning of the year 2038. Such dates are specifically disallowed by the Event Scheduler. (Bug#16396)
References to stored functions, user-defined functions, and
tables in the ON SCHEDULE
clauses of
CREATE EVENT
and
ALTER EVENT
statements are not
supported. These sorts of references are disallowed. (See
Bug#22830 for more information.)
Generally speaking, statements which are not permitted in stored routines or in SQL prepared statements are also not allowed in the body of an event. For more information, see Section 12.6, “SQL Syntax for Prepared Statements”.
User Comments
Add your own comment.