MySQL 存储过程语法详解

创建过程的完整语法如下：

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


proc_parameter:
   [ IN | OUT | INOUT ] 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

说明：

• CREATE PROCEDURE  创建存储过程的关键字
  

• [DEFINER = user]  可选参数，用来指定该存储过程是由谁创建的，需要该用户具有 CREATE PROCEDURE 需要 CREATE ROUTINE 权限。

• [IF NOT EXISTS]  可选参数，如果当前名称的存储过程已经存在，则不创建。如果不存在，则创建该存储过程。

• sp_name  存储过程名称

• proc_parameter  可选参数，存储过程允许定义一个空参数列表，但是不能省略括号 “()”，你可以指定多个 proc_parameter，每一个 proc_parameter 表示一个参数，参数包含下面几个数据：

• [IN | OUT | INOUT]  参数传值类型，IN 表示输入参数，OUT 表示输出参数，INOUT 表示输入输出参数。

• param_name  表示参数名称，必须是一个合法的标识符

• type  表示参数的数据类型，必须是一个有效的 MySQL 数据类型

• characteristic  可选参数，用来为当前存储过程指定一些特殊特性，取值如下：

• COMMENT 'string'   用来为存储过程添加注释，以提供有关存储过程的额外说明或描述。注释可以用于文档化存储过程的用途、参数、返回值等。

• LANGUAGE SQL   用于指定存储过程的语言。LANGUAGE SQL 表示存储过程使用 SQL 语言编写。这是 MySQL 中最常见和最常用的存储过程语言。使用 LANGUAGE SQL 选项，可以在存储过程中使用标准的 SQL 语句和语法来编写逻辑。这使得存储过程的编写和维护更加简单和直观。除了 LANGUAGE SQL，MySQL 还支持其他存储过程语言，如LANGUAGE Java、LANGUAGE C 等。这些语言允许开发人员使用 Java、C 等编程语言来编写存储过程的逻辑。但是，绝大多数情况下，使用 LANGUAGE SQL 已经足够满足大多数需求。

• [NOT] DETERMINISTIC   用于指定存储过程是否是确定性的修饰符。确定性存储过程是指在相同的输入条件下，每次执行都会产生相同的结果。使用 DETERMINISTIC 修饰符可以提供一些性能优化，因为 MySQL 可以缓存确定性存储过程的结果，避免重复计算。这对于频繁调用的存储过程来说尤为重要。

  注意：MySQL 不会严格检查存储过程是否真的是确定性的，因此在使用 DETERMINISTIC 修饰符时需要确保存储过程的逻辑确实是确定性的。如果存储过程的逻辑不是确定性的，使用 DETERMINISTIC 修饰符可能会导致错误的结果。

  MySQL 还提供了 NOT DETERMINISTIC 修饰符，用于指定存储过程是非确定性的。非确定性存储过程在相同的输入条件下可能会产生不同的结果。如果不显式指定存储过程的确定性，MySQL 默认将其视为非确定性。

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

• SQL SECURITY { DEFINER | INVOKER }   指定存储过程的执行权限。SQL SECURITY 选项有以下两个可选值：

• DEFINER  表示存储过程的执行权限与定义者（创建存储过程的用户）的权限相同。这是默认的选项。

• INVOKER  表示存储过程的执行权限与调用者的权限相同。

• routine_body  存储过程的正文部分，一些有效的 SQL 常规语句，通常情况是一个代码块 BENG ...  END。

默认情况下，存储函数与默认数据库相关联。要将存储函数与指定数据库相关联，请在创建存储过程时将名称指定为 db_name.sp_name（db_name 是数据库名）。

如果要调用存储过程，请使用 CALL 语句。

CREATE PROCEDURE 需要 CREATE ROUTINE 权限。如果存在 DEFINER 子句，所需的权限取决于 DEFINER 子句的值。

默认情况下，MySQL 会自动授予存储过程创建者 ALTER ROUTINE 和 EXECUTE 权限。可以通过禁用 automatic_sp_privileges 系统变量来改变这种行为。

DEFINER 和 SQL SECURITY 子句指定了在存储过程执行时检查访问权限时使用的安全上下文，本节稍后将对此进行介绍。

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

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

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

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

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

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

mysql 广告位

存储过程示例

示例一：完整创建存储过程

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

-- 查看 @cities 变量结果
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)

-- 查看 @cities 变量结果
mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

示例二：DEFINER 关键字的使用

下面使用 DEFINER 关键字指定当前存储过程的拥有者为 admin@localhost 用户：

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.user 表的 SELECT 权限。

示例三：SQL SECURITY INVOKER 关键字的使用

下面示例假设存储过程是用 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'，但在这种情况下，它以调用用户的权限执行（因为使用了 SQL SECURITY INVOKER 语句）。因此，存储过程成功或失败取决于调用者是否拥有 EXECUTE 权限和 mysql.user 表的 SELECT 权限。