CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] 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:
    SQL routine

这些语句用于创建存储例程（存储过程或函数）。也就是说，指定的例程会被服务器知道。默认情况下，存储例程与默认数据库关联。要将例程显式关联到给定数据库，创建时指定名称为 db_name.sp_name。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.

CREATE FUNCTION 语句也在 MySQL 中用于支持可加载函数。参见 第 15.7.4.1 节，“CREATE FUNCTION Statement for Loadable Functions”。可加载函数可以视为外部存储函数。存储函数与可加载函数共享命名空间。参见 第 11.2.5 节“函数名解析与解析”，以了解服务器如何解释不同类型函数引用的规则。The CREATE FUNCTION statement is also used in MySQL to support loadable functions. See Section 15.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 11.2.5, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

调用存储过程时，请使用 CALL 语句（参见 第 15.2.1 节，“CALL 语句”）。调用存储函数时，请在表达式中引用。函数在表达式求值时返回一个值。To invoke a stored procedure, use the CALL statement (see Section 15.2.1, “CALL Statement”). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

CREATE PROCEDURE 和 CREATE FUNCTION 需要 CREATE ROUTINE 权限。如果存在DEFINER子句，所需的权限取决于user value，如第 27.6 节“存储对象访问控制”中讨论。如果启用了二进制日志，CREATE FUNCTION可能需要 SUPER 权限，如 第 27.7 节“存储程序二进制日志”中讨论。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 27.6, “Stored Object Access Control”. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as discussed in Section 27.7, “Stored Program Binary Logging”.

默认情况下，MySQL 会自动赋予例程创建者 ALTER ROUTINE 和 EXECUTE 权限。通过禁用系统变量 automatic_sp_privileges。参见 第 27.2.2 节，“存储例程与 MySQL 权限”。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 27.2.2, “Stored Routines and MySQL Privileges”.

DEFINER 和 SQL SECURITY 子句指定了例程执行时检查访问权限时使用的安全上下文，如本节后文所述。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.

如果例程名称与内置 SQL 函数的名称相同，则会发生语法错误，除非在定义例程或以后调用它时在名称与随后的括号之间使用空格。出于这个原因，避免为自己存储的例程使用现有 SQL 函数的名称。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.

IGNORE_SPACE SQL 模式适用于内置函数，而非存储例程。无论是否启用IGNORE_SPACE。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.

IF NOT EXISTS 防止在已有同名例程时发生错误。该选项支持 CREATE FUNCTION 和 CREATE PROCEDURE。IF NOT EXISTS prevents an error from occurring if there already exists a routine with the same name. This option is supported with both CREATE FUNCTION and CREATE PROCEDURE.

如果已有同名的内置函数，尝试创建带有 CREATE FUNCTION ... IF NOT EXISTS，则会收到警告，提示其与原生函数名称相同;这与执行相同的 CREATE FUNCTION 语句但未指定 IF NOT EXISTS。If a built-in function with the same name already exists, attempting to create a stored function with CREATE FUNCTION ... IF NOT EXISTS succeeds with a warning indicating that it has the same name as a native function; this is no different than when performing the same CREATE FUNCTION statement without specifying IF NOT EXISTS.

如果已有同名可加载函数，尝试使用 IF NOT EXISTS会成功，但会收到警告。这与未指定 IF NOT EXISTS。If a loadable function with the same name already exists, attempting to create a stored function using IF NOT EXISTS succeeds with a warning. This is the same as without specifying IF NOT EXISTS.

更多信息请参见 Function Name Resolution。See Function Name Resolution, for more information.

括号内的参数列表必须始终存在。如果没有参数，应使用空参数列表 ()参数名称不区分大小写。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.

每个参数默认为 IN 参数。要对参数进行其他指定，可以在参数名称前使用关键字 OUT 或 INOUT。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 参数将过程的一个值传回调用者。其初始值为 NULL，过程返回时调用者可见其值。INOUT 参数由调用者初始化，过程可修改，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.

对于每个 OUT 或 INOUT 参数，在 CALL 语句中传递用户自定义变量，调用过程时获取其值。如果你是在另一个存储过程或函数中调用该过程，你也可以将例程参数或本地例程变量传递为 OUT 或 INOUT 参数。如果你是在触发器内部调用该过程，也可以传递 NEW.col_name作为 OUT 或 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.

