-- 创建存储过程
delimiter //
create procedure company_attachment_cursor(in _type int)
begin
-- 申明局部变量 declare
declare _company_id varchar(25);
declare targetId varchar(25);
declare num int;
DECLARE done int DEFAULT FALSE;
-- 申明游标
declare cur1 CURSOR FOR select distinct company_id from company_attachment where type=_type;
-- 第二个游标需使用第一个循环的变量_company_id，故将变量申明在最外层
declare cur2 CURSOR FOR select id from company_attachment where company_id=_company_id and type=_type;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
read_loop: LOOP
fetch cur1 into _company_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 每次循环开始时重新对num进行赋值
set num = 0;
-- 开启第二个游标
open cur2;
inner_loop: LOOP
fetch cur2 into targetId;
-- 打印过程
-- select num;
IF done THEN
LEAVE inner_loop;
END IF;
update company_attachment set number=num where id=targetId;
set num=num+1;
END LOOP;
close cur2;
-- 第一个循环结束时done=1
-- 故需手动设置done=0,否则外层循环仅会执行一次
SET done = 0;
END LOOP;
close cur1;
delimiter //
create procedure company_attachment_cursor(in _type int)
begin
-- 申明局部变量 declare
declare _company_id varchar(25);
declare targetId varchar(25);
declare num int;
DECLARE done int DEFAULT FALSE;
-- 申明游标
declare cur1 CURSOR FOR select distinct company_id from company_attachment where type=_type;
-- 第二个游标需使用第一个循环的变量_company_id，故将变量申明在最外层
declare cur2 CURSOR FOR select id from company_attachment where company_id=_company_id and type=_type;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
read_loop: LOOP
fetch cur1 into _company_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 每次循环开始时重新对num进行赋值
set num = 0;
-- 开启第二个游标
open cur2;
inner_loop: LOOP
fetch cur2 into targetId;
-- 打印过程
-- select num;
IF done THEN
LEAVE inner_loop;
END IF;
update company_attachment set number=num where id=targetId;
set num=num+1;
END LOOP;
close cur2;
-- 第一个循环结束时done=1
-- 故需手动设置done=0,否则外层循环仅会执行一次
SET done = 0;
END LOOP;
close cur1;
end; //
本文转自 Lee_吉 博客，原文链接： http://blog.51cto.com/12173069/2050909 如需转载请自行联系原作者
