CREATE PROCEDURE和CREATE FUNCTION语句
CREATE [DEFINER = user]PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bodyCREATE [DEFINER = 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 MySQLdata type characteristic:COMMENT 'string' |LANGUAGE SQL | [NOT]DETERMINISTIC | {CONTAINS SQL |NO SQL |READS SQL DATA |MODIFIES SQL DATA } |SQL SECURITY {DEFINER |INVOKER } routine_body: ValidSQL routine statement
这些语句创建存储的例程。默认情况下,例程与默认数据库关联。要将例程与给定数据库明确关联,请在创建例程db_name.sp_name
时指定名称。
该CREATE FUNCTION
语句在MySQL中也用于支持UDF(用户定义的函数)。请参见“MySQL服务器支持用户自定义函数”。UDF可以视为外部存储功能。存储的函数与UDF共享其名称空间。有关描述服务器如何解释对不同类型功能的引用的规则,请参见“函数名称解析和解析”。
要调用存储过程,请使用CALL
语句(请参见“ CALL语句”)。要调用存储的函数,请在表达式中引用它。该函数在表达式求值期间返回一个值。
CREATE PROCEDURE
并CREATE FUNCTION
要求CREATE ROUTINE
特权。如果DEFINER
存在该子句,则所需的特权取决于该user
值,如“存储的对象访问控制”中所述。如果启用了二进制日志记录,则CREATE FUNCTION
可能需要SUPER
特权,如“存储的程序二进制日志记录”中所述。
默认情况下,MySQL自动将ALTER ROUTINE
和EXECUTE
特权授予例程创建者。可以通过禁用automatic_sp_privileges
系统变量来更改此行为。请参见“存储的例程和MySQL特权”。
DEFINER
和SQL SECURITY
子句指定的安全上下文,在程序执行时检查访问权限的情况下,如在本节后面所述使用。
如果例程名称与内置SQL函数的名称相同,则除非在定义例程或以后调用它时在名称和以下括号之间使用空格,否则会发生语法错误。因此,请避免将现有SQL函数的名称用于您自己的存储例程。
在IGNORE_SPACE
SQL模式适用于内置的功能,而不是保存的程序。无论是否IGNORE_SPACE
启用,始终允许在存储的例程名称后留空格。
括号内的参数列表必须始终存在。如果没有参数,()
则应使用的空参数列表。参数名称不区分大小写。
IN
默认情况下,每个参数都是一个参数。要为参数另外指定,请使用关键字OUT
或INOUT
在参数名称之前。
注意将参数指定为
IN
,OUT
或INOUT
仅对有效PROCEDURE
。对于FUNCTION
,参数始终被视为IN
参数。
一个IN
参数传送一个值的过程。该过程可能会修改该值,但是该过程返回时,调用者看不到该修改。一个OUT
参数传送从过程返回给调用者的值。它的初始值NULL
在过程中,并且过程返回时,调用者可以看到其初始值。一个INOUT
参数是由呼叫者初始化,可以由程序进行修改,并且由所述方法制备的任何变化是可见的呼叫者时,过程返回。
对于每一个OUT
或INOUT
参数,在传递一个用户定义的变量CALL
,调用的程序,这样就可以得到在其值在过程返回语句。如果要从另一个存储过程或函数中调用过程,则还可以将例程参数或本地例程变量作为OUT
or INOUT
参数传递。如果要从触发器内调用过程,则还可以将其作为 or 参数传递。NEW.col_name
OUT
INOUT
有关未处理条件对过程参数的影响的信息,请参见“条件处理和OUT或INOUT参数”。
例程参数不能在例程内准备的语句中引用;请参见“对存储程序的限制”。
以下示例显示了一个使用OUT
参数的简单存储过程:
mysql>delimiter // mysql>CREATE PROCEDURE simpleproc (OUT param1 INT) ->BEGIN ->SELECT COUNT(*)INTO param1FROM t; ->END // Query OK, 0 rows affected (0.00 sec) mysql>delimiter ; mysql>CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql>SELECT @a; +------ + | @a | +------ + | 3 | +------ + 1 row in set (0.00 sec)
该示例使用MySQL的客户机delimiter
命令语句从定界符改变;
到//
当正在定义的过程。这使;
过程主体中使用的定界符可以传递到服务器,而不是由mysql本身解释。请参见“定义存储程序”。
RETURNS
只能为FUNCTION
强制指定该子句。它指示函数的返回类型,并且函数主体必须包含一条语句。如果该语句返回其他类型的值,则该值将被强制为正确的类型。例如,如果函数在子句中指定 or 值,但该语句返回整数,则从函数返回的值是成员集的对应成员的字符串。RETURN value
RETURN
ENUM
SET
RETURNS
RETURN
ENUM
SET
下面的示例函数使用参数,使用SQL函数执行操作,然后返回结果。在这种情况下,delimiter
由于函数定义不包含内部;
语句定界符,因此无需使用:
mysql>CREATE FUNCTION hello (s CHAR(20)) mysql>RETURNS CHAR(50)DETERMINISTIC ->RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) mysql>SELECT hello('world'); +---------------- + | hello('world') | +---------------- + | Hello, world! | +---------------- + 1 row in set (0.00 sec)
可以声明参数类型和函数返回类型以使用任何有效的数据类型。COLLATE
如果在CHARACTER SET
规格之前加上该属性,则可以使用该属性。
该routine_body
由一个有效的SQL例程语句。这可以是一个简单的语句(例如SELECT
或)INSERT
,也可以是使用BEGIN
和编写的复合语句END
。复合语句可以包含声明,循环和其他控制结构语句。“复合语句语法”中介绍了这些语句的语法。实际上,除非主体由单个RETURN
语句组成,否则存储函数倾向于使用复合语句。
MySQL允许例程包含DDL语句,例如CREATE
和DROP
。MySQL还允许存储过程(但不允许存储函数)包含SQL事务语句,例如COMMIT
。存储的函数可能不包含执行显式或隐式提交或回滚的语句。SQL标准不需要支持这些语句,该标准指出每个DBMS供应商都可以决定是否允许它们。
返回结果集的语句可以在存储过程中使用,但不能在存储函数中使用。这项禁令包括SELECT
不具有的声明条款等语句,如,和。对于可以在函数定义时确定以返回结果集的语句,将发生错误()。对于只能在运行时确定以返回结果集的语句,将发生错误()。INTO var_list
SHOW
EXPLAIN
CHECK TABLE
Not allowed to return a result set from a function
ER_SP_NO_RETSET
PROCEDURE %s can't return a result set in the given context
ER_SP_BADSELECT
USE
不允许在存储的例程中使用语句。调用例程时,将执行隐式操作(在例程终止时撤消操作)。使例程在执行时具有给定的默认数据库。对例程默认数据库以外的数据库中对象的引用应使用适当的数据库名称进行限定。USE db_name
有关存储例程中不允许的语句的更多信息,请参见“对存储程序的限制”。
有关从以具有MySQL接口的语言编写的程序中调用存储过程的信息,请参见“ CALL语句”。
MySQL存储在sql_mode
创建或更改例程时有效的系统变量设置,并且始终在执行该设置的情况下执行该例程,而与例程开始执行时当前的服务器SQL模式无关。
在对参数进行评估并将结果值分配给例程参数之后,将发生从调用者的SQL模式到例程模式的切换。如果以严格SQL模式定义例程但以非严格模式调用例程,则在严格模式下不会将参数分配给例程参数。如果需要以严格的SQL模式分配传递给例程的表达式,则应以有效的严格模式调用该例程。
该COMMENT
特性是MySQL扩展,可用于描述存储的例程。该信息由SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
语句显示。
该LANGUAGE
特征指示例程所用的语言。服务器忽略此特征;仅支持SQL例程。
例程被认为是“确定性的”,如果它总是产生相同的结果对于相同的输入参数,并且“不确定性”否则。如果例程定义中也DETERMINISTIC
没有NOT DETERMINISTIC
给出,则默认为NOT DETERMINISTIC
。要声明一个函数是确定性的,必须DETERMINISTIC
明确指定。
例程性质的评估基于创建者的“诚实”:MySQL不检查所声明的例程DETERMINISTIC
是否没有产生不确定性结果的语句。但是,错误声明例程可能会影响结果或影响性能。DETERMINISTIC
通过使优化器做出错误的执行计划选择,将不确定的例程声明为可能会导致意外结果。将确定性例程声明为NONDETERMINISTIC
可能会导致不使用可用的优化,从而降低性能。
如果启用了二进制日志记录,则该DETERMINISTIC
特性会影响MySQL接受哪些例程定义。请参见“存储程序二进制记录”。
包含NOW()
函数(或其同义词)或RAND()
不确定性的例程,但是它可能仍然是复制安全的。对于NOW()
,二进制日志包含时间戳记并可以正确复制。RAND()
只要在例程执行期间仅一次调用它,它也可以正确复制。(您可以将例程执行时间戳和随机数种子视为隐式输入,它们在主服务器和从属服务器上是相同的。)
几个特性提供了有关例程使用数据的性质的信息。在MySQL中,这些特征仅是建议性的。服务器不使用它们来约束例程将允许执行哪种类型的语句。
CONTAINS SQL
指示例程不包含读取或写入数据的语句。如果未明确给出这些特征,则为默认设置。此类语句的示例为SET @x = 1
或DO RELEASE_LOCK('abc')
,它们执行但既不读取也不写入数据。NO SQL
指示例程不包含SQL语句。READS SQL DATA
表示该例程包含读取数据的语句(例如SELECT
),但不包含写入数据的语句。MODIFIES SQL DATA
表示例程包含可能写入数据的语句(例如INSERT
或DELETE
)。
该SQL SECURITY
特征可以是DEFINER
或INVOKER
指定安全上下文;也就是说,例程是否使用例程DEFINER
子句中命名的帐户或调用该例程的用户的特权执行。该帐户必须有权访问与该例程相关联的数据库。默认值为DEFINER
。调用例程的用户必须具有EXECUTE
特权,DEFINER
如果例程在定义者安全上下文中执行,则帐户也必须具有特权。
将DEFINER
在程序执行时检查访问权限时是有程序要使用子句指定MySQL账户SQL SECURITY DEFINER
特征。
如果DEFINER
子句,该user
值应被指定为一个MySQL帐户,或。允许的值取决于您拥有的特权,如“存储的对象访问控制”中所述。另请参阅该部分以获取有关存储的例程安全性的其他信息。'user_name'@'host_name'
CURRENT_USER
CURRENT_USER()
user
如果DEFINER
省略该子句,则默认定义器是执行CREATE PROCEDURE
or CREATE FUNCTION
语句的用户。这与DEFINER = CURRENT_USER
显式指定相同。
在用SQL SECURITY DEFINER
特性定义的存储例程的主体内,该CURRENT_USER
函数返回该例程的DEFINER
值。有关在存储的例程中进行用户审核的信息,请参见“基于SQL的帐户活动审核”。
考虑以下过程,该过程显示mysql.user
系统表中列出的MySQL帐户数:
CREATE DEFINER = 'admin'@'localhost'PROCEDURE account_count()BEGIN SELECT 'Number of accounts:', COUNT(*)FROM mysql.user ;END ;
不管哪个用户定义该程序,都会为其分配一个DEFINER
帐户'admin'@'localhost'
。无论哪个用户调用该帐户,它都将以该帐户的特权执行(因为默认的安全特性是DEFINER
)。该过程是成功还是失败,取决于调用者是否EXECUTE
对其'admin'@'localhost'
具有SELECT
特权以及对mysql.user
表具有特权。
现在,假设该过程具有以下SQL SECURITY INVOKER
特征:
CREATE DEFINER = 'admin'@'localhost'PROCEDURE account_count()SQL SECURITY INVOKER BEGIN SELECT 'Number of accounts:', COUNT(*)FROM mysql.user ;END ;
该过程仍然具有DEFINER
of 'admin'@'localhost'
,但是在这种情况下,它以调用用户的特权执行。因此,该过程是成功还是失败取决于调用者是否具有EXECUTE
它的SELECT
特权以及mysql.user
表的特权。
服务器处理例程参数的数据类型,使用DECLARE
或函数返回值创建的局部例程变量,如下所示:
- 检查分配是否存在数据类型不匹配和溢出。转换和溢出问题会导致警告或严格SQL模式下的错误。
- 只能分配标量值。例如,诸如这样的语句
SET x =(SELECT 1, 2)
无效。 对于字符数据类型,如果
CHARACTER SET
声明中包含,则使用指定的字符集及其默认排序规则。如果COLLATE
还存在该属性,则使用该排序规则而不是默认排序规则。如果
CHARACTER SET
和COLLATE
不存在,则使用在常规创建时有效的数据库字符集和排序规则。为避免服务器使用数据库字符集和排序规则,请为字符数据参数提供显式属性CHARACTER SET
和COLLATE
属性。如果更改数据库默认字符集或排序规则,则必须删除并重新创建使用数据库默认值的存储例程,以便它们使用新的默认值。
数据库字符集和排序规则由
character_set_database
和collation_database
系统变量的值给出。有关更多信息,请参见“数据库字符集和校验规则”。