100分请教存储过程问题？

[zhangwei1437]

表 CKDA(仓库部门资料) 字段 ckbh,ckmc
01 中厨
02 面点
表 WLDA（物料资料） 字段 wlbh, wlmc, dj
1000 精肉 5.00
1001 味精 3.20
1002 糖 0.5
表 PF（配方表） 字段 cpbh wlbh yl
10000 1000 0.7
10000 1001 0.01
10000 1002 0.05
20000 1000 1.0
20000 3000 2.0
表 XFJLB(消费记录表) 字段 cpbh sl, cw lsh
10000 1 中厨 2006-1-1-0001
10000 2 中厨 2006-1-1-0001
20000 1 中厨 2006-1-1-0001
30000 1 面点 2006-1-1-0002
表 CKWL（分仓库存表） 字段 ckbh,ckmc,wlbh,wlmc,kcsl ,dj
01 中厨 1000 精肉 0.1 5.00
如果我在知道XFJLB中 lsh 值的情况下，我如何更新和向CKWL表中添加记录
首先更新 比如我知道lsh=2006-1-1-0001 我就要从XFJLB 中查出lsh=2006-1-1-0001 的记录
再根据cpbh 从PF中找wlbh,然后根据wlbh 和XFJLB中的cw来更新 CKWL表 让kcsl=kcsl-pf.yl*xfjlb.sl 这是一种情况
还有就是从CKWL表里原先没有合适的更新记录需要添加记录 ，添加CKDA中的ckbh,ckmc WLDA中的wlbh,wlmc,dj kcsl=-pf.yl*xfjlb.sl
请高手指点
我是这样写的可是，并没有达到要求，居然能添加相同的记录，一个lsh对应一个cpbh可以实现
但是lsh=2006-1-1-0001的相同的cpbh也有就是说一个lsh可能对应相同的cpbh
高手给看看，
问题2：如果存储过程执行的时间非常短，会不会出现并发的情况，第一个没有执行完或者第二个没有执行完的情况？？？？？？？
CREATE PROCEDURE ckjl
@lsh as varchar(20)
AS

IF EXISTS(SELECT 1 FROM PF p, XFJLB x, CKWL c WHERE p.cpbh=x.cpbh AND p.wlbh=c.wlbh AND x.lsh=@lsh)
BEGIN
UPDATE c
SET c.sl=c.sl-p.yl*x.ls_sl
FROM PF p, XFJLB x, CKWL c
WHERE p.cpbh=x.cpbh
AND p.wlbh=c.wlbh
AND x.lsh=@lsh
END
ELSE
BEGIN
INSERT INTO CKWL(ckbh, ckmc, wlbh, wlmc, sl, pjdj)
SELECT k.ckbh, k.ckmc, w.wlbh, w.wlmc, -p.yl*x.ls_sl, w.dj
FROM CKDA k, WLDA w, PF p, XFJLB x
WHERE x.lsh=@lsh
AND x.ls_cw=k.ckmc
AND p.cpbh=x.cpbh
AND p.wlbh=w.wlbh
END
GO

[白发程序猿]

首先你这些表的主键和外键搞清楚了，我看了一下，有点糊涂，搞不懂表之间的关系，需求明白了，就是根据消费记录表来更新分仓库存表，首先分仓库存表的主键搞不明白，这是没办法做的，所以你把表结构说一下

[子陌红尘]

create procedure sp_test(@lsh varchar(20))
as
begin
--更新已经存在的记录
update e
set
kcsl=e.kcsl+f.kcsl
from
CKWL e,
(select
d.ckbh,d.ckmc,c.wlbh,c.wlmc,sum(a.sl*b.yl) kcsl,c.dj
from
XFJLB a,PF b,WLDA c,CKDA d
where
a.cpbh=b.cpbh and b.wlbh=c.wlbh and a.cw=d.ckmc and a.lsh=@lsh
and
exists(select 1 from CKWL where ckbh=d.ckbh and wlbh=c.wlbh)
group by
d.ckbh,d.ckmc,c.wlbh,c.wlmc,c.dj) f
where
e.ckbh=f.ckbh and e.wlbh=f.wlbh

--插入尚不存在的记录
insert into ckwl
select
d.ckbh,d.ckmc,c.wlbh,c.wlmc,sum(a.sl*b.yl),c.dj
from
XFJLB a,PF b,WLDA c,CKDA d
where
a.cpbh=b.cpbh and b.wlbh=c.wlbh and a.cw=d.ckmc and a.lsh=@lsh
and
not exists(select 1 from CKWL where ckbh=d.ckbh and wlbh=c.wlbh)
group by
d.ckbh,d.ckmc,c.wlbh,c.wlmc,c.dj
end
go

[子陌红尘]

--生成测试数据
create table CKDA(ckbh varchar(20),ckmc varchar(20)) --仓库部门资料
insert into ckda select '01','中厨'
insert into ckda select '02','面点'

create table WLDA(wlbh int,wlmc varchar(20),dj numeric(5,2)) --物料资料
insert into wlda select 1000,'精肉',5.00
insert into wlda select 1001,'味精',3.20
insert into wlda select 1002,'糖 ',0.5

create table PF(cpbh int,wlbh int,yl numeric(5,2)) --配方表
insert into PF select 10000,1000,0.7
insert into PF select 10000,1001,0.01
insert into PF select 10000,1002,0.05
insert into PF select 20000,1000,1.0
insert into PF select 20000,3000,2.0

create table XFJLB(cpbh int,sl int,cw varchar(20),lsh varchar(20)) --消费记录表
insert into xfjlb select 10000,1,'中厨','2006-1-1-0001'
insert into xfjlb select 10000,2,'中厨','2006-1-1-0001'
insert into xfjlb select 20000,1,'中厨','2006-1-1-0001'
insert into xfjlb select 30000,1,'面点','2006-1-1-0002'

create table CKWL(ckbh varchar(20),ckmc varchar(20),wlbh int,
wlmc varchar(20),kcsl numeric(5,2),dj numeric(5,2)) --分仓库存表
insert into ckwl select '01','中厨',1000,'精肉',0.1,5.00
go


--创建存储过程
create procedure sp_test(@lsh varchar(20))
as
begin
--更新已经存在的记录
update e
set
kcsl=e.kcsl+f.kcsl
from
CKWL e,
(select
d.ckbh,d.ckmc,c.wlbh,c.wlmc,sum(a.sl*b.yl) kcsl,c.dj
from
XFJLB a,PF b,WLDA c,CKDA d
where
a.cpbh=b.cpbh and b.wlbh=c.wlbh and a.cw=d.ckmc and a.lsh=@lsh
and
exists(select 1 from CKWL where ckbh=d.ckbh and wlbh=c.wlbh)
group by
d.ckbh,d.ckmc,c.wlbh,c.wlmc,c.dj) f
where
e.ckbh=f.ckbh and e.wlbh=f.wlbh

--插入尚不存在的记录
insert into CKWL
select
d.ckbh,d.ckmc,c.wlbh,c.wlmc,sum(a.sl*b.yl),c.dj
from
XFJLB a,PF b,WLDA c,CKDA d
where
a.cpbh=b.cpbh and b.wlbh=c.wlbh and a.cw=d.ckmc and a.lsh=@lsh
and
not exists(select 1 from CKWL where ckbh=d.ckbh and wlbh=c.wlbh)
group by
d.ckbh,d.ckmc,c.wlbh,c.wlmc,c.dj
end
go


--调用存储过程，查看执行结果
exec sp_test '2006-1-1-0001'
select * from CKWL

--输出结果
/*
ckbh ckmc wlbh wlmc kcsl dj
------ ------ ------ ------ ------ ------
01 中厨 1000 精肉 3.20 5.00
01 中厨 1001 味精 0.03 3.20
01 中厨 1002 糖 0.15 0.50
*/


--清除测试环境
drop procedure sp_test
drop table CKWL,XFJLB,PF,WLDA,CKDA

[-狙击手-]

CREATE PROCEDURE ckjl
@lsh as varchar(20)
AS

IF EXISTS(SELECT 1 FROM PF p, XFJLB x, CKWL c WHERE p.cpbh=x.cpbh AND p.wlbh=c.wlbh AND x.lsh=@lsh)
BEGIN
UPDATE c
SET c.sl=c.sl- cc.zw
from CKDA k, WLDA w,
(select wlbh,sum(yl*sl) as zl,cw from(
select p.wlbh,p.yl,x.sl,x.cw from PF p,(select lsh,cpbh,sum(sl) as sl ,cw from XFJLB
group by cpbh,cw,lsh) x where x.lsh=@lsh and p.cpbh=x.cpbh) bb group by wlbh,cw) cc
where cc.wlbh = w.wlbh and k.ckmc = cc.cw

END
ELSE
BEGIN
INSERT INTO CKWL(ckbh, ckmc, wlbh, wlmc, sl, pjdj)
select k.ckbh, k.ckmc,cc.wlbh,w.wlmc ,-zl as zl,cc.cw from CKDA k, WLDA w,
(select wlbh,sum(yl*sl) as zl,cw from(
select p.wlbh,p.yl,x.sl,x.cw from PF p,(select lsh,cpbh,sum(sl) as sl ,cw from XFJLB
group by cpbh,cw,lsh) x where x.lsh=@lsh and p.cpbh=x.cpbh) bb group by wlbh,cw) cc
where cc.wlbh = w.wlbh and k.ckmc = cc.cw
END
GO

[zhangwei1437]

正在测试中

[-狙击手-]

create table CKDA(ckbh char(2),ckmc varchar(10))
go
insert into ckda select '01','中厨'
insert into ckda select '02','面点'
create table WLDA(wlbh char(4),wlmc char(6),dj real)
go
insert into wlda select '1000','精肉',5.00
insert into wlda select '1001','味精',3.20
insert into wlda select '1002','糖',0.5
insert into wlda select '3000','糖1',0.5
create table PF(cpbh char(5),wlbh char(4),yl real)
go
insert into pf select '10000','1000',0.7
insert into pf select '10000','1001',0.01
insert into pf select '10000','1002',0.05
insert into pf select '20000','1000',1.0
insert into pf select '20000','3000',2.0
create table XFJLB(cpbh char(5),sl int,cw char(6),lsh varchar(16))
go
insert into xfjlb select '10000',1,'中厨','2006-1-1-0001'
insert into xfjlb select '10000',2,'中厨','2006-1-1-0001'
insert into xfjlb select '20000',1,'中厨','2006-1-1-0001'
insert into xfjlb select '30000',1,'面点','2006-1-1-0002'

create table CKWL(ckbh varchar(20),ckmc varchar(20),wlbh int,
wlmc varchar(20),kcsl numeric(5,2),dj numeric(5,2))
go
insert into ckwl select '01','中厨',1000,'精肉',0.1,5.00


exec ckjl '2006-1-1-0001'
select * from CKWL


drop table CKDA
drop table WLDA
drop table PF
drop table xfjlb
drop table CKWL

CREATE PROCEDURE ckjl
@lsh as varchar(20)
AS

IF EXISTS(SELECT 1 FROM PF p, XFJLB x, CKWL c WHERE p.cpbh=x.cpbh AND p.wlbh=c.wlbh AND x.lsh=@lsh)
BEGIN
UPDATE c
SET c.kcsl=c.kcsl- cc.zl
from CKWL c, CKDA k, WLDA w,
(select wlbh,sum(yl*sl) as zl,cw from(
select p.wlbh,p.yl,x.sl,x.cw from PF p,(select lsh,cpbh,sum(sl) as sl ,cw from XFJLB
group by cpbh,cw,lsh) x where x.lsh=@lsh and p.cpbh=x.cpbh) bb group by wlbh,cw) cc
where cc.wlbh = w.wlbh and k.ckmc = cc.cw and c.ckbh = k.ckbh and c.wlbh = cc.wlbh

END

INSERT INTO CKWL(ckbh, ckmc, wlbh, wlmc, kcsl, dj)
select k.ckbh, k.ckmc,cc.wlbh,w.wlmc ,-zl as zl,w.dj from CKDA k, WLDA w,
(select wlbh,sum(yl*sl) as zl,cw from(
select p.wlbh,p.yl,x.sl,x.cw from PF p,(select lsh,cpbh,sum(sl) as sl ,cw from XFJLB
group by cpbh,cw,lsh) x where x.lsh=@lsh and p.cpbh=x.cpbh) bb group by wlbh,cw) cc
where cc.wlbh = w.wlbh and k.ckmc = cc.cw and
not exists(select 1 from CKWL where ckbh=k.ckbh and wlbh=cc.wlbh)


GO


/*


ckbh ckmc wlbh wlmc kcsl dj
-------------------- -------------------- ----------- -------------------- ------- -------
01 中厨 1000 精肉 -3.00 5.00
01 中厨 1001 味精 -.03 3.20
01 中厨 1002 糖 -.15 .50
01 中厨 3000 糖1 -2.00 .50

*/

[Free_Windy]

有点晕....