Oracle 5.0, Reference Manual

Page: 1047 / 3232

CREATE PROCEDURE
and
CREATE FUNCTION
Syntax
1027
A routine is considered “deterministic” if it always produces the same result for the same input
parameters, and “not deterministic” otherwise. If neither
DETERMINISTIC
nor
NOT DETERMINISTIC
is given in the routine definition, the default is
NOT DETERMINISTIC
. To declare that a function is
deterministic, you must specify
DETERMINISTIC
explicitly.
Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not
check that a routine declared
DETERMINISTIC
is free of statements that produce nondeterministic
results. However, misdeclaring a routine might affect results or affect performance. Declaring
a nondeterministic routine as
DETERMINISTIC
might lead to unexpected results by causing
the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as
NONDETERMINISTIC
might diminish performance by causing available optimizations not to be used.
Prior to MySQL 5.0.44, the
DETERMINISTIC
characteristic is accepted, but not used by the optimizer.
If binary logging is enabled, the
DETERMINISTIC
characteristic affects which routine definitions
MySQL accepts. See Section 18.6, “Binary Logging of Stored Programs” .
A routine that contains the
NOW()
[926] function (or its synonyms) or
RAND()
[912] is nondeterministic,
but it might still be replication-safe. For
NOW()
[926] , the binary log includes the timestamp and
replicates correctly.
RAND()
[912] also replicates correctly as long as it is called only a single time
during the execution of a routine. (You can consider the routine execution timestamp and random
number seed as implicit inputs that are identical on the master and slave.)
Several characteristics provide information about the nature of data use by the routine. In MySQL,
these characteristics are advisory only. The server does not use them to constrain what kinds of
statements a routine will be permitted to execute.
•
CONTAINS SQL
indicates that the routine does not contain statements that read or write data. This
is the default if none of these characteristics is given explicitly. Examples of such statements are
SET
@x = 1
or
DO RELEASE_LOCK('abc')
, which execute but neither read nor write data.
•
NO SQL
indicates that the routine contains no SQL statements.
•
READS SQL DATA
indicates that the routine contains statements that read data (for example,
SELECT
), but not statements that write data.
•
MODIFIES SQL DATA
indicates that the routine contains statements that may write data (for
example,
INSERT
or
DELETE
).
The
SQL SECURITY
characteristic can be
DEFINER
or
INVOKER
to specify the security context; that is,
whether the routine executes using the privileges of the account named in the routine
DEFINER
clause
or the user who invokes it. This account must have permission to access the database with which
the routine is associated. The default value is
DEFINER
. As of MySQL 5.0.3, the user who invokes
the routine must have the
EXECUTE
[577] privilege for it, as must the
DEFINER
account if the routine
executes in definer security context.
The
DEFINER
clause specifies the MySQL account to be used when checking access privileges
at routine execution time for routines that have the
SQL SECURITY DEFINER
characteristic. The
DEFINER
clause was added in MySQL 5.0.20.
If a
user
value is given for the
DEFINER
clause, it should be a MySQL account specified as
'user_name'@'host_name'
(the same format used in the
GRANT
statement),
CURRENT_USER
[959] ,
or
CURRENT_USER()
[959] . The default
DEFINER
value is the user who executes the
CREATE
PROCEDURE
or
CREATE FUNCTION
or statement. This is the same as specifying
DEFINER =
CURRENT_USER
explicitly.
If you specify the
DEFINER
clause, these rules determine the legal
DEFINER
user values:
• If you do not have the
SUPER
[578] privilege, the only legal
user
value is your own account, either
specified literally or by using
CURRENT_USER
[959] . You cannot set the definer to some other
account.