13.1.16 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(用户定义的函数)。参见第 28.4 节“向 MySQL 添加函数”。 UDF 可以视为外部存储功能。存储的函数与 UDF 共享其名称空间。有关描述服务器如何解释对各种功能的引用的规则，请参见第 9.2.5 节“函数名称的解析和解析”。

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

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

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

DEFINER和SQL SECURITY子句指定在例行执行时检查访问特权时要使用的安全上下文，如本节稍后所述。

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

IGNORE_SPACE SQL 模式适用于内置函数，不适用于存储的例程。始终允许在存储的例程名称后留空格，无论是否启用了IGNORE_SPACE。

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

默认情况下，每个参数都是IN参数。要为参数另外指定，请在参数名称前使用关键字OUT或INOUT。

Note

将参数指定为IN，OUT或INOUT仅对PROCEDURE有效。对于FUNCTION，参数始终被视为IN参数。

IN参数将值传递给过程。该过程可能会修改该值，但是该过程返回时，调用者看不到该修改。 OUT参数将值从过程传递回调用方。在过程中，其初始值为NULL，并且在过程返回时，调用者可以看到其初始值。 INOUT参数由调用者初始化，可以由过程修改，并且过程返回时，调用者可以看到该过程所做的任何更改。

对于每个OUT或INOUT参数，在调用该过程的CALL语句中传递用户定义的变量，以便在过程返回时可以获取其值。如果要从另一个存储过程或函数中调用过程，则还可以将例程参数或本地例程变量作为OUT或INOUT参数传递。如果要从触发器内调用过程，则还可以将NEW.col_name作为OUT或INOUT参数传递。

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

例程参数不能在例程中准备的语句中引用；参见第 23.8 节“对存储程序的限制”。

下面的示例显示了一个简单的存储过程，该存储过程在给定国家(locale)代码的情况下，对出现在world数据库的citytable 中的那个国家的城市进行计数。使用IN参数传递国家/locale 代码，并使用OUT参数返回城市计数：

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|     248 |
+---------+
1 row in set (0.00 sec)

mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

该示例在定义过程时使用mysql client delimiter命令将语句定界符从;更改为//。这使过程主体中使用的;分隔符可以传递到服务器，而不是由mysql本身解释。参见第 23.1 节“定义存储程序”。

只能为FUNCTION指定RETURNS子句，对此它是必需的。它指示函数的返回类型，并且函数主体必须包含RETURN value语句。如果RETURN语句返回其他类型的值，则该值将被强制为正确的类型。例如，如果函数在RETURNS子句中指定ENUM或SET值，但RETURN语句返回整数，则从函数返回的值是SET成员集中相应ENUM成员的字符串。

下面的示例函数使用参数，使用 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)

可以声明参数类型和函数返回类型以使用任何有效的数据类型。如果带有CHARACTER SET规范，则可以使用COLLATE属性。

  • routine_body *由有效的 SQL 例程语句组成。这可以是简单的语句，例如SELECT或INSERT，也可以是使用BEGIN和END编写的复合语句。复合语句可以包含声明，循环和其他控制结构语句。 第 13.6 节“复合语句”中描述了这些语句的语法。实际上，除非主体由单个RETURN语句组成，否则存储函数倾向于使用复合语句。

MySQL 允许例程包含 DDL 语句，例如CREATE和DROP。 MySQL 还允许存储过程(但不能存储函数)包含 SQL 事务语句，例如COMMIT。存储的函数可能不包含执行显式或隐式提交或回滚的语句。 SQL 标准不需要支持这些语句，该标准指出每个 DBMS 供应商都可以决定是否允许它们。

返回结果集的语句可以在存储过程中使用，但不能在存储函数中使用。此禁止包括不具有INTO var_list子句的SELECT语句以及其他诸如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(并且在例程终止时撤消)。使例程在执行时具有给定的默认数据库。对例程默认数据库以外的数据库中对象的引用应使用适当的数据库名称进行限定。

有关存储例程中不允许的语句的其他信息，请参见第 23.8 节“对存储程序的限制”。

有关从以具有 MySQL 界面的语言编写的程序中调用存储过程的信息，请参见第 13.2.1 节“ CALL 语句”。

MySQL 会在创建或更改例程时存储有效的sql_mode系统变量设置，并始终在此设置有效的情况下执行该例程，而不管例程开始执行时当前的服务器 SQL 模式如何。

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

