• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • CREATE PROCEDURE和CREATE FUNCTION语句

    CREATE
        [DEFINER = user]
        PROCEDURE sp_name ([proc_parameter[,...]])
        [characteristic ...] routine_body
    
    CREATE
        [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 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
    

    这些语句创建存储的例程。默认情况下,例程与默认数据库关联。要将例程与给定数据库明确关联,请在创建例程db_name.sp_name时指定名称。

    CREATE FUNCTION语句在MySQL中也用于支持UDF(用户定义的函数)。请参见“MySQL服务器支持用户自定义函数”。UDF可以视为外部存储功能。存储的函数与UDF共享其名称空间。有关描述服务器如何解释对不同类型功能的引用的规则,请参见“函数名称解析和解析”。

    要调用存储过程,请使用CALL语句(请参见“ CALL语句”)。要调用存储的函数,请在表达式中引用它。该函数在表达式求值期间返回一个值。

    CREATE PROCEDURECREATE FUNCTION要求CREATE ROUTINE特权。如果DEFINER存在该子句,则所需的特权取决于该user值,如“存储的对象访问控制”中所述。如果启用了二进制日志记录,则CREATE FUNCTION可能需要SUPER特权,如“存储的程序二进制日志记录”中所述。

    默认情况下,MySQL自动将ALTER ROUTINEEXECUTE特权授予例程创建者。可以通过禁用automatic_sp_privileges系统变量来更改此行为。请参见“存储的例程和MySQL特权”。

    DEFINERSQL SECURITY子句指定的安全上下文,在程序执行时检查访问权限的情况下,如在本节后面所述使用。

    如果例程名称与内置SQL函数的名称相同,则除非在定义例程或以后调用它时在名称和以下括号之间使用空格,否则会发生语法错误。因此,请避免将现有SQL函数的名称用于您自己的存储例程。

    IGNORE_SPACESQL模式适用于内置的功能,而不是保存的程序。无论是否IGNORE_SPACE启用,始终允许在存储的例程名称后留空格。

    括号内的参数列表必须始终存在。如果没有参数,()则应使用的空参数列表。参数名称不区分大小写。

    IN默认情况下,每个参数都是一个参数。要为参数另外指定,请使用关键字OUTINOUT在参数名称之前。

    注意

    将参数指定为INOUTINOUT仅对有效PROCEDURE。对于FUNCTION,参数始终被视为IN参数。

    一个IN参数传送一个值的过程。该过程可能会修改该值,但是该过程返回时,调用者看不到该修改。一个OUT参数传送从过程返回给调用者的值。它的初始值NULL在过程中,并且过程返回时,调用者可以看到其初始值。一个INOUT参数是由呼叫者初始化,可以由程序进行修改,并且由所述方法制备的任何变化是可见的呼叫者时,过程返回。

    对于每一个OUTINOUT参数,在传递一个用户定义的变量CALL,调用的程序,这样就可以得到在其值在过程返回语句。如果要从另一个存储过程或函数中调用过程,则还可以将例程参数或本地例程变量作为OUT or INOUT参数传递。如果要从触发器内调用过程,则还可以将其作为 or 参数传递。NEW.col_nameOUTINOUT

    有关未处理条件对过程参数的影响的信息,请参见“条件处理和OUT或INOUT参数”。

    例程参数不能在例程内准备的语句中引用;请参见“对存储程序的限制”。

    以下示例显示了一个使用OUT参数的简单存储过程:

    mysql> delimiter //
    
    mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM 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 valueRETURNENUMSETRETURNSRETURNENUMSET

    下面的示例函数使用参数,使用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语句,例如CREATEDROP。MySQL还允许存储过程(但不允许存储函数)包含SQL事务语句,例如COMMIT。存储的函数可能不包含执行显式或隐式提交或回滚的语句。SQL标准不需要支持这些语句,该标准指出每个DBMS供应商都可以决定是否允许它们。

    返回结果集的语句可以在存储过程中使用,但不能在存储函数中使用。这项禁令包括SELECT不具有的声明条款等语句,如,和。对于可以在函数定义时确定以返回结果集的语句,将发生错误()。对于只能在运行时确定以返回结果集的语句,将发生错误()。INTO var_listSHOWEXPLAINCHECK TABLENot allowed to return a result set from a functionER_SP_NO_RETSETPROCEDURE %s can't return a result set in the given contextER_SP_BADSELECT

    USE不允许在存储的例程中使用语句。调用例程时,将执行隐式操作(在例程终止时撤消操作)。使例程在执行时具有给定的默认数据库。对例程默认数据库以外的数据库中对象的引用应使用适当的数据库名称进行限定。USE db_name

    有关存储例程中不允许的语句的更多信息,请参见“对存储程序的限制”。

    有关从以具有MySQL接口的语言编写的程序中调用存储过程的信息,请参见“ CALL语句”。

    MySQL存储在sql_mode创建或更改例程时有效的系统变量设置,并且始终在执行该设置的情况下执行该例程,而与例程开始执行时当前的服务器SQL模式无关

    在对参数进行评估并将结果值分配给例程参数之后,将发生从调用者的SQL模式到例程模式的切换。如果以严格SQL模式定义例程但以非严格模式调用例程,则在严格模式下不会将参数分配给例程参数。如果需要以严格的SQL模式分配传递给例程的表达式,则应以有效的严格模式调用该例程。

    COMMENT特性是MySQL扩展,可用于描述存储的例程。该信息由SHOW CREATE PROCEDUREand 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 = 1DO RELEASE_LOCK('abc'),它们执行但既不读取也不写入数据。
    • NO SQL指示例程不包含SQL语句。
    • READS SQL DATA表示该例程包含读取数据的语句(例如SELECT),但不包含写入数据的语句。
    • MODIFIES SQL DATA表示例程包含可能写入数据的语句(例如INSERTDELETE)。

    SQL SECURITY特征可以是DEFINERINVOKER指定安全上下文;也就是说,例程是否使用例程DEFINER子句中命名的帐户或调用该例程的用户的特权执行。该帐户必须有权访问与该例程相关联的数据库。默认值为DEFINER。调用例程的用户必须具有EXECUTE特权,DEFINER如果例程在定义者安全上下文中执行,则帐户也必须具有特权。

    DEFINER在程序执行时检查访问权限时是有程序要使用子句指定MySQL账户SQL SECURITY DEFINER特征。

    如果DEFINER子句,该user值应被指定为一个MySQL帐户,或。允许的值取决于您拥有的特权,如“存储的对象访问控制”中所述。另请参阅该部分以获取有关存储的例程安全性的其他信息。'user_name'@'host_name'CURRENT_USERCURRENT_USER()user

    如果DEFINER省略该子句,则默认定义器是执行CREATE PROCEDUREor 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;
    

    该过程仍然具有DEFINERof 'admin'@'localhost',但是在这种情况下,它以调用用户的特权执行。因此,该过程是成功还是失败取决于调用者是否具有EXECUTE它的SELECT特权以及mysql.user表的特权。

    服务器处理例程参数的数据类型,使用DECLARE或函数返回值创建的局部例程变量,如下所示:

    • 检查分配是否存在数据类型不匹配和溢出。转换和溢出问题会导致警告或严格SQL模式下的错误。
    • 只能分配标量值。例如,诸如这样的语句SET x =(SELECT 1, 2)无效。
    • 对于字符数据类型,如果CHARACTER SET声明中包含,则使用指定的字符集及其默认排序规则。如果COLLATE还存在该属性,则使用该排序规则而不是默认排序规则。

      如果CHARACTER SETCOLLATE不存在,则使用在常规创建时有效的数据库字符集和排序规则。为避免服务器使用数据库字符集和排序规则,请为字符数据参数提供显式属性CHARACTER SETCOLLATE属性。

      如果更改数据库默认字符集或排序规则,则必须删除并重新创建使用数据库默认值的存储例程,以便它们使用新的默认值。

      数据库字符集和排序规则由character_set_databasecollation_database系统变量的值给出。有关更多信息,请参见“数据库字符集和校验规则”。