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

一时失言乱红尘 2023-02-18 14:09 11阅读 0赞

我们如果要在MySQL的存储过程中遍历一个查询语句的结果集，需要使用到游标cursor（SQL server中可以定义表类型的变量Table，但MySQL中不行，只能用游标）。  
![在这里插入图片描述][20200618095734844.jpeg]

假设我需要从 tb\_stu 这张表中查询出所有记录插入到tb\_stu\_copy1中，等价于`insert into tb_stu_copy1 select * from tb_stu;`

以下是存储过程的具体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

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

1.  定义游标变量 `DECLARE 游标变量名 CURSOR FOR 查询语句`
2.  定义一个当游标移动到末尾时执行的操作 `DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 具体操作代码`
3.  打开游标 `OPEN 游标变量名`
4.  用 `FETCH` 关键字存查询结果集中获取数据
5.  循环遍历（注意MySQL存储过程中不能用for循环）
6.  关闭游标 `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][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** 错误。

[20200618095734844.jpeg]: https://img-blog.csdnimg.cn/20200618095734844.jpeg
[https_blog.csdn.net_cangyingaoyou_article_details_7402243]: https://blog.csdn.net/cangyingaoyou/article/details/7402243