MySQL 8.0-CREATE PROCEDURE and CREATE FUNCTION Statements

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

These statements are used to create a stored routine (a stored procedure or function). That is, the specified routine becomes known to the server. By default, a stored 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.

这些语句用于创建存储例程(存储过程或函数)。也就是说，服务器知道指定的例程。默认情况下，存储的例程与默认数据库相关联。要将例程与给定的数据库显式关联，在创建它时指定名称为db_name.sp_name。

The CREATE FUNCTION statement is also used in MySQL to support loadable functions. See Section 13.7.4.1, “CREATE FUNCTION Statement for Loadable Functions”. A loadable function can be regarded as an external stored function. Stored functions share their namespace with loadable functions. See Section 9.2.5, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

MySQL中还使用CREATE FUNCTION语句来支持可加载函数。参见13.7.4.1节“为可加载函数创建函数语句”。可加载函数可以被视为外部存储函数。存储函数与可加载函数共享名称空间。有关服务器如何解释对不同类型函数的引用的规则，请参见9.2.5节“函数名称解析和解析”。

To invoke a stored procedure, use the CALL statement (see Section 13.2.1, “CALL Statement”). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

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

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 25.6, “Stored Object Access Control”. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as discussed in Section 25.7, “Stored Program Binary Logging”.

CREATE PROCEDURE和CREATE FUNCTION需要CREATE例程权限。如果存在DEFINER子句，所需的特权取决于用户值，如第25.6节“存储对象访问控制”中所讨论的。如果启用了二进制日志记录，CREATE FUNCTION可能需要SUPER权限，如25.7节“存储的程序二进制日志记录”中所讨论的。

By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 25.2.2, “Stored Routines and MySQL Privileges”.

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

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.

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

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.

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

The IGNORE_SPACE 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 is enabled.

IGNORE_SPACE SQL模式适用于内置函数，而不适用于存储例程。无论是否启用了IGNORE_SPACE，存储例程名称之后总是允许有空格。

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.

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

Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.

默认情况下，每个参数都是IN参数。若要以其他方式指定参数，请在参数名称之前使用关键字OUT或INOUT。

Note

Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.

将参数指定为IN、OUT或INOUT仅对PROCEDURE有效。对于FUNCTION，形参总是被视为IN形参。

An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

IN参数将值传递给过程。过程可能修改该值，但当过程返回时，调用者不可见修改。OUT参数将一个值从过程传递回调用者。它在过程中的初始值是NULL，当过程返回时，调用者可以看到它的值。INOUT参数由调用者初始化，过程可以修改，过程所做的任何更改在过程返回时对调用者都是可见的

For each OUT or INOUT parameter, pass a user-defined variable in the CALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an OUT or INOUT parameter. If you are calling the procedure from within a trigger, you can also pass NEW.col_name as an OUT or INOUT parameter.

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

For information about the effect of unhandled conditions on procedure parameters, see Section 13.6.7.8, “Condition Handling and OUT or INOUT Parameters”.

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

Routine parameters cannot be referenced in statements prepared within the routine; see Section 25.8, “Restrictions on Stored Programs”.

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

The following example shows a simple stored procedure that, given a country code, counts the number of cities for that country that appear in the city table of the world database. The country code is passed using an IN parameter, and the city count is returned using an OUT parameter:

下面的示例显示了一个简单的存储过程，给定一个国家代码，计算出现在世界数据库的城市表中的国家的城市数量。使用IN参数传递国家代码，使用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)

The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 25.1, “Defining Stored Programs”.

示例使用mysql客户端分隔符命令更改语句分隔符;到//当过程被定义时。这使得;在传递给服务器的过程主体中使用的分隔符，而不是由mysql本身解释。参见25.1节，“定义存储程序”。

The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN statement returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members.

只能为函数指定RETURNS子句，因为它是强制性的。它指示函数的返回类型，并且函数体必须包含return值语句。如果RETURN语句返回不同类型的值，则将该值强制转换为适当的类型。例如，如果一个函数在RETURNS子句中指定了一个ENUM或SET值，但是RETURN语句返回的是一个整数，那么函数返回的值就是SET成员中的SET成员对应的ENUM成员的字符串。

The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

下面的示例函数接受一个参数，使用SQL函数执行操作，并返回结果。在这种情况下，没有必要使用分隔符，因为函数定义不包含内部;语句分隔符:

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)

Parameter types and function return types can be declared to use any valid data type. The COLLATE attribute can be used if preceded by a CHARACTER SET specification.

形参类型和函数返回类型可以声明为使用任何有效的数据类型。如果前面有字符集规范，则可以使用COLLATE属性。

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, “Compound Statement Syntax”. In practice, stored functions tend to use compound statements, unless the body consists of a single RETURN statement.

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

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.

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

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). 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).

返回结果集的语句可以在存储过程中使用，但不能在存储函数中使用。这种禁止包括没有INTO var_list子句的SELECT语句和其他语句，如SHOW、EXPLAIN和CHECK TABLE。对于可以在函数定义时确定返回结果集的语句，将发生不允许从函数返回结果集错误(ER_SP_NO_RETSET)。对于只能在运行时确定返回结果集的语句，PROCEDURE %s不能在给定的上下文中返回结果集(ER_SP_BADSELECT)

 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). The 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.

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

For additional information about statements that are not permitted in stored routines, see Section 25.8, “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 Statement”.

MySQL stores the sql_mode system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing.

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.