有关未处理条件对过程参数影响的信息，请参见 第 15.6.7.8 节，“条件处理及 OUT 或 INOUT 参数”。For information about the effect of unhandled conditions on procedure parameters, see Section 15.6.7.8, “Condition Handling and OUT or INOUT Parameters”.

例程参数不能在例程内的语句中引用;参见 第 27.8 节“存储程序限制”。Routine parameters cannot be referenced in statements prepared within the routine; see Section 27.8, “Restrictions on Stored Programs”.

以下示例展示了一个简单的存储过程，在给定一个国家代码时，统计该国家出现在cityThe 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:

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 客户端 delimiter 命令，在过程定义过程中将语句分隔符从 ; 改为 //这使得过程体中使用的;分隔符能够传递给服务器，而不是被mysql本身。参见 第 27.1 节“定义存储程序”。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 27.1, “Defining Stored Programs”.

该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或者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.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.

以下示例函数取参数，使用 SQL 函数执行操作，返回结果。此时无需使用delimiter，因为函数定义中不包含内部;语句分隔符：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:

mysql> CREATE FUNCTION hello (s CHAR(20))
    ->   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 和 function return types 都可以声明使用任意有效数据类型。如果前有COLLATEParameter 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.

该routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT或者INSERT, or a compound statement written using BEGIN以及END. Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described in Section 15.6, “Compound Statement Syntax”. In practice, stored functions tend to use compound statements, unless the body consists of a single RETURN在定义具有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 15.6, “Compound Statement Syntax”. In practice, stored functions tend to use compound statements, unless the body consists of a single RETURN statement.

MySQL 允许例程包含 DDL 语句，例如 CREATE 和 DROPMySQL 还允许存储过程（但不允许存储函数）包含 SQL 事务语句，如 COMMIT存储函数不得包含执行显式或隐式提交或回滚的语句。SQL 标准并不要求支持这些语句，标准规定每个数据库管理系统厂商可自行决定是否允许。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.

返回结果集的语句可以在存储过程内使用，但不能在存储函数内使用。这种禁止包括SELECT没有INTO var_list子句的语句以及其他语句，例如SHOW, EXPLAIN，以及CHECK TABLE。对于在函数定义时可以确定返回结果集的语句，会发生Not allowed to return a result set from a function错误（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错误（ER_SP_BADSELECT).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).

USE当需要全表扫描时。自然行顺序是行在USE db_name在执行过程中进行（并在例程终止时撤销）。这会导致例程在执行时使用给定的默认数据库。对非例程默认数据库中的对象的引用应使用相应的数据库名称进行限定。USE 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.

有关存储程序中不允许的语句的更多信息，请参见 第 27.8 节，“存储程序的限制”。For additional information about statements that are not permitted in stored routines, see Section 27.8, “Restrictions on Stored Programs”.

有关如何从具有 MySQL 接口的语言编写的程序中调用存储过程的信息，请参见 第 15.2.1 节，“CALL 语句”。For information about invoking stored procedures from within programs written in a language that has a MySQL interface, see Section 15.2.1, “CALL Statement”.

MySQL 将创建或修改例程时生效的 sql_mode 系统变量设置存储起来，并始终在执行例程时使用此设置，无论例程开始执行时当前服务器的 SQL 模式如何。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.

从调用者的 SQL 模式切换到例程的 SQL 模式发生在对参数进行求值并将结果值分配给例程参数之后。如果你在严格 SQL 模式下定义了例程，但在非严格模式下调用它，参数分配给例程参数时不会在严格模式下进行。如果你要求传递给例程的表达式必须在严格 SQL 模式下分配，则应该在严格模式下调用该例程。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.

COMMENT 特性是 MySQL 的扩展，可用于描述存储程序。此信息由 SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION 语句显示。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.

LANGUAGE 特性指示程序编写所使用的语言。服务器会忽略此特性；仅支持 SQL 程序。The LANGUAGE characteristic indicates the language in which the routine is written. The server ignores this characteristic; only SQL routines are supported.

如果程序在相同输入参数下总是产生相同结果，则该程序被视为 “确定的”，否则为 “不确定的”。如果程序定义中未给出 DETERMINISTIC 或 NOT DETERMINISTIC，默认值为 NOT DETERMINISTIC。若要声明函数为确定性函数，必须显式指定 DETERMINISTIC。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.

