BEGIN DECLARE v_CkQD_Pn varchar(30);
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
insert into ck(select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl and ckqd.ph=rk.ph);
OPEN cursor_ckqd;
REPEAT FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl;
begin
if (stopFlag is null) then
select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl and ckqd.ph=rk.ph;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn;
end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END表结构如下
入库表(Rk)
| Id | Pn | Gz | Lb | Dw | Sl | Jj | Lj | Ph | Rt | Qx | Cj | Bz
出库清单(Ckqd)
| Id | Pn | Gz | Dw | Lb | Sl | Jj | Lj | Ph | Ckf | Ckt | Jlr | chr | Cj | Bz |
出库表和清单结构一样,只要出库清单的物品只要是RK的PH(批号)相同的,就插入到CK表再删除CKQD表中的行,并在RK表中减去出库商品的数量(SL)
现在是过程执行插入到Ck表后,就不动了以后的RK表的数量也没有减,Ckqd也没有删
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
insert into ck(select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl and ckqd.ph=rk.ph);
OPEN cursor_ckqd;
REPEAT FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl;
begin
if (stopFlag is null) then
select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl and ckqd.ph=rk.ph;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn;
end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END表结构如下
入库表(Rk)
| Id | Pn | Gz | Lb | Dw | Sl | Jj | Lj | Ph | Rt | Qx | Cj | Bz
出库清单(Ckqd)
| Id | Pn | Gz | Dw | Lb | Sl | Jj | Lj | Ph | Ckf | Ckt | Jlr | chr | Cj | Bz |
出库表和清单结构一样,只要出库清单的物品只要是RK的PH(批号)相同的,就插入到CK表再删除CKQD表中的行,并在RK表中减去出库商品的数量(SL)
现在是过程执行插入到Ck表后,就不动了以后的RK表的数量也没有减,Ckqd也没有删
解决方案 »
- MySQL还有希望吗?
- 100分求imageMagick与im4java急急急!!!!
- mysql中如何筛选出含有某个字符的数据并且替换?
- CMS系统的文章(content)存储的问题(数据库OR文件系统,这是个问题)
- 请教关于MYSQL中索引的比较形象的解释,谢谢。
- 一个MySQL的数据库的问题
- 怎么解决安装中的错误?在线等……
- 谁有时间,麻烦详细介绍一下innodb.
- 如何在Mysql中书写select 以获得当前行的行号
- mysql查询6号到24号数据,就是用户在6号到24号中,有5天出现过就查询出来。
- 【修改datetime字段值报错】如果把datetime字段修改成大于2039-01-08则报错,小于它则OK,请各位大师帮忙分析一下,谢谢!
- mysql查询慢
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn;
均会导致
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;利用中间变量保存一下 stopFlag 的状态。
set stopFlag=0;
UNTIL stopFlag = 1 END REPEAT;
http://bbs.csdn.net/topics/390339534
DECLARE v_CkQD_Pn varchar(30);
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN cursor_ckqd;
REPEAT
FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl; select Sl into v_Rk_Sl from Rk where Ph=v_CkQD_Pn and sl>=v_CkQD_Sl;
if v_Rk_Sl>0 then
insert into Ck select * from CkQD where Ph=v_CkQD_Pn;
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = Sl - v_CkQD_Sl where pn = v_CkQD_Pn;
end if; UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END