COMMENT特性是 MySQL 扩展，可用于描述存储的例程。此信息通过显示创建步骤和显示创建功能语句显示。

LANGUAGE特性指示编写例程的语言。服务器忽略此 Feature；仅支持 SQL 例程。

如果例程对于相同的 Importing 参数始终产生相同的结果，则将其视为“确定性”，否则将其视为“不确定性”。如果例程定义中未提供DETERMINISTIC或NOT DETERMINISTIC，则默认值为NOT DETERMINISTIC。要声明函数是确定性的，必须显式指定DETERMINISTIC。

例程性质的评估基于创建者的“诚实”：MySQL 不会检查声明为DETERMINISTIC的例程是否没有产生不确定结果的语句。但是，错误声明例程可能会影响结果或影响性能。将不确定的例程声明为DETERMINISTIC可能会导致优化器做出错误的执行计划选择，从而导致意外结果。将确定性例程声明为NONDETERMINISTIC可能会导致不使用可用的优化，从而降低性能。

如果启用了二进制日志记录，则DETERMINISTIC特性会影响 MySQL 接受哪些例程定义。参见第 23.7 节“存储的程序二进制日志”。

包含NOW()函数(或其同义词)或RAND()的例程是不确定的，但是它可能仍然是安全复制的。对于NOW()，二进制日志包括时间戳记并可以正确复制。 RAND()也可以正确复制，只要在例程执行期间仅一次调用它即可。 (您可以将例程执行时间戳和随机数种子视为隐式 Importing，它们在主服务器和从属服务器上是相同的.)

几个特性提供了有关例程使用数据的性质的信息。在 MySQL 中，这些 Feature 仅是建议性的。服务器不使用它们来约束例程将被允许执行的语句类型。

  • CONTAINS SQLtable 示例程不包含读取或写入数据的语句。如果未明确给出这些 Feature，则为默认设置。此类语句的示例是SET @x = 1或DO RELEASE_LOCK('abc')，它们执行但既不读取也不写入数据。

  • NO SQLtable 示例程不包含 SQL 语句。

  • READS SQL DATAtable 示例程包含读取数据的语句(例如SELECT)，但不包含写入数据的语句。

  • MODIFIES SQL DATAtable 示例程包含可能写入数据的语句(例如INSERT或DELETE)。

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

DEFINER子句指定在例程执行时检查具有SQL SECURITY DEFINERFeature 的例程的访问权限时要使用的 MySQL 帐户。

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

如果省略DEFINER子句，则默认定义器是执行CREATE PROCEDURE或CREATE FUNCTION语句的用户。这与显式指定DEFINER = CURRENT_USER相同。

在以SQL SECURITY DEFINER特性定义的存储例程的主体内，CURRENT_USER函数返回该例程的DEFINER值。有关在存储的例程中进行用户审核的信息，请参见第 6.2.18 节“基于 SQL 的帐户活动审核”。

考虑以下过程，该过程显示mysql.user系统 table 中列出的 MySQL 帐户数：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

不管哪个用户定义了该程序，都为该程序分配了'admin'@'localhost'的DEFINER帐户。无论哪个用户调用该帐户，它都以该帐户的特权执行(因为默认的安全特性是DEFINER)。该过程是成功还是失败，取决于调用者对其具有EXECUTE特权，而'admin'@'localhost'对mysql.usertable 具有SELECT特权。

现在假设该过程是用SQL SECURITY INVOKER特性定义的：

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

该过程的DEFINER仍然为'admin'@'localhost'，但是在这种情况下，它以调用用户的特权执行。因此，该过程是成功还是失败取决于调用者是否对其具有EXECUTE特权和mysql.usertable 具有SELECT特权。

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

  • 检查分配是否存在数据类型不匹配和溢出。转换和溢出问题会导致警告或严格 SQL 模式下的错误。

  • 只能分配标量值。例如，诸如SET x = (SELECT 1, 2)之类的语句无效。

  • 对于字符数据类型，如果声明中包含CHARACTER SET，则使用指定的字符集及其默认排序规则。如果COLLATE属性也存在，则使用该排序规则而不是默认排序规则。

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

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

数据库字符集和排序规则由character_set_database和collation_database系统变量的值给出。有关更多信息，请参见第 10.3.3 节“数据库字符集和排序规则”。

Name MySql 中文文档
Version
Language
Badge
Last Updated 2021-07-03T12:24:35