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

我们如果要在MySQL的存储过程中遍历一个查询语句的结果集，需要使用到游标cursor（SQL server中可以定义表类型的变量Table，但MySQL中不行，只能用游标）。

假设我需要从 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

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

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