mysql双游标_mysql 双层游标循环问题.

create procedure skubalance()

begin

declare done int default 0;

declare code varchar(20);

declare num int;

declare storid int;

declare sdate date;

declare bid int;

declare bcode varchar(20);

declare bnum int;

declare bstorid int;

declare no2 int default 0;

declare cursor1 cursor for select sku_code,sum(sku_count),stor_id from sku_move where sys_date = '2011-06-17' and type=2 group by sku_code,stor_id;

declare cursor2 cursor for select sku_code,sku_count,stor_id from sku_balance where sys_date = '2011-06-20';

declare continue handler for sqlstate '02000' set done = 1;

select max(BATCH_ID) into bid from batch_parameters;

set bid=bid+1;

select static_value into sdate FROM system_configure where static_key='system_date';

open cursor2;

repeat

fetch cursor2 into bcode,bnum,bstorid;

if not done then

open cursor1;

fetch cursor1 into code,num,storid;

while no2<14 do

begin

if bcode = code && bstorid = storid then

set bnum = bnum+num;

end if;

fetch next from cursor1 into code,num,storid;

end;

set no2 = no2 + 1;

end while;

close cursor1;

insert into sku_balance values(null,bcode,bnum,bstorid,sdate,bid);

end if;

until done end repeat;

end;

我想请问下，在这个proc中，有两个游标，在游标cursor1循环完之后，done的值就变成1了，那么外面的cursor2就不会在循环了，有什么办法能解决这个问题。或者是都用while循环也可以，需要知道怎么获得游标的行数。或者还有好的方法解决双层游标循环的办法。谢谢！

作者: huqingwei0824

发布时间: 2011-07-04