概述

本章，您将了解 MySQL 中的游标。

游标（也称光标）：一种用于在存储过程、存储函数或触发器中逐行处理查询结果集的临时数据库对象，其类似编程语言当中的指针，都是用来定位和遍历数据。

不同的 RDBMS 中，游标的语法以及使用位置都略有不同，在 MySQL 中，游标只能存在于用于存储过程、存储函数以及触发器的 begin...end 代码块中。

在实际生产环境下的使用建议 —— 由于滥用游标会影响系统性能且游标难以调试，因此在生产环境下不建议大规模使用游标。

游标的使用

要使用游标，需要有下面四个步骤：

1. 声明游标（类似指针声明）
2. 打开游标（类似内存分配）
3. 使用游标（类似指针解引用）
4. 关闭游标（类似内存释放）

声明游标

在前面的文章中提到，若要声明局部变量，需要使用 declare 关键字，接着使用 set 关键字对局部变量进行赋值，比如：

...
begin
    ...
    declare my_variables int default 10;
    set my_variables:=100;
    ...
end
... 

若要声明游标，同样也是使用 declare 关键字，基本语法为：

declare 游标名 cursor for 查询语句;

需要注意的是，如果 begin...end 代码块中有局部变量或用户变量，则游标的声明需要在它们的后面。

打开游标

语法为：

open 游标名;

本质上，就是将 select 的结果集送到游标的工作区。

使用游标

语法为：

fetch 游标名 into var_name1,var_name2… 

变量名必须已经在 begin...end 代码块中声明，且变量数量需要对应 select 查询列表的个数。

关闭游标

语法为：

close 游标名;

由于游标是一种临时性的数据库对象，因此不存在删除游标的概念，当所属的存储过程、存储函数、触发器执行结束后（无论是正常执行还是异常终止），MySQL 都会自动释放所有关联的游标资源，换言之，若忘记使用 close 关闭游标，MySQL 也会在存储过程、存储函数、触发器执行结束后自动回收相应的游标资源。

当需要重新使用对应的游标时，使用 open 关键字打开即可。

游标作用域规则

游标仅在声明它的 begin...end 代码块中有效，超出作用域后即使存储过程未结束也无法访问，比如：

create procedure demo()
begin
    # 外层代码块
    declare cur1 cursor for select...;
    ...
    begin
        # 内层代码块
        declare cur2 cursor for select...;
        # 这里可以访问cur1和cur2
    end;
    ...
    # 这里只能访问cur1，cur2已失效
end

对于这种嵌套的游标，内层游标会先于外层游标被回收。

游标示例

delimiter //

CREATE PROCEDURE curdemo()
BEGIN
  # 声明 3 个局部变量
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;

  # 声明 2 个游标
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  # DECLARE ... HANDLER 语法，后面介绍
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  # 开启 2 个游标
  OPEN cur1;
  OPEN cur2;

  # LOOP 循环，read_loop 是 LOOP 循环的标签名称
  read_loop: LOOP
    # 使用第一个游标，a 和 b 两个局部变量已经在 begin...end 代码块中声明
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;

    # IF 结构进行逻辑判断
    IF done THEN LEAVE read_loop;
    # 结束 IF 结构
    END IF;

    IF b < c THEN INSERT INTO test.t3 VALUES (a,b);
    ELSE INSERT INTO test.t3 VALUES (a,c);
    END IF;

  # 结束 LOOP 循环
  END LOOP;

  # 关闭第一个游标
  CLOSE cur1;
  # 关闭第二个游标
  CLOSE cur2;

END //

delimiter ;

解释说明：

• 游标的声明必须在局部变量或用户变量的后面
• int 本身是整数数据类型，其并不包括布尔值。DECLARE done INT DEFAULT FALSE; 会自动将布尔值的 TRUE 或 FLASE 转换为 1 或 0
• 在 begin...end 代码块中键入 DECLARE done INT DEFAULT FALSE; 和 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 是游标的标准写法。
• cur1 游标声明时由于有两个查询列表字段（test.t1 表中的 id 和 data 字段），因此在使用 cur1 游标时需要有两个对应的局部变量
• cur2 游标声明时由于有一个查询列表字段（test.t2 表中的 i 字段），因此在使用 cur2 游标时需要有一个对应的局部变量
• 在 LOOP 循环中使用两个游标，每次都从这两个游标中取一行数据并进行处理。通过比较局部变量 b 和 c 的值来决定向 t3 表插入哪些值。
• 异常处理 —— DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 定义了一个异常处理器。当游标遍历完所有数据时（NOT FOUND 状态），将 done 局部变量的值设为 TRUE。在 LOOP 循环中使用 IF 结构对 done 局部变量进行逻辑判断，为真则跳出 LOOP 循环
• 关闭游标在 LOOP 循环结束之后

DECLARE...HANDLER语句用于在存储过程、存储函数或触发器执行过程中捕获并处理特定的错误或警告条件，语法为：

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

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

• CONTINUE 动作 - 继续执行
• NOT FOUND 条件值 - 当遍历完所有数据后或未返回行后进行触发
• SET done = TRUE - 满足条件之后的执行语句

赞赏