网站设置不能通过链接访问,网站建设网址,全国建设造价信息网站,用前端做的比较酷的网站深刻理解MySQL8游标处理中not found
最近使用MySQL的游标#xff0c;在fetch循环过程中#xff0c;程序总是提前退出 #xff0c;百思不得其解#xff0c;经过测试#xff0c;原来是对于游标处理中not found的定义理解有误#xff0c;默认是视同Oracle的游标not found定…深刻理解MySQL8游标处理中not found
最近使用MySQL的游标在fetch循环过程中程序总是提前退出 百思不得其解经过测试原来是对于游标处理中not found的定义理解有误默认是视同Oracle的游标not found定义结果思考测试了两天终于走出了思维定式。
1. 问题描述
MySQL版本8.0.16 。
存储过程如下
CREATE DEFINERroot% PROCEDURE pro_test_nofound_cursor()
begindeclare 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: loopfetch 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)
结果说明 记录返回只有1条 计数器是2
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共用的
惯性思维困扰了两天。 declare continue handler for not found set v_done 0; 3. 问题解决
在游标循环中最后增加一行强制设置为1 set v_done 1; 程序只有在fetch的时候产生的v_done状态才能触发退出循环。 修改后的程序如下
CREATE DEFINERroot% PROCEDURE pro_test_nofound_cursor()
begindeclare 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: loopfetch 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)
执行结果正确返回了3条记录计数器值是4 。