从调用者的SQL模式切换到例程的SQL模式发生在对参数求值并将结果值赋给例程参数之后。如果在严格SQL模式下定义例程，但在非严格模式下调用它，则在严格模式下不会将参数分配给例程参数。如果需要在严格SQL模式下为传递给例程的表达式赋值，则应该使用严格模式调用该例程。

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.

COMMENT特性是一个MySQL扩展，可以用来描述存储的例程。此信息由SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION语句显示。

The LANGUAGE characteristic indicates the language in which the routine is written. The server ignores this characteristic; only SQL routines are supported.

语言特征表明程序所使用的语言。服务器忽略这个特征;只支持SQL例程。

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.

如果一个例程总是为相同的输入参数产生相同的结果，那么它就被认为是“确定的”，否则就被认为是“不确定的”。如果在例程定义中既没有给出DETERMINISTIC也没有给出NOT DETERMINISTIC，则默认为NOT DETERMINISTIC。要声明一个函数是确定性的，必须显式地指定deterministic。

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

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

If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 25.7, “Stored Program Binary Logging”.

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

A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the source and replica.)

包含NOW()函数(或其同义词)或RAND()的例程是不确定的，但它可能仍然是复制安全的。对于NOW()，二进制日志包括时间戳并正确复制。只要RAND()在例程执行期间只被调用一次，它就可以正确地复制。(可以将例程执行时间戳和随机数种子视为隐式输入，它们在源和副本上是相同的。)

Several characteristics provide information about the nature of data use by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine is permitted to execute.

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

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• CONTAINS SQL表示例程不包含读或写数据的语句。如果没有明确给出这些特征，这是默认值。这类语句的例子有SET @x = 1或DO RELEASE_LOCK('abc')，它们执行但不读也不写数据。

• NO SQL indicates that the routine contains no SQL statements.

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

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• READS SQL DATA表示例程包含读取数据的语句(例如，SELECT)，而不包含写数据的语句。

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

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

The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.

SQL SECURITY特征可以是DEFINER或INVOKER来指定安全上下文;也就是说，例程是使用例程DEFINER子句中指定的帐户的权限执行，还是使用调用它的用户执行。此帐户必须具有访问与例程关联的数据库的权限。默认值为DEFINER。调用例程的用户必须拥有它的EXECUTE特权，如果例程在定义器安全上下文中执行，那么DEFINER帐户也必须拥有该特权。

The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

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

If the DEFINER clause is present, the user value should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The permitted user values depend on the privileges you hold, as discussed in Section 25.6, “Stored Object Access Control”. Also see that section for additional information about stored routine security.

如果存在DEFINER子句，用户值应该是一个MySQL帐户，指定为'user_name'@'host_name'， CURRENT_USER，或CURRENT_USER()。允许的用户值取决于您拥有的特权，如第25.6节“存储对象访问控制”中所讨论的。有关存储的例程安全性的其他信息，请参见该部分。

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

Within the body of a stored routine that is defined with the SQL SECURITY DEFINER characteristic, the CURRENT_USER function returns the routine's DEFINER value. For information about user auditing within stored routines, see Section 6.2.23, “SQL-Based Account Activity Auditing”.

Consider the following procedure, which displays a count of the number of MySQL accounts listed in the mysql.user system table:

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

The procedure is assigned a DEFINER account of 'admin'@'localhost' no matter which user defines it. It executes with the privileges of that account no matter which user invokes it (because the default security characteristic is DEFINER). The procedure succeeds or fails depending on whether invoker has the EXECUTE privilege for it and 'admin'@'localhost' has the SELECT privilege for the mysql.user table.

无论哪个用户定义了这个过程，它都会被分配一个DEFINER帐户“admin”@“localhost”。无论哪个用户调用它，它都使用该帐户的特权执行(因为默认的安全特征是DEFINER)。这个过程的成功或失败取决于调用者是否对它有EXECUTE权限，而'admin'@'localhost'对mysql有SELECT权限。用户表。

Now suppose that the procedure is defined with the SQL SECURITY INVOKER characteristic:

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

The procedure still has a DEFINER of 'admin'@'localhost', but in this case, it executes with the privileges of the invoking user. Thus, the procedure succeeds or fails depending on whether the invoker has the EXECUTE privilege for it and the SELECT privilege for the mysql.user table.

这个过程仍然有一个定义器'admin'@'localhost'，但是在本例中，它以调用用户的权限执行。因此，过程的成功或失败取决于调用者对它是否有EXECUTE特权，对mysql是否有SELECT特权。用户表。

The server handles the data type of a routine parameter, local routine variable created with DECLARE, or function return value as follows:

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

• Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict SQL mode.

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

• Only scalar values can be assigned. For example, a statement such as SET x = (SELECT 1, 2) is invalid.

• 只能分配标量值。例如，像SET x = (SELECT 1,2)这样的语句是无效的。

• For character data types, if CHARACTER SET is includedd in the declaration, the specified character set and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than the default collation.

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

  If CHARACTER SET and COLLATE are not present, the database character set and collation in effect at routine creation time are used. To avoid having the server use the database character set and collation, provide an explicit CHARACTER SET and a COLLATE attribute for character data parameters.

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

  If you alter the database default character set or collation, stored routines that are to use the new database defaults must be dropped and recreated.如果更改数据库默认字符集或排序规则，则必须删除并重新创建将要使用新数据库默认值的存储例程。

  The database character set and collation are given by the value of the character_set_database and collation_database system variables. For more information, see Section 13.2.1, “CALL Statement”. 数据库字符集和排序规则由character_set_database和collation_database系统变量的值给出。有关更多信息，请参见10.3.3节“数据库字符集和排序规则”。