深刻理解MySQL8游标处理中not found
最近使用MySQL的游标，在fetch循环过程中，程序总是提前退出 ，百思不得其解，经过测试，原来是对于游标处理中not found的定义理解有误，默认是视同Oracle的游标not found定义，结果思考测试了两天，终于走出了思维定式。
1. 问题描述
MySQL版本，8.0.16 。
存储过程如下：
CREATE DEFINER=`root`@`%` PROCEDURE `pro_test_nofound_cursor`()
begin
declare v_done int default 1 ;
declare v_name varchar(10);
declare v_date date;
declare v_string text;
declare v_for_nofound varchar(10);
declare v_counter int default 0;
declare cur_stud1 cursor for select t.name ,t.birthday from tb_student t where t.grade >= 70 and t.grade < 80 order by t.grade desc limit 3;
declare continue handler for not found set v_done = 0;
#使用游标前打开游标
open cur_stud1 ;
set v_string = '';
cur_loop: loop
fetch next from cur_stud1 into v_name ,v_date;
set v_counter = v_counter + 1;
if v_done = 0 then
leave cur_loop;
end if;
-- 此查询无结果，是空。
select t.name into v_for_nofound from tb_student t where t.grade >= 101 order by t.grade desc limit 1;
set v_string = concat(v_string,' stud1:',v_name , ' :',v_date);
end loop cur_loop;
close cur_stud1 ;
select v_string;
select v_counter;
end
游标记录是3条记录，但是查询结果，只反馈一条记录值。
游标理解应该循环3次！！！，但是只返回了一条记录。
为什么 ？？？
结果如下：
mysql> call pro_test_nofound_cursor();
+-------------------------------+
| v_string |
+-------------------------------+
| stud1:CJXBCEXCOF :2023-09-18 |
+-------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call pro_test_nofound_cursor();
+-------------------------------+
| v_string |
+-------------------------------+
| stud1:CJXBCEXCOF :2023-09-18 |
+-------------------------------+
1 row in set (0.00 sec)
+-----------+
| v_counter |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
2. 问题分析
MySQL文档：
MySQL定义not found的说明
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with ‘02’. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value ‘02000’. To detect this condition, you can set up a handler for it or for a NOT FOUND condition.
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN
-- body of handler
END;
For another example, see Section 13.6.6, “Cursors”. The NOT FOUND condition also occurs for SELECT … INTO var_list statements that retrieve no rows.
说明：
SQLSTATE value ‘02000’ 和 NOT FOUND 是等价的，那么NOT FOUND 就不是cursor所专属的状态值。因此在循环中，如果出现了查询没有结果的情况，那么将直接 触发v_done = 0 ，并非cursor的fetch 触发的结果。
注意：与Oracle游标访问的notfound状态值是不同的，oracle是专用于cursor，而MySQL是notfound状态是所有SQL共用的！！！
惯性思维，困扰了两天。
3. 问题解决
set v_done = 1;
程序只有在fetch的时候，产生的v_done状态，才能触发退出循环。
修改后的程序如下：
CREATE DEFINER=`root`@`%` PROCEDURE `pro_test_nofound_cursor`()
begin
declare v_done int default 1 ;
declare v_name varchar(10);
declare v_date date;
declare v_string text;
declare v_for_nofound varchar(10);
declare v_counter int default 0;
declare cur_stud1 cursor for select t.name ,t.birthday from tb_student t where t.grade >= 70 and t.grade < 80 order by t.grade desc limit 3;
declare continue handler for not found set v_done = 0;
#使用游标前打开游标
open cur_stud1 ;
set v_string = '';
cur_loop: loop
fetch next from cur_stud1 into v_name ,v_date;
set v_counter = v_counter + 1;
if v_done = 0 then
leave cur_loop;
end if;
-- 此查询无结果，是空。
select t.name into v_for_nofound from tb_student t where t.grade >= 101 order by t.grade desc limit 1;
set v_string = concat(v_string,' stud1:',v_name , ' :',v_date);
set v_done = 1;
end loop cur_loop;
close cur_stud1 ;
select v_string;
select v_counter;
end
执行结果：
mysql> call pro_test_nofound_cursor();
+-----------------------------------------------------------------------------------------+
| v_string |
+-----------------------------------------------------------------------------------------+
| stud1:CJXBCEXCOF :2023-09-18 stud1:FIDLSJAYFS :2023-11-08 stud1:KEVQMOCIEW :2023-09-06 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
+-----------+
| v_counter |
+-----------+
| 4 |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
原文地址:https://blog.csdn.net/qq_39065491/article/details/134592943
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_3484.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
