MySQL8 嵌套游标循环遍历
1. 游标的特点
官网介绍:
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
- Asensitive: The server may or may not make a copy of its result table
- Read only: Not updatable
- Nonscrollable: Can be traversed only in one direction and cannot skip
rows
Cursor declarations must appear before handler declarations and after variable and condition declarations.
特性:
不敏感:数据库服务器可以,或不可 复制其结果表 ,如何理解,疑惑??
只读:不能更新数据
不可滚动:游标只能单方向访问,不能跳行
定义的时候要注意:
游标声明必须在其他变量之后,handler 的声明在游标定义之后。
2. 嵌套游标遍历
重点是对于handler控制的标志变量进行赋值,保证嵌套的游标能够正常遍历完成。关键赋值:
#cur_stud2完成后,v_done=0 ,重置游标标志
set v_done = 1;
程序如下
CREATE DEFINER=`root`@`%` PROCEDURE `pro_loop_cursor`()
begin
declare v_done int default 1 ;
declare v_name varchar(10);
declare v_grade int ;
declare v_string text;
declare v_counter int default 0;
declare cur_stud1 cursor for select t.name ,t.grade from tb_student t where t.grade >= 70 and t.grade < 80 order by t.grade desc limit 3;
declare cur_stud2 cursor for select t.name ,t.grade from tb_student t where t.grade >= 90 and t.grade < 100 order by t.grade desc limit 2;
#游标结标志,必须在游标声明后声明!!!否则报错
declare continue handler for not found set v_done = 0;
set v_string = '';
#外循环,打开游标
open cur_stud1 ;
cur_loop1:loop
#内循环,打开游标
open cur_stud2 ;
fetch next from cur_stud1 into v_name ,v_grade;
#外循环,游标标志判断
if v_done = 0 then
leave cur_loop1;
end if;
# 外循环游标数据遍历
set v_string = concat(v_string,' stud1:',v_name , ' :',v_grade);
# 内循环开始
cur_loop2: loop
fetch next from cur_stud2 into v_name ,v_grade;
#内循环,游标标志判断
if v_done = 0 then
leave cur_loop2;
end if;
# 内循环游标数据遍历
set v_string = concat(v_string,' stud2:',v_name , ' :',v_grade);
end loop cur_loop2; # 内循环结束
#关闭内循环游标
close cur_stud2 ;
#计数器 +1
set v_counter = v_counter + 1;
# cur_stud2完成后,v_done=0 ,重置游标标志
set v_done = 1;
end loop cur_loop1; # 外循环结束
#关闭外循环游标
close cur_stud1 ;
select v_counter ;
select v_string;
end
执行结果:
外循环记录是3条,70-80分的前三名,内循环记录是2条,90-100分的前两名,需要循环3次。
v_counter 是3 。
每个stud1对应两条stud2记录。
mysql> call pro_loop_cursor();
+-----------+
| v_counter |
+-----------+
| 3 |
+-----------+
1 row in set (0.01 sec)
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v_string |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stud1:CJXBCEXCOF :79 stud2:WKJVXXFKGY :99 stud2:PYRESDIMBG :99 stud1:FIDLSJAYFS :79 stud2:WKJVXXFKGY :99 stud2:PYRESDIMBG :99 stud1:KEVQMOCIEW :78 stud2:WKJVXXFKGY :99 stud2:PYRESDIMBG :99 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)