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循环也可以,需要知道怎么获得游标的行数。或者还有好的方法解决双层游标循环的办法。谢谢!
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循环也可以,需要知道怎么获得游标的行数。或者还有好的方法解决双层游标循环的办法。谢谢!
解决方案 »
- mysql不能正确读取decimal,求助!!!
- 请问一句 mysql语句
- 求一优化SQL
- 请问各位mysql可以装数据量最大的类型是什么???
- 还是mysql数据获取乱码的问题,tomcat环境下使用
- 谁能给我下载 MySQL 的链接``急急急```
- 请问有谁知道哪有关于MYSQL的教学录像下载?
- 高手指教:下面这条特别麻烦的SQL SERVER内的子从语句如何转换成MYSQL语句?
- MySQL的查询结果怎么保留阿?在线等~~~~~~~~~~~~~~~``````
- mysql 中执行sql命令
- 各位兄弟,急,急,急,mysql connector6.0.4版本 安装 回滚
- MySQL建表出错 求解
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;
set done=0;
until done end repeat;
end;