第 32 篇存储过程（二）

imodouAbout 14 minMySQL

《MySQL 入门教程》第 32 篇存储过程（二）

书接上文，本篇我们来详细介绍一下 MySQL 存储过程和函数支持的编程功能。

32.1 存储过程变量

变量（Variable）是存储过程和函数内部用于存储中间结果的对象。这些变量属于局部变量，作用域限定在存储过程中。在使用存储过程变量之前需要使用DECLARE语句声明变量：

DECLARE variable_name datatype(size) [DEFAULT default_value];

其中，DECLARE 表达定义变量，必须位于 BEGIN 和 END 之间并且是第一个语句；variable_name 是变量名；datatype(size) 是变量的类型和长度；DEFAULT default_value 用于为变量指定一个默认值，否则默认值为 NULL。例如，以下是一些变量的定义：

DECLARE salary DECIMAL(8, 2) DEFAULT 0.0;
DECLARE x, y INTEGER DEFAULT 0;

salary 是 DECIMAL(8, 2) 类型的变量，默认值为 0.0；x 和 y 是 INTEGER 类型变量，默认值为 0。

声明了变量之后，可以使用SET语句进行赋值：

SET variable_name = value;

或者使用SELECT INTO语句将查询的结果赋值给变量：

SELECT expression1, ...
INTO variable1, ...
FROM ...;

SELECT expression1, ...
FROM ...
INTO variable1, ...;

SELECT 返回的表达式个数和变量的个数相同，查询语句最多只能返回一行数据，可以使用 LIMIT 1 进行限定。

以下语句创建了一个存储过程 TestProc：

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
  DECLARE sal decimal(8, 2) DEFAULT 0.0;
  DECLARE x, y integer DEFAULT 0;
  
  SELECT salary
  FROM employee
  WHERE emp_name = '张飞'
  INTO sal;
 
  SET x = 6, y = 9;
 
  SELECT sal, x, y;
  
END$$

DELIMITER ;

调用该存储过程查看变量的赋值结果：

CALL TestProc();

sal     |x|y|
--------|-|-|
24000.00|6|9|

对于存储过程变量，它的作用域（生命周期）位于存储过程定义中的 BEGIN 和 END 语句之间。如果在嵌套的 BEGIN 和 END 语句之间定义的变量，作用域只在这个模块内部。如果不同作用域中存在同名的变量，在作用域内有效范围更小的变量优先级更高。例如：

DROP PROCEDURE TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
  DECLARE x integer DEFAULT 0; 
  SET x = 6;
  SELECT 'outer', x;
 
  BEGIN
	DECLARE x integer DEFAULT 0;
    SET x = 7;
    SELECT 'inner', x;
  END;
  
END$$

DELIMITER ;

存储过程中先定义了一个个变量 x，然后在嵌套模块中又定义了同名的变量。调用该存储过程的输出结果如下：

CALL TestProc();

outer|x|
-----|-|
outer|6|

inner|x|
-----|-|
inner|7|

在嵌套模块中查询返回的是该模块中的变量 x。虽然 MySQL 支持这种同名变量的使用，但是建议尽量不要这样使用，避免引起混淆。

📝关于 MySQL 系统变量和用户变量的介绍，可以参考本教程的第 17 篇。

32.2 条件控制语句

MySQL 提供了两种条件控制语句：IF 语句和 CASE 语句。

32.2.1 IF 语句

IF 语句可以实现基本的条件判断结构，语法如下：

IF search_condition THEN statement_list
    [ELSEIF other_condition THEN other_statement_list] 
    ...
    [ELSE else_statement_list]
END IF;

如果 search_condition 的结果为 true，指定对应 THEN 之后的 statement_list 语句列表；否则，如果存在可选的 ELSEIF 并且 other_condition 结果为 true，执行对应的 other_statement_list 语句列表；依次类推；否则，如果存在可选的 ELSE，执行对应的 else_statement_list 语句列表。

例如：

