MySQL的存储过程中使用游标来接收查询结果集 - 《MySQL总结》

我们如果要在MySQL的存储过程中遍历一个查询语句的结果集，需要使用到游标cursor（SQL server中可以定义表类型的变量Table，但MySQL中不行）。
假设我需要从 tb_stu 这张表中查询出所有记录插入到tb_stu_copy1中，等价于insert into tb_stu_copy1 select * from tb_stu;
1592213728(1).jpg
以下是存储过程的具体sql

CREATE PROCEDURE curdemo () 
BEGIN
    DECLARE stop_flag INT DEFAULT 0;
    DECLARE this_id INT;
    DECLARE this_name VARCHAR ( 100 );
    DECLARE this_sex VARCHAR ( 2 );
    DECLARE    cu CURSOR FOR SELECT * FROM    tb_stu;
    DECLARE    CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag = 1;#当fetch游标到了结果即最后一行的时候，设置stop_flag=1
    OPEN cu;#定义完游标之后要打开游标
    FETCH cu INTO this_id, this_name, this_sex;#读取数据到游标
    WHILE
            stop_flag <> 1 DO
            INSERT INTO tb_stu_copy1
        VALUES
            ( this_id, this_name, this_sex );
        FETCH cu INTO this_id, this_name, this_sex;
    END WHILE;
    CLOSE cu;#关闭游标
END

我们使用游标的公式步骤大概如下：

定义游标变量 DECLARE 游标变量名 CURSOR FOR 查询语句
定义一个当游标移动到末尾时执行的操作 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 具体操作代码
打开游标 OPEN 游标变量名
用 FETCH 关键字存查询结果集中获取数据
循环遍历（注意MySQL存储过程中不能用for循环）
关闭游标 CLOSE 游标变量名

这里有一段代码值得注意

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag = 1;

这句话是声明了一个句柄，当 sqlstate 的值为’02000’ 时会执行这个句柄，从而使stop_flag设置为1。而 sqlstate '02000' 是什么意思呢？sqlstate正常状态是返回’00000’的，当发生下述异常之一时，sqlstate就会变成02000

SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。

上述的第三种异常是符合我们的情况的。也就是说，将在遍历完所有结果集之后就会调用sqlstate变成02000。

关于sqlstate更多的状态码，可以查看这篇博客：https://blog.csdn.net/cangyingaoyou/article/details/7402243

这里边有一个关键字 continue ，这个关键字和 exit 关键字相对应的。 continue 语句执行完指定操作后继续循环，而 exit 语句会从最近的 begin...end 语句块中退出。这里指定的操作就是 SET stop_flag = 1 。我们给 stop_flag 设置为1之后continue就会继续循环，然后循环条件判断发现stop_flag <>1 不满足，于是while循环就结束了

注意：句柄要定义在游标之后，不然会报 1338 - Cursor declaration after handler declaration 错误。