Oracle 5.0, Reference Manual

Page: 3002 / 3232

Restrictions on Stored Programs
2982
• Within stored programs (stored procedures and functions, and triggers), 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
not permitted:
• 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 permit
them.
• Statements that return a result set. This includes
SELECT
statements that do not have an
INTO
var_list
clause and other statements such as
SHOW
,
EXPLAIN
, and
CHECK TABLE
. A function
can process a result set either with
SELECT ... INTO var_list
or by using a cursor and
FETCH
statements. See Section 13.2.8.1, “
SELECT ... INTO
Syntax” , and Section 13.6.6, “Cursors” .
•
FLUSH
statements.
• Before MySQL 5.0.10, stored functions created with
CREATE FUNCTION
must not contain
references to tables, with limited exceptions. They may include some
SET
statements that contain
table references, for example
SET a:= (SELECT MAX(id) FROM t)
, and
SELECT
statements
that fetch values directly into variables, for example
SELECT i INTO var1 FROM t
.
• 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: 'tbl_name'
error occurs, even if the references occur in different statements
within the function.
• 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. 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. 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