CREATE PROCEDURE
and
CREATE FUNCTION
Syntax
1024
CREATE
[DEFINER = {
user
| CURRENT_USER }]
FUNCTION
sp_name
([
func_parameter
[,...]])
RETURNS
type
[
characteristic
...]
routine_body
proc_parameter
:
[ IN | OUT | INOUT ]
param_name
type
func_parameter
:
param_name
type
type
:
Any valid MySQL data type
characteristic
:
COMMENT '
string
'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body
:
Valid SQL routine statement
These statements create stored routines. By default, a routine is associated with the default database.
To associate the routine explicitly with a given database, specify the name as
db_name.sp_name
when you create it.
The
CREATE FUNCTION
statement is also used in MySQL to support UDFs (user-defined functions).
See
Section 21.2, “Adding New Functions to MySQL”
. A UDF can be regarded as an external stored
function. Stored functions share their namespace with UDFs. See
Section 9.2.3, “Function Name
Parsing and Resolution”
, for the rules describing how the server interprets references to different kinds
of functions.
To invoke a stored procedure, use the
CALL
statement (see
Section 13.2.1, “
CALL
Syntax”
). To invoke
a stored function, refer to it in an expression. The function returns a value during expression evaluation.
As of MySQL 5.0.3,
CREATE PROCEDURE
and
CREATE FUNCTION
require the
CREATE
ROUTINE
[576]
privilege. They might also require the
SUPER
[578]
privilege, depending on the
DEFINER
value, as described later in this section. If binary logging is enabled,
CREATE FUNCTION
might require the
SUPER
[578]
privilege, as described in
Section 18.6, “Binary Logging of Stored
Programs”
.
By default, MySQL automatically grants the
ALTER ROUTINE
[576]
and
EXECUTE
[577]
privileges to the routine creator. This behavior can be changed by disabling the
automatic_sp_privileges
[437]
system variable. See
Section 18.2.2, “Stored Routines and
MySQL Privileges”
.
The
DEFINER
and
SQL SECURITY
clauses specify the security context to be used when checking
access privileges at routine execution time, as described later in this section.
If the routine name is the same as the name of a built-in SQL function, a syntax error occurs unless you
use a space between the name and the following parenthesis when defining the routine or invoking it
later. For this reason, avoid using the names of existing SQL functions for your own stored routines.
The
IGNORE_SPACE
[536]
SQL mode applies to built-in functions, not to stored routines. It is always
permissible to have spaces after a stored routine name, regardless of whether
IGNORE_SPACE
[536]
is
enabled.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an
empty parameter list of
()
should be used. Parameter names are not case sensitive.
Each parameter is an
IN
parameter by default. To specify otherwise for a parameter, use the keyword
OUT
or
INOUT
before the parameter name.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
