有两个表一个入库
create table Rk
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
constraint un_uq unique (Pn),
Gz varchar(20),
Dw varchar(10),
Sl bigint,
Jz decimal(10,2),
Tm varchar(30),
Rt datetime,
Qx datetime,
Cj varchar (50),
Bz varchar(100))
和一个出库清单
create table CkQD
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))
将出库清单清空,将Rk表相对应的物品数量(Sl)减少CkQD表对应的物品数量(Sl),如果CkQD的物品数量字段(Sl)大于Rk表的话,就将跳过该行执行并且不清空该行数据
create table Rk
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
constraint un_uq unique (Pn),
Gz varchar(20),
Dw varchar(10),
Sl bigint,
Jz decimal(10,2),
Tm varchar(30),
Rt datetime,
Qx datetime,
Cj varchar (50),
Bz varchar(100))
和一个出库清单
create table CkQD
(Id int auto_increment not null,
constraint Id_pr primary key(Id),
Pn varchar(30),
Gz varchar(20),
Sl bigint,
Jz decimal(10,2),
Dw varchar(10),
Ckt datetime,
Jlr varchar(20),
chr varchar(20),
Bz varchar(100))
将出库清单清空,将Rk表相对应的物品数量(Sl)减少CkQD表对应的物品数量(Sl),如果CkQD的物品数量字段(Sl)大于Rk表的话,就将跳过该行执行并且不清空该行数据
BEGINDECLARE 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;
begin
if (stopFlag is null) then
select Sl into v_Rk_Sl from Rk where Pn = v_CkQD_Pn;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn; --从ckqd表中删除符合条件的记录
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn; --更新Rk表
end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END;
然后有问题可以把自己的代码及错误信息一同贴出,然后大家可以给你提供意见。
create procedure proc_rk()
begin
--判断有没有符合条件的记录
if exists (select 1 from rk,ckqd where rk.Pn = ckqd.Pn and rk.S1>=ckqd.S1) then
drop table if exists temp_rk;
--创建临时表
create table temp_rk (select rk.Pn,rk.S1-ckqd.S1 as S1 from rk,ckqd where rk.Pn = ckqd.Pn and rk.S1>=ckqd.S1);
--从ckqd中删除记录
delete from ckqd where ckqd.Pn in (select temp_rk.Pn from temp_rk);
--更新rk表
update rk inner join temp_rk on rk.Pn = temp_rk.Pn set rk.S1 = temp_rk.S1;
drop table if exists temp_rk;
end if;
end;//--测试结果
mysql> select Pn,S1 from rk//
+------+------+
| Pn | S1 |
+------+------+
| t1 | 60 |
| t2 | 20 |
| t3 | 20 |
+------+------+
3 rows in set (0.00 sec)mysql> select Pn,S1 from ckqd;
-> //
+------+------+
| Pn | S1 |
+------+------+
| t1 | 40 |
| t2 | 40 |
| t3 | 40 |
+------+------+
3 rows in set (0.00 sec)mysql> call proc_rk()//
Query OK, 0 rows affected (0.14 sec)mysql> select Pn,S1 from rk//
+------+------+
| Pn | S1 |
+------+------+
| t1 | 20 |
| t2 | 20 |
| t3 | 20 |
+------+------+
3 rows in set (0.00 sec)mysql> select Pn,S1 from ckqd//
+------+------+
| Pn | S1 |
+------+------+
| t2 | 40 |
| t3 | 40 |
+------+------+
2 rows in set (0.00 sec)
if exists (select 1 from rk,ckqd where rk.Pn = ckqd.Pn and rk.S1>=ckqd.S1) then
drop table if exists temp_rk;
这里面的select 1 里的1指的什么
另外问一下,怎么利用updateRkSl()内的前向游标,将符合条件的数据行插入到新的表ck内去,Ck表与ckQd结构完全一致,太感谢了
updateRkSl()是逐条查找和删除的,你可以参照下面的创建临时表的语句,把创建临时表的语句改成insert into ck (select * from ckqd inner join rk on ckqd.Pn = rk.Pn and ckqd.S1 <= rk.S1)
这个语句没测试你可以试试
insert into ck (select ckqd .* from ckqd inner join rk on ckqd.Pn = rk.Pn and ckqd.S1 <= rk.S1)
测试没问题