mysql多重嵌套循环_MYSQL游标嵌套循环示例

本文将举例演示游标的嵌套用法，首先建造表结构，如下：

drop table if exists `tb_user`;

create table tb_user (

id bigint(22) not null auto_increment primary key,

name varchar(20) not NULL,

age tinyint(3),

gmt_create date,

gmt_modified date

);

drop table if exists `tb_user_param`;

create table tb_user_param (

id bigint(22) not null auto_increment primary key,

user_id bigint(22) not null,

code varchar(100) not null,

value varchar(1000),

gmt_create DATE,

gmt_modified date

);

drop procedure if exists `sp_init_data`;

create procedure `sp_init_data`()

begin

declare v_i bigint(22) default 0;

declare v_uid bigint(22);

declare v_nick varchar(20) default 'lanbo_';

declare v_code_1 varchar(20) default 'address';

declare v_code_2 varchar(20) default 'phone';

declare v_code_3 varchar(20) default 'wangwang_id';

declare v_value_1 varchar(20) default 'HZ.XiHu';

declare v_value_2 varchar(20) default '1875757198';

declare v_value_3 varchar(20) default 'shansun_';

while v_i < 10 do

set v_i = v_i + 1;

insert into tb_user values (null, concat(v_nick, v_i), 23, now(), now());

select LAST_INSERT_ID() into v_uid;

insert into tb_user_param values(null, v_uid, v_code_1, v_value_1, now(), now());

insert into tb_user_param values(null, v_uid, v_code_2, concat(v_value_2, v_i), now(), now());

insert into tb_user_param values(null, v_uid, v_code_3, concat(v_value_3, v_i), now(), now());

end while;

commit;

end;

call sp_init_data();

drop table if exists `tb_key_value`;

create table `tb_key_value`(

uid bigint(22) not null,

k varchar(100),

v varchar(100)

);

我们插入了10条数据到tb_user中，如果tb_user中未定义的字段如address，则放置在tb_user_param中，该表是个key_value的结构，由uid+code定位。

后面我们要做的是，将根据tb_user中的uid找到tb_user_param中相关记录后，将key_value信息转移到tb_key_value表中，为达到演示效果，我们使用嵌套游标操作数据，代码如下：

drop procedure if exists `sp_nested_cursor`;

create procedure `sp_nested_cursor`()

begin

declare v_uid bigint(22);

declare v_code varchar(100);

declare v_value varchar(100);

declare _done TINYINT(1) default 0;

declare cur_user cursor for select id from `tb_user`;

declare continue handler for not found set _done = 1;

open cur_user;

loop_xxx:loop

fetch cur_user into v_uid;

if _done=1 then

leave loop_xxx;

end if;

begin

declare _inner tinyint(1) default 0;

declare cur_param cursor for select code, value

from `tb_user_param`

where user_id=v_uid;

declare continue handler for not found set _inner = 1;

open cur_param;

loop_yyy:loop

fetch cur_param into v_code, v_value;

if _inner=1 then

leave loop_yyy;

end if;

insert into tb_key_value values (v_uid, v_code, v_value);

end loop;

commit;

end;

end loop;

end;

call `sp_nested_cursor`();

如果想跟踪上面程序的执行过程，可以借助MySQL Debugger工具调试学习。