对程序性质的评估基于创建者的 “诚信”：MySQL 不会检查声明为 DETERMINISTIC 的程序中是否存在产生不确定结果的语句。然而，错误声明程序可能会影响结果或性能。将不确定程序声明为 DETERMINISTIC 可能导致优化器选择错误的执行计划，从而产生意外结果。将确定程序声明为 NONDETERMINISTIC 可能会降低性能，因为未能使用可用优化。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.

如果启用了二进制日志，DETERMINISTIC 特性会影响 MySQL 接受哪些程序定义。参见 第 27.7 节，“存储程序二进制日志”。If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 27.7, “Stored Program Binary Logging”.

包含 NOW() 函数（或其同义词）或 RAND() 的程序是不确定的，但仍可能是复制安全的。对于 NOW()，二进制日志会包含时间戳并正确复制。RAND() 也会正确复制，只要它在程序执行过程中只被调用一次。（可以把程序执行时间戳和随机数种子视为在源端和副本端相同的隐式输入。）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.)

有几个特性可以提供例程使用数据性质的信息。在 MySQL 中，这些特性仅供参考。服务器不会使用它们来限制例程允许执行的语句类型。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.

SQL SECURITY 特性可以是 DEFINER 或 INVOKER，以指定安全上下文;也就是说，例程是使用例程中指定的账户权限DEFINER 子句，还是调用该程序的用户。该账户必须有权限访问 例程是关联的。默认值为 DEFINER。调用例程的用户必须拥有 EXECUTE 权限，例程在 definer 安全上下文中执行时，DEFINER。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.

DEFINER 子句指定了 MySQL 账户在例行执行时检查访问权限时使用的 MySQL 账户，这些例程的 SQL SECURITY 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 clause is present, the user value should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER，或CURRENT_USER(). The permitted user values depend on the privileges you hold, as discussed in 第 27.6 节，“存储对象访问控制”中所讨论的. Also see that section for additional information about stored routine security.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 27.6, “Stored Object Access Control”. Also see that section for additional information about stored routine security.

如果省略了DEFINER 子句，默认定义者是执行 CREATE PROCEDURE 或 CREATE FUNCTION 语句。这等同于显式指定 DEFINER = CURRENT_USER。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.

在定义为 SQL SECURITY DEFINER中，CURRENT_USER函数返回例程的 DEFINER 值。有关存储例程内用户审计的信息，请参见 第 8.2.23 节，“基于 SQL 的账户活动审计”。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 8.2.23, “SQL-Based Account Activity Auditing”.

考虑以下过程，显示 mysql.user：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;

该过程被分配一个 DEFINER 账户，该账户为 'admin'@'localhost'，无论哪个用户定义。无论哪个用户调用该账户，它都会以该账户的权限执行（因为默认安全特征是 DEFINER）。该过程的成功与否取决于调用者是否拥有 EXECUTE 权限，以及 'admin'@'localhost' 是否拥有 SELECT 权限，适用于 mysql.user 表。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.

现在假设该过程定义为 SQL SECURITY INVOKER 特征：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'，但在这种情况下，它以调用用户的权限执行。因此，该过程是否成功取决于调用者是否拥有EXECUTE它的权限以及SELECT表的mysql.user权限。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.

默认情况下，当执行具有 SQL SECURITY DEFINER时，MySQL 服务器不会为 DEFINER，只设置默认角色。例外情况是，如果系统变量启用了 activate_all_roles_on_login 服务器会设置所有授予 DEFINER 用户的角色，包括强制角色。因此，当CREATE PROCEDURE“或 CREATE FUNCTION 语句时，默认不会被检查。对于存储程序，如果执行角色与默认不同，程序本体可以执行 SET ROLE”来激活所需角色。此操作需谨慎，因为分配给角色的权限可能会被更改。By default, when a routine with the SQL SECURITY DEFINER characteristic is executed, MySQL Server does not set any active roles for the MySQL account named in the DEFINER clause, only the default roles. The exception is if the activate_all_roles_on_login system variable is enabled, in which case MySQL Server sets all roles granted to the DEFINER user, including mandatory roles. Any privileges granted through roles are therefore not checked by default when the CREATE PROCEDURE or CREATE FUNCTION statement is issued. For stored programs, if execution should occur with roles different from the default, the program body can execute SET ROLE to activate the required roles. This must be done with caution since the privileges assigned to roles can be changed.

服务器处理例程参数的数据类型，即本地例程变量，创建时DECLARE函数返回值如下：The server handles the data type of a routine parameter, local routine variable created with DECLARE, or function return value as follows: