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. CREATE FUNCTION语句在MySQL中也用于支持可加载函数。See Section 13.7.4.1, “CREATE FUNCTION Statement for Loadable Functions”. 请参阅第13.7.4.1节，“针对可加载函数的CREATE FUNCTION语句”。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.有关描述服务器如何解释对不同类型函数的引用的规则，请参阅第9.2.5节，“函数名解析和解析”。

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

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. CREATE PROCEDURE和CREATE FUNCTION需要CREATE ROUTINE权限。If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 25.6, “Stored Object Access Control”. 如果存在DEFINER子句，则所需的权限取决于user值，如第25.6节，“存储对象访问控制”所述。If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as discussed in Section 25.7, “Stored Program Binary Logging”.如果启用了二进制日志记录，CREATE FUNCTION可能需要SUPER权限，如第25.7节，“存储程序二进制日志记录”所述。

By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. 默认情况下，MySQL自动将ALTER ROUTINE权限和EXECUTE权限授予例程创建者。This behavior can be changed by disabling the automatic_sp_privileges system variable. 可以通过禁用automatic_sp_privileges系统变量来更改此行为。See Section 25.2.2, “Stored Routines and MySQL 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. 如果例程名称与内置SQL函数的名称相同，则会发生语法错误，除非在定义例程或稍后调用例程时在名称和以下括号之间使用空格。For this reason, avoid using the names of existing SQL functions for your own stored routines.因此，避免将现有SQL函数的名称用于您自己的存储例程。

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. IGNORE_SPACE SQL模式适用于内置函数，而不是存储例程。It is always permissible to have spaces after a stored routine name, regardless of whether IGNORE_SPACE is enabled.始终允许在存储的例程名称后面有空格，而不管是否启用了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.参数名称不区分大小写。

Each parameter is an IN parameter by default. 默认情况下，每个参数都是IN参数。To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.若要为参数指定其他值，请在参数名称之前使用关键字OUT或INOUT。

An IN parameter passes a value into a procedure. IN参数将值传递给过程。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. OUT参数将值从过程传递回调用方。Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. 它的初始值在过程中为NULL，当过程返回时，调用方可以看到它的值。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.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. 对于每个OUT或INOUT参数，在调用过程的CALL语句中传递一个用户定义的变量，以便在过程返回时获得其值。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. 如果从另一个存储过程或函数中调用该过程，还可以将例程参数或局部例程变量作为OUT或INOUT参数传递。If you are calling the procedure from within a trigger, you can also pass NEW.col_name as an OUT or INOUT parameter.如果是从触发器中调用过程，还可以将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节，“条件处理和输出或输入参数”。

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. 下面的示例显示了一个简单的存储过程，在给定国家/地区代码的情况下，该存储过程统计world数据库的city表中显示的该国家/地区的城市数。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. 该示例在定义过程时使用mysql客户机delimiter命令将语句分隔符从;更改为//。This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. 这使得在过程主体中使用的;分隔符将传递到服务器，而不是由mysql本身进行解释。See Section 25.1, “Defining Stored Programs”.请参阅第25.1节，“定义存储程序”。

The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. RETURNS子句只能为FUNCTION指定，对于FUNCTION，RETURNS子句是必需的。It indicates the return type of the function, and the function body must contain a RETURN value statement. 它表示函数的返回类型，函数体必须包含RETURN value语句。If the RETURN statement returns a value of a different type, the value is coerced to the proper type. 如果RETURN语句返回不同类型的值，则该值将强制为正确的类型。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子句中指定ENUM或SET值，但RETURN语句返回整数，则从函数返回的值是SET成员集合中相应ENUM成员的字符串。

The following example function takes a parameter, performs an operation using an SQL function, and returns the result. 下面的示例函数接受一个参数，使用SQL函数执行一个操作，并返回结果。In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:在这种情况下，不需要使用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)

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.如果前面有CHARACTER SET规范，则可以使用COLLATE属性。

The routine_body consists of a valid SQL routine statement. routine_body由有效的SQL例程语句组成。This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. 这可以是一个简单的语句，如SELECT或INSERT，也可以是使用BEGIN和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”. 第13.6节，“复合语句语法”中描述了这些语句的语法。In practice, stored functions tend to use compound statements, unless the body consists of a single RETURN statement.实际上，存储函数倾向于使用复合语句，除非主体由单个RETURN语句组成。

MySQL permits routines to contain DDL statements, such as CREATE and DROP. MySQL允许例程包含DDL语句，如CREATE和DROP。MySQL also permits stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT. MySQL还允许存储过程（而不是存储函数）包含诸如COMMIT之类的SQL事务语句。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.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. 此禁止包括没有INTO var_list子句的SELECT语句和其他语句，如SHOW、EXPLAIN和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). 对于可以在函数定义时确定返回结果集的语句，会出现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 error occurs (ER_SP_BADSELECT).对于只能在运行时确定返回结果集的语句，会出现PROCEDURE %s can't return a result set in the given context错误（ER_SP_BADSELECT）。

USE statements within stored routines are not permitted. 不允许在存储的例程中出现USE语句。When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). 调用例程时，将执行隐式USE db_name（并在例程终止时撤消）。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.对数据库中除例程默认数据库之外的对象的引用应使用适当的数据库名称限定。

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

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接口的语言编写的程序中调用存储过程的信息，请参阅第13.2.1节，“调用语句”。

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.MySQL存储在创建或更改例程时生效的sql_mode系统变量设置，并始终在该设置生效的情况下执行例程，而不管例程开始执行时的当前服务器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. 从调用程序的SQL模式切换到例程的SQL模式发生在参数求值和将结果值分配给例程参数之后。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. 如果在严格SQL模式下定义例程，但在非严格模式下调用它，则不会在严格模式下为例程参数赋值。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模式分配传递给例程的表达式，则应使用有效的严格模式调用例程。

The COMMENT characteristic is a MySQL extension, and may be used to describe the stored routine. COMMIT特性是MySQL扩展，可用于描述存储的例程。This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.此信息由SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION语句显示。

The LANGUAGE characteristic indicates the language in which the routine is written. LANGUAGE特性表示编写例程所用的语言。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. 如果例程定义中既没有给出DETERMINISTIC也没有给出NOT DETERMINISTIC，则默认值为NOT DETERMINISTIC。To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.要声明函数是确定性的，必须显式指定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. 对例程性质的评估基于创建者的“诚实”：MySQL不会检查声明为DETERMINISTIC的例程是否没有产生不确定性结果的语句。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. 将非确定性例程声明为DETERMINISTIC例程可能会导致优化器做出错误的执行计划选择，从而导致意外结果。Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.将确定性例程声明为DETERMINISTIC例程可能会导致不使用可用的优化，从而降低性能。

If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. 如果启用了二进制日志记录，则DETERMINISTIC特征会影响MySQL接受的例程定义。See Section 25.7, “Stored Program Binary Logging”.请参阅第25.7节，“存储程序二进制记录”。

A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. 包含NOW()函数（或其同义词）或RAND()的例程是不确定的，但它可能仍然是复制安全的。For NOW(), the binary log includes the timestamp and replicates correctly. 对于NOW()，二进制日志包含时间戳并正确复制。RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. 只要在例程执行期间只调用一次，RAND()也可以正确复制。(You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the source and replica.)（可以将例程执行时间戳和随机数种子视为源和副本上相同的隐式输入。）

Several characteristics provide information about the nature of data use by the routine. 有几个特征提供有关例程使用数据的性质的信息。In MySQL, these characteristics are advisory only. 在MySQL中，这些特性只是建议性的。The server does not use them to constrain what kinds of statements a routine is permitted to execute.服务器不使用它们来约束允许例程执行的语句类型。

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. SQL SECURITY特性可以是DEFINER或INVOKER来指定安全上下文；也就是说，例程是使用例程DEFINER子句中指定的帐户的权限执行，还是使用调用它的用户的权限执行。This account must have permission to access the database with which the routine is associated. 此帐户必须具有访问与例程关联的数据库的权限。The default value is DEFINER. 默认值是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.调用例程的用户必须具有该例程的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(). 如果存在DEFINER子句，则user值应为指定为'user_name'@'host_name'、CURRENT_USER或CURRENT_user()的MySQL帐户。The permitted user values depend on the privileges you hold, as discussed in Section 25.6, “Stored Object Access Control”. 允许的user值取决于您拥有的权限，如第25.6节，“存储对象访问控制”所述。Also see that section for additional information about stored routine security.有关存储例程安全性的更多信息，请参阅该部分。

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement. 如果省略了DEFINER子句，则默认的定义者是执行CREATE PROCEDURE语句或CREATE FUNCTION语句的用户。This is the same as specifying DEFINER = CURRENT_USER explicitly.这与显式指定DEFINER=CURRENT_USER效果相同。

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. 在使用SQL SECURITY DEFINER特性定义的存储例程的主体中，CURRENT_USER函数返回例程的DEFINER值。For information about user auditing within stored routines, see Section 6.2.22, “SQL-Based Account Activity Auditing”.有关存储例程中用户审核的信息，请参阅第6.2.22节，“基于SQL的帐户活动审核”。

Consider the following procedure, which displays a count of the number of MySQL accounts listed in the mysql.user system table:考虑下面的过程，它显示mysql.user系统表中列出的MySQL帐户数的计数：

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. 无论由哪个用户定义，都会为该过程分配一个DEFINDER帐户'admin'@'localhost'。It executes with the privileges of that account no matter which user invokes it (because the default security characteristic is DEFINER). 无论哪个用户调用它，它都以该帐户的权限执行（因为默认的安全特性是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.该过程的成功与否取决于调用程序是否对其具有EXECUTE权限，'admin'@'localhost'是否对mysql.user表具有选择权限。

Now suppose that the procedure is defined with the SQL SECURITY INVOKER characteristic:现在假设该过程是使用SQL SECURITY INVOKER性定义的：

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. 该过程仍然有一个'admin'@'localhost'的DEFINER，但在本例中，它以调用用户的权限执行。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.因此，该过程的成功与否取决于调用程序是否具有针对它的EXECUTE权限和针对mysql.user表的SELECT权限。

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