DROP PROCEDURE TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
  DECLARE x, y integer DEFAULT 0; 
  SET x = 5, y = 10;
 
  IF x = 5 THEN
    SELECT 'x = 5';
  END IF;
 
  IF y < 10 THEN
    SELECT 'y < 10';
  ELSEIF y > 10 THEN
    SELECT 'y > 10';
  ELSE
    SELECT 'y = 10';
  END IF;
  
END$$

DELIMITER ;

调用该存储过程的输出结果如下：

CALL TestProc();

x = 5|
-----|
x = 5|

y = 10|
------|
y = 10|

32.2.2 CASE 语句

CASE 语句可以用于构造复杂的条件判断结构，支持两种形式的语法。第一种简单形式的 CASE 语句如下：

CASE case_value
    WHEN when_value1 THEN statement_list1
    [WHEN when_value2 THEN statement_list2] 
    ...
    [ELSE else_statement_list]
END CASE;

其中，case_value 是一个表达式；首先使用该表达式的值和 when_value1 进行比较，如果相等则执行 statement_list1 语句列表并结束 CASE 语句；否则，如果表达式等于可选的 when_value2，则执行 statement_list2 并结束 CASE 语句；依次类推；最后，如果存在可选的 ELSE，执行 else_statement_list 语句列表；如果此时没有定义 ELSE 子句，将会返回“Case not found for CASE statement”错误。

第二种形式的搜索 CASE 语句如下：

CASE
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition2 THEN statement_list2] 
    ...
    [ELSE else_statement_list]
END CASE;

首先，判断 search_condition1 是否为 true；如果是则执行 statement_list1 语句列表；否则，判断 search_condition2 是否为 true，如果是则执行 statement_list2 语句列表；依此类推；最后，如果存在可选的 ELSE，执行 else_statement_list 语句列表；如果此时没有定义 ELSE 子句，将会返回“Case not found for CASE statement”错误。

例如：

DROP PROCEDURE TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
  DECLARE x integer DEFAULT 0; 
  SET x = 5;
 
  CASE
    WHEN x < 0 THEN
      SELECT 'x < 0';
    WHEN x = 0 THEN
      SELECT 'x = 0';
    ELSE
      SELECT 'x > 0';
  END CASE;
  
END$$

DELIMITER ;

调用该存储过程的输出结果如下：

CALL TestProc();

x > 0|
-----|
x > 0|

⚠️第 13 篇我们介绍了 CASE 表达式与 IF 函数，和本篇的 CASE 语句和 IF 语句是不同的概念。

32.3 循环控制语句

MySQL 支持三种循环控制语句：LOOP、REPEAT 以及 WHILE 语句。

32.3.1 LOOP 语句

LOOP语句用于实现简单的循环结构，语法如下：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP 语句重复执行 statement_list 直到循环被终止，通常使用LEAVE label语句退出循环，或者使用函数的 RETURN 语句退出整个函数。begin_label 和 end_label 是该循环语句的标签。例如：

DELIMITER $$

CREATE PROCEDURE TestLoop()
BEGIN
  DECLARE x,sumx integer DEFAULT 0; 
 
  label1: LOOP
    SET x = x + 1;
    
    IF x > 10 THEN
      LEAVE label1;
    END IF;
    IF x mod 2 THEN
      ITERATE label1;
    ELSE
      SET sumx = sumx + x;
    END IF;
    
  END LOOP label1;
 
  SELECT sumx;
  
END$$

DELIMITER ;

其中，LEAVE 表示 x 大于 10 时退出循环 label1；ITERATE 类似于 C++ 或者 Java 中的 CONTINUE，表示 x 为奇数则进入下一次循环，否则将其增加到变量 sumx 。存储过程的运行结果如下：

CALL TestLoop();

sumx|
----|
  30|

⚠️如果 LOOP 循环语句中没有指定退出条件，将会进入死循环。

32.3.2 REPAET 语句

REPAET语句用于重复执行指定语句列表直到某个条件为真，语法如下：

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label];

REPEAT 语句首先执行 statement_list，然后判断 search_condition 是否为 true；如果是则终止循环，否则再次循环。REPAET 语句至少会执行一次。例如，上面的 LOOP 语句示例使用 REPAET 实现如下：

DELIMITER $$

