CREATE PROCEDURE
and
CREATE FUNCTION
Syntax
1026
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
Parameter types and function return types can be declared to use any valid data type, except that the
COLLATE
attribute cannot be used.
The
routine_body
consists of a valid SQL routine statement. This can be a simple statement
such as
SELECT
or
INSERT
, or a compound statement written using
BEGIN
and
END
. Compound
statements can contain declarations, loops, and other control structure statements. The syntax for
these statements is described in
Section 13.6, “MySQL Compound-Statement Syntax”
.
MySQL permits routines to contain DDL statements, such as
CREATE
and
DROP
. MySQL also permits
stored procedures (but not stored functions) to contain SQL transaction statements such as
COMMIT
.
Stored functions may not contain 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 can be used within a stored procedure but not within a stored
function. This prohibition includes
SELECT
statements that do not have an
INTO var_list
clause
and other statements such as
SHOW
,
EXPLAIN
, and
CHECK TABLE
. For statements that can be
determined at function definition time to return a result set, a
Not allowed to return a result
set from a function
error occurs (
ER_SP_NO_RETSET
[2937]
). For statements that can be
determined only at runtime to return a result set, a
PROCEDURE %s can't return a result set
in the given context
error occurs (
ER_SP_BADSELECT
[2930]
).
Note
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
.
USE
statements within stored routines are not permitted. When a routine is invoked, an implicit
USE
db_name
is performed (and undone when the routine terminates). This causes the routine to have the
given default database while it executes. References to objects in databases other than the routine
default database should be qualified with the appropriate database name.
For additional information about statements that are not permitted in stored routines, see
Section E.1,
“Restrictions on Stored Programs”
.
For information about invoking stored procedures from within programs written in a language that has a
MySQL interface, see
Section 13.2.1, “
CALL
Syntax”
.
MySQL stores the
sql_mode
[495]
system variable setting that is in effect at the time a routine is
created, and always executes the routine with this setting in force, regardless of the server SQL mode
in effect when the routine is invoked.
The switch from the SQL mode of the invoker to that of the routine occurs after evaluation of arguments
and assignment of the resulting values to routine parameters. If you define a routine in strict SQL mode
but invoke it in nonstrict mode, assignment of arguments to routine parameters does not take place in
strict mode. If you require that expressions passed to a routine be assigned in strict SQL mode, you
should invoke the routine with strict mode in effect.
The
COMMENT
characteristic is a MySQL extension, and may be used to describe the stored routine.
This information is displayed by the
SHOW CREATE PROCEDURE
and
SHOW CREATE FUNCTION
statements.
The
LANGUAGE
characteristic indicates the language in which the routine is written. The server ignores
this characteristic; only SQL routines are supported.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...