☆【嚣张恒少】mysql游标内嵌套游标

一、数据背景：
create table z1(id int,name varchar(10));
insert into z1 values(1,‘a’),(2,‘b’),(3,‘c’);

create table z2(id int,c1 varchar(10));
insert into z2 values(9,‘rrr’),(7,‘yyy’);

mysql> select * from z1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | a |
| 2 | b |
| 3 | c |
±-----±-----+
3 rows in set (0.00 sec)

mysql> select * from z2;
±-----±-----+
| id | c1 |
±-----±-----+
| 9 | rrr |
| 7 | yyy |
±-----±-----+
2 rows in set (0.00 sec)

二、sql主体：
################################################
delimiter $$
drop procedure if exists zh;
CREATE Procedure zh()
begin

declare v1 varchar(10);
declare v2 varchar(10);

– 外部游标
DECLARE stop_flag1 INT DEFAULT 1;
DECLARE update_cur1 CURSOR FOR select id from z1;
DECLARE continue handler FOR sqlstate ‘02000’ set stop_flag1=0;

open update_cur1;
REPEAT
FETCH update_cur1
INTO v1;
if stop_flag1=1 then
select v1;

– 内部游标
begin
DECLARE stop_flag2 INT DEFAULT 1;
DECLARE update_cur2 CURSOR FOR select c1 from z2;
DECLARE continue handler FOR sqlstate ‘02000’ set stop_flag2=0;
open update_cur2;
REPEAT
FETCH update_cur2
INTO v2;
if stop_flag2=1 then
select v2;
end if;
until stop_flag2=0
end REPEAT;
close update_cur2;
end;
– 内部游标结束

end if;
until stop_flag1=0
end REPEAT;
close update_cur1;
end $$
delimiter ;

三、使用效果：
mysql> call zh();
±-----+
| v1 |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)

±-----+
| v2 |
±-----+
| rrr |
±-----+
1 row in set (0.01 sec)

±-----+
| v2 |
±-----+
| yyy |
±-----+
1 row in set (0.01 sec)

±-----+
| v1 |
±-----+
| 2 |
±-----+
1 row in set (0.01 sec)

±-----+
| v2 |
±-----+
| rrr |
±-----+
1 row in set (0.01 sec)

±-----+
| v2 |
±-----+
| yyy |
±-----+
1 row in set (0.02 sec)

±-----+
| v1 |
±-----+
| 3 |
±-----+
1 row in set (0.02 sec)

±-----+
| v2 |
±-----+
| rrr |
±-----+
1 row in set (0.03 sec)

±-----+
| v2 |
±-----+
| yyy |
±-----+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)