CREATE PROCEDURE TestRepeat()
BEGIN
  DECLARE x,sumx integer DEFAULT 0; 
 
  label1: REPEAT
    SET x = x + 1;
    
    IF x mod 2 THEN
      ITERATE label1;
    ELSE
      SET sumx = sumx + x;
    END IF;
    
  UNTIL x = 10 END REPEAT label1;
 
  SELECT sumx;
  
END$$

DELIMITER ;

UNTIL 指定了 x = 10 时退出循环，执行该存储过程的结果和上一节相同。

32.3.3 WHILE 语句

WHILE语句基于某个条件为真时重复执行指定的语句列表，语法如下：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label];

首先，判断 search_condition 是否为 true；如果是则执行 statement_list 语句列表，否则退出循环语句。WHILE 语句可能一次也不执行。例如，上面的 LOOP 语句示例使用 WHILE 实现如下：

DELIMITER $$

CREATE PROCEDURE TestWhile()
BEGIN
  DECLARE x,sumx integer DEFAULT 0; 
 
  label1: WHILE x < 10 DO
    SET x = x + 1;
    
    IF x mod 2 THEN
      ITERATE label1;
    ELSE
      SET sumx = sumx + x;
    END IF;
    
  END WHILE label1;
 
  SELECT sumx;
  
END$$

DELIMITER ;

32.4 错误处理

当存储过程或者函数在执行过程中出现某种错误条件（例如警告或者异常）时，需要进行特殊处理，例如退出当前程序模块或者继续执行，同时返回一个有用的错误信息。

32.4.1 定义错误条件

MySQL 提供了定义错误条件的DECLARE ... CONDITION语句：

DECLARE condition_name CONDITION 
FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate_value }

其中，condition_name 是错误条件的名称，可以用于随后的错误处理器声明；mysql_error_code 表示 MySQL 错误码，例如 1062 代表了唯一键重复错误；SQLSTATE 表示使用 5 位字符串代表的 SQLSTATE 值，例如“42000” 和错误码 1062 一样代表了唯一键重复错误。

32.4.2 定义错误处理器

定义了错误条件之后，我们可以使用DECLARE ... HANDLER语句定义处理一个或多个错误条件的错误处理器：

DECLARE { CONTINUE | EXIT } HANDLER
    FOR condition_value [, condition_value] ...
    statement;

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

该语句定义了一个错误处理器；当某个错误条件 condition_value 发生时，执行指定的 statement 语句，可以是单个语句或者 BEGIN/END 代码块；然后执行相应的操作，CONTINUE 表示继续执行当前程序，EXIT 表示终止执行错误处理器所在的 BEGIN/END 模块。

指定错误条件时，mysql_error_code 表示 MySQL 错误码，SQLSTATE 表示使用 5 位字符串代表的 SQLSTATE 值，condition_name 是使用 DECLARE ... CONDITION 语句定义的错误条件名称，SQLWARNING 代表了以“01”开头的一类 SQLSTATE 值，NOT FOUND 代表了以“02”开头的一类 SQLSTATE 值，SQLEXCEPTION 代表了以“00”、“01”或者“02”开头的一类 SQLSTATE 值。

例如：

DELIMITER $$

CREATE PROCEDURE TestError()
BEGIN
  DECLARE empid int DEFAULT 1;
  DECLARE duplicate_employee CONDITION FOR 1062;
  
  DECLARE EXIT HANDLER
  FOR duplicate_employee
  BEGIN
	ROLLBACK;
    SELECT CONCAT('重复的员工信息！', 'emp_id:', empid) Message;
  END;    
        
  INSERT INTO employee 
  VALUES (empid, '刘备', '男', 1, NULL, DATE('2000-01-01'), 1, 30000, 10000, 'liubei@shuguo.com');
END$$

DELIMITER ;

首先，我们定义了一个错误条件 duplicate_employee，表示员工信息重复错误；然后定义了一个错误处理器，发生该错误时回滚数据修改并且输出一个消息，使用命名的错误条件可以提高代码的可读性。执行该存储过程的结果如下：

CALL TestError();

Message              |
---------------------|
重复的员工信息！emp_id:1|

如果将错误处理器执行的语句稍加修改，就可以实现第 26 篇中的合并数据操作：

DECLARE EXIT HANDLER
  FOR duplicate_employee
  BEGIN
	UPDATE employee
	SET emp_name = '刘备'
	...
	WHERE emp_id = empid;
  END;

📝如果定义了多个错误处理器，指定错误码的处理器优先级最高，然后是 SQLSTATE 处理器，然后是 SQLEXCEPTION 处理器，最后是 SQLWARNING 处理器以及 NOT FOUND 处理器。多个优先级相同的错误处理器的执行顺序不确定。

32.4.3 获取诊断信息

MySQL 在发生错误时会生成诊断信息，应用程序可以通过GET DIAGNOSTICS语句获取这些信息。例如：

mysql> DROP TABLE no_such_table;
ERROR 1051 (42S02): Unknown table 'hrdb.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1
    ->     @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1   | @p2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'hrdb.no_such_table' |
+-------+------------------------------------+
1 row in set (0.00 sec)

另外，我们也可以使用 SHOW WARNINGS 或者 SHOW ERRORS 查看相关的错误信息。

GET DIAGNOSTICS 语句的完整语法如下：

GET [CURRENT | STACKED] DIAGNOSTICS {
    target = NUMBER | target = ROW_COUNT,
    ...
} 

GET [CURRENT | STACKED] DIAGNOSTICS 
CONDITION condition_number {
    target = condition_information_item_name,
    ...
} 

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | RETURNED_SQLSTATE
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

其中，CURRENT 表示返回当前诊断区域中的信息，STACKED 表示返回第二诊断区域的信息（只能用于 DECLARE HANDLER 语句之中）。默认返回当前诊断区域中的信息。

简单来说，诊断区域中存储了两类信息：

语句信息，包括错误条件的编号（NUMBER）和影响的行数（ROW_COUNT），对应上面的第一种语法。
条件信息，例如错误码（MYSQL_ERRNO）和错误信息（MESSAGE_TEXT）等，对应上面的第二种语法。如果一个语句触发了多个错误条件，每个错误条件都提供了一个条件区域，编号从 1 到 NUMBER。

📝关于 MySQL 诊断区域的详细介绍，可以参考官方文档。

以下是一个在存储过程中获取诊断信息的示例：

CREATE TABLE t(id int NOT NULL);

DELIMITER $$

CREATE PROCEDURE do_insert(value INT)
BEGIN
  -- 声明保存诊断信息的变量
  DECLARE cno INT;
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows INT;
  DECLARE result TEXT;
  -- 声明异常错误处理器
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
	  GET DIAGNOSTICS cno = NUMBER;
      GET DIAGNOSTICS CONDITION cno
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

  -- 执行插入语句
  INSERT INTO t(id) VALUES(value);
  -- 检查插入是否成功
  IF code = '00000' THEN
    GET DIAGNOSTICS nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',nrows);
  ELSE
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  -- 返回错误信息
  SELECT result;
END$$

DELIMITER ;

其中，cno 是返回的错误条件编号，code 和 msg 是错误编码和信息，nrows 是成功插入的行数。运行以下测试语句：

CALL do_insert(1);
result                         |
-------------------------------|
insert succeeded, row count = 1|

CALL do_insert(NULL);
result                                                            |
------------------------------------------------------------------|
insert failed, error = 23000, message = Column 'id' cannot be null|

32.4.4 抛出错误条件

除了捕获和处理错误之外，MySQL 还提供了抛出错误的SIGNAL和RESIGNAL语句。

SIGNAL { SQLSTATE [VALUE] sqlstate_value | condition_name }
    [SET condition_information_item_name = simple_value, ...]

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

SIGNAL 可以抛出一个 SQLSTATE 值或者命名的错误条件，SET 子句可以设置不同的信息，它们可以在调用程序中使用 GET DIAGNOSTICS 语句捕获。例如：

DELIMITER $$

CREATE PROCEDURE p(divisor INT)
BEGIN
  IF divisor = 0 THEN
    SIGNAL SQLSTATE '22012'
    SET MESSAGE_TEXT = 'devided by zero!';
  END IF;
END;

DELIMITER ;

存储过程 p 抛出了一个自定义的错误条件，指定了 SQLSTATE 值和错误消息。传入参数 0，测试抛出错误的结果：

CALL p(0);
ERROR 1644 (22012): devided by zero!

另外，RESIGNAL语句也可以抛出错误，语法和 SIGNAL 相同。不过，RESIGNAL 和 SIGNAL 存在一些差异：

RESIGNAL 语句只能用于错误处理器的处理语句中，可以修改某些错误信息后再次抛出；
RESIGNAL 语句的属性可以忽略，意味着将接收到的错误不经修改再次抛出。

32.5 游标的使用

游标（Cursor）可以支持对查询结果的遍历，从而实现数据集的逐行处理。MySQL 存储过程和函数支持游标，它们具有以下特性：

Asensitive 灵敏性，服务器可能直接遍历基础表的数据，也可能复制一份额外的临时数据；
只读性（Read only），不能通过游标修改基础表中的数据；
不可滚动性（Nonscrollable），只能按照查询结果的顺序访问数据，不能反向遍历，也不能跳跃访问数据行。

使用 MySQL 游标的过程如下：

通过DECLARE语句声明游标；
使用OPEN语句打开游标；
循环使用FETCH语句获取游标中的数据行；
使用CLOSE语句关闭游标并释放资源。

以下是一个使用游标的示例：

DELIMITER $$

CREATE PROCEDURE TestCurosr()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id int;
  DECLARE name varchar(50);
  DECLARE namelist varchar(500) default '';
  DECLARE cur1 CURSOR FOR 
    SELECT emp_id, emp_name FROM employee WHERE sex = '女';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  getEmp: LOOP
    FETCH cur1 INTO id, name;
    IF done THEN
      LEAVE getEmp;
    ELSE
      SET namelist = concat( name, ';', namelist);
    END IF;
  END LOOP;

  CLOSE cur1;
 
  SELECT namelist;
END$$

DELIMITER ;

该存储过程的执行结果如下：

CALL TestCurosr();
namelist          |
------------------|
赵氏;孙丫鬟;孙尚香;|

下面我们基于该示例介绍使用游标过程中的语句。

32.5.1 声明游标

MySQL 使用 DECLARE 语句声明游标：

DECLARE cursor_name CURSOR FOR select_statement;

其中，cursor_name 是游标名称，select_statement 定义了与游标关联的查询语句。游标声明必须在变量和错误条件（CONDITION）声明之后，以及错误处理器（HANDLER）声明之前。在上面的示例中声明了一个游标 cur1：

 DECLARE cur1 CURSOR FOR 
    SELECT emp_id, emp_name FROM employee WHERE sex = '女';

32.5.2 打开游标

声明之后，使用 OPEN 语句打开游标，也就是执行查询初始化结果集：

OPEN cur1;

32.5.3 遍历游标

接下来就是循环使用 FETCH 语句获取下一行数据，并且移动游标指针：

FETCH cur1 INTO id, name;

同时，在循环中检查是否还有更多数据行；如果没有则退出循环。变量 done 在预定义的错误处理器中进行设置，没有找到数据（NOT FOUND）时设置为 true。

32.5.4 关闭游标

最后，使用 CLOSE 语句关闭游标：

CLOSE cur1;

关闭游标可以释放相关的资源。

32.6 访问权限控制

在存储过程和函数的定义中，可以使用 SQL SECURITY 属性指定定义者权限（DEFINER ）或者调用者权限（INVOKER ）。

对于 SQL SECURITY DEFINER，存储过程使用定义者的权限执行，定义者可以通过 DEFINER 属性进行指定，默认为当前用户。

对于 SQL SECURITY INVOKER，存储过程将会使用调用者的权限执行，此时 DEFINER 属性不会对执行产生任何作用。如果调用者的权限较低，定义者的权限较高，调用者仍然能够获得存储过程内部操作所需的权限。

第 32 篇 存储过程（二）

第 32 篇存储过程（二）