create table test1111
( 批次号 INT NOT NULL IDENTITY , --为方便,免得需要生成,改了类型
商品类别 varchar(20),
商品名称 varchar(20),
商品规格 varchar(20),
商品数量 int,
商品价格 int,
出入库标志 varchar(20),
日期 datetime,
入库单编号 varchar(20),
出库单编号 varchar(20)
)
GO
CREATE PROC PR_出库
@商品类别 varchar(20),
@商品名称 varchar(20),
@商品规格 varchar(20),
@商品数量 int,
@日期 datetime,
@出库单编号 varchar(20)
AS--计算库存
DECLARE @目前库存 TABLE (
批次号 INT,
入库单编号 varchar(20),
商品数量 int,
商品价格 int
)INSERT @目前库存
SELECT
MIN(批次号) AS 批次号,
入库单编号,
SUM(CASE WHEN 出入库标志='入库' THEN 商品数量 ELSE -商品数量 END) AS 商品数量,
MIN(CASE WHEN 出入库标志='入库' THEN 商品价格 ELSE NULL END) AS 商品价格
FROM TEST1111
WHERE 商品类别=@商品类别
AND 商品名称=@商品名称
AND 商品规格=@商品规格
AND 日期<=@日期
GROUP BY 入库单编号
HAVING SUM(CASE WHEN 出入库标志='入库' THEN 商品数量 ELSE -商品数量 END)>0
ORDER BY 1,2--拆分出库记录
WHILE @商品数量>0 AND EXISTS (SELECT 1 FROM @目前库存)
BEGIN
SET ROWCOUNT 1
INSERT TEST1111 SELECT
@商品类别,
@商品名称,
@商品规格,
CASE WHEN 商品数量<=@商品数量 THEN 商品数量 ELSE @商品数量 END AS 商品数量,
商品价格,
出入库标志='出库',
@日期,
入库单编号,
@出库单编号
FROM @目前库存
SELECT @商品数量=CASE WHEN 商品数量<=@商品数量 THEN @商品数量-商品数量 ELSE 0 END FROM @目前库存
DELETE @目前库存
SET ROWCOUNT 0
ENDIF @商品数量>0 --不够出库
RETURN -1RETURN 0GO
/*入库数据*/
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-1','a-1',null
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-1','a-2',null
insert into test1111 select 'a','a1','a11',20,200,'入库','2006-1-1','a-3',null
insert into test1111 select 'a','a1','a11',30,300,'入库','2006-1-1','a-4',null
insert into test1111 select 'a','a1','a11',40,400,'入库','2006-1-1','a-5',null/*
出库数据
假设要出库:时间为2006-1-2,类别为a,名称为a1,规格为a11的商品50件,则出库数据为
这里入库单是5张,所以有5个入库单号,出库单是1张,只有一个出库单号
*//*
insert into test1111 select'6','a','a1','a11',10,100,'出库','2006-1-2',null,'b-1'
insert into test1111 select'7','a','a1','a11',10,100,'出库','2006-1-2',null,'b-1'
insert into test1111 select'8','a','a1','a11',20,200,'出库','2006-1-2',null,'b-1'
insert into test1111 select'9','a','a1','a11',10,300,'出库','2006-1-2',null,'b-1'
*/
EXEC PR_出库 'a','a1','a11',50,'2006-1-2','b-1'
GO
--查看结果
SELECT * FROM TEST1111/*再出库5件*/
/*
insert into test1111 select'10','a','a1','a11',5,300,'出库','2006-1-2',null,'b-2'
*/
EXEC PR_出库 'a','a1','a11',5,'2006-1-2','b-2'
GO
--查看结果
SELECT * FROM TEST1111/*再入库*/
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-3','a-6',null
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-3','a-7',null
insert into test1111 select 'a','a1','a11',20,200,'入库','2006-1-3','a-8',null
insert into test1111 select 'a','a1','a11',30,300,'入库','2006-1-3','a-9',null
insert into test1111 select 'a','a1','a11',40,400,'入库','2006-1-3','a-10',null
/*再出库50件*/
/*
insert into test1111 select'16','a','a1','a11',15,300,'出库','2006-1-3',null,'b-3'
insert into test1111 select'17','a','a1','a11',35,400,'出库','2006-1-3',null,'b-3'
*/
EXEC PR_出库 'a','a1','a11',50,'2006-1-3','b-3'
GO
--查看结果
SELECT * FROM TEST1111/*再出库30件*/
/*
insert into test1111 select'18','a','a1','a11',15,400,'出库','2006-1-3',null,'b-4'
insert into test1111 select'19','a','a1','a11',15,100,'出库','2006-1-3',null,'b-4'
*/EXEC PR_出库 'a','a1','a11',30,'2006-1-3','b-4'
GO
--查看最后结果
SELECT * FROM TEST1111--删除环境
drop PROC PR_出库
drop table test1111----------------------------------------
/*再出库30件*/
/*
insert into test1111 select'18','a','a1','a11',15,400,'出库','2006-1-3',null,'b-4'
insert into test1111 select'19','a','a1','a11',15,100,'出库','2006-1-3',null,'b-4'
*/
这里如果录人错误,不是30件,而应该是40件,但是这30件已经保存到数据库中
应该怎样做冲销处理,才能改成40件,而不影响先进先出的算法.
( 批次号 INT NOT NULL IDENTITY , --为方便,免得需要生成,改了类型
商品类别 varchar(20),
商品名称 varchar(20),
商品规格 varchar(20),
商品数量 int,
商品价格 int,
出入库标志 varchar(20),
日期 datetime,
入库单编号 varchar(20),
出库单编号 varchar(20)
)
GO
CREATE PROC PR_出库
@商品类别 varchar(20),
@商品名称 varchar(20),
@商品规格 varchar(20),
@商品数量 int,
@日期 datetime,
@出库单编号 varchar(20)
AS--计算库存
DECLARE @目前库存 TABLE (
批次号 INT,
入库单编号 varchar(20),
商品数量 int,
商品价格 int
)INSERT @目前库存
SELECT
MIN(批次号) AS 批次号,
入库单编号,
SUM(CASE WHEN 出入库标志='入库' THEN 商品数量 ELSE -商品数量 END) AS 商品数量,
MIN(CASE WHEN 出入库标志='入库' THEN 商品价格 ELSE NULL END) AS 商品价格
FROM TEST1111
WHERE 商品类别=@商品类别
AND 商品名称=@商品名称
AND 商品规格=@商品规格
AND 日期<=@日期
GROUP BY 入库单编号
HAVING SUM(CASE WHEN 出入库标志='入库' THEN 商品数量 ELSE -商品数量 END)>0
ORDER BY 1,2--拆分出库记录
WHILE @商品数量>0 AND EXISTS (SELECT 1 FROM @目前库存)
BEGIN
SET ROWCOUNT 1
INSERT TEST1111 SELECT
@商品类别,
@商品名称,
@商品规格,
CASE WHEN 商品数量<=@商品数量 THEN 商品数量 ELSE @商品数量 END AS 商品数量,
商品价格,
出入库标志='出库',
@日期,
入库单编号,
@出库单编号
FROM @目前库存
SELECT @商品数量=CASE WHEN 商品数量<=@商品数量 THEN @商品数量-商品数量 ELSE 0 END FROM @目前库存
DELETE @目前库存
SET ROWCOUNT 0
ENDIF @商品数量>0 --不够出库
RETURN -1RETURN 0GO
/*入库数据*/
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-1','a-1',null
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-1','a-2',null
insert into test1111 select 'a','a1','a11',20,200,'入库','2006-1-1','a-3',null
insert into test1111 select 'a','a1','a11',30,300,'入库','2006-1-1','a-4',null
insert into test1111 select 'a','a1','a11',40,400,'入库','2006-1-1','a-5',null/*
出库数据
假设要出库:时间为2006-1-2,类别为a,名称为a1,规格为a11的商品50件,则出库数据为
这里入库单是5张,所以有5个入库单号,出库单是1张,只有一个出库单号
*//*
insert into test1111 select'6','a','a1','a11',10,100,'出库','2006-1-2',null,'b-1'
insert into test1111 select'7','a','a1','a11',10,100,'出库','2006-1-2',null,'b-1'
insert into test1111 select'8','a','a1','a11',20,200,'出库','2006-1-2',null,'b-1'
insert into test1111 select'9','a','a1','a11',10,300,'出库','2006-1-2',null,'b-1'
*/
EXEC PR_出库 'a','a1','a11',50,'2006-1-2','b-1'
GO
--查看结果
SELECT * FROM TEST1111/*再出库5件*/
/*
insert into test1111 select'10','a','a1','a11',5,300,'出库','2006-1-2',null,'b-2'
*/
EXEC PR_出库 'a','a1','a11',5,'2006-1-2','b-2'
GO
--查看结果
SELECT * FROM TEST1111/*再入库*/
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-3','a-6',null
insert into test1111 select 'a','a1','a11',10,100,'入库','2006-1-3','a-7',null
insert into test1111 select 'a','a1','a11',20,200,'入库','2006-1-3','a-8',null
insert into test1111 select 'a','a1','a11',30,300,'入库','2006-1-3','a-9',null
insert into test1111 select 'a','a1','a11',40,400,'入库','2006-1-3','a-10',null
/*再出库50件*/
/*
insert into test1111 select'16','a','a1','a11',15,300,'出库','2006-1-3',null,'b-3'
insert into test1111 select'17','a','a1','a11',35,400,'出库','2006-1-3',null,'b-3'
*/
EXEC PR_出库 'a','a1','a11',50,'2006-1-3','b-3'
GO
--查看结果
SELECT * FROM TEST1111/*再出库30件*/
/*
insert into test1111 select'18','a','a1','a11',15,400,'出库','2006-1-3',null,'b-4'
insert into test1111 select'19','a','a1','a11',15,100,'出库','2006-1-3',null,'b-4'
*/EXEC PR_出库 'a','a1','a11',30,'2006-1-3','b-4'
GO
--查看最后结果
SELECT * FROM TEST1111--删除环境
drop PROC PR_出库
drop table test1111----------------------------------------
/*再出库30件*/
/*
insert into test1111 select'18','a','a1','a11',15,400,'出库','2006-1-3',null,'b-4'
insert into test1111 select'19','a','a1','a11',15,100,'出库','2006-1-3',null,'b-4'
*/
这里如果录人错误,不是30件,而应该是40件,但是这30件已经保存到数据库中
应该怎样做冲销处理,才能改成40件,而不影响先进先出的算法.
我觉得问题在于你怎么定义这个业务规则。冲销处理的业务规则是业务定义的,不是技术定义的。
假设你录入30件之后,又录入了出库60件,这时候你发现30错了,应该是40(或者是20)。你允许不允许修改,修改规则是什么?
MARK
我觉得问题在于你怎么定义这个业务规则。冲销处理的业务规则是业务定义的,不是技术定义的。
假设你录入30件之后,又录入了出库60件,这时候你发现30错了,应该是40(或者是20)。你允许不允许修改,修改规则是什么?
----------------------------------------------------------------------------------
zjcxc(邹建)
------------------------------------------
如果不允许修改,直接冲掉,重新生成新的出库单数据,该怎样做呢?
-------------------------
多谢邹老大,这个先放一放,我先与用户先了六清楚再做.不过,有个问题,在通常的情况下,这种情况用户是如何定义业务规则的?
-------------------------------------------
是啊,是啊,真的好累啊 ,这么多东西需要学
-----------------------------------------
冲数可以规定下,最好是单独的“出入库标志”,用单独的编号,这样存储过程也需要相应做调整现在test表里面已经有了"出入库"字段了,单独的编号就是入库编号和出库编号不能重复吧
这个也容易做到.
-----------------------------------------
但是存储过程怎样修改呢?
http://community.csdn.net/Expert/topic/5027/5027786.xml?temp=1.918972E-03前面那个加权平均的贴子地址没找到我觉得,综合来考虑,先进先出算法往往需要查询以前的记录,做起来其实很麻烦,如果你的用户不是很规范的话,最好不用说个最简单的方法(一般平均):
create table 出入库表
(
批次号 INT NOT NULL IDENTITY ,
商品类别 varchar(20),
商品名称 varchar(20),
商品规格 varchar(20),
商品数量 int,
商品价格 numeric(18,2),
出入库标志 varchar(20),
日期 datetime,
出入库单编号 varchar(20)
)
GOcreate table 库存表
(
商品类别 varchar(20),
商品名称 varchar(20),
商品规格 varchar(20),
商品数量 int,
商品金额 numeric(18,2),
最后更新日期 datetime,
最后编号 varchar(20)
)
GO--insert触发器
create trigger tr_出入库表_insert
on 出入库表
for insert
as--更新出库记录的单价
update 出入库表
set 商品价格=b.商品金额/b.商品数量
from 出入库表 a,库存表 b,inserted i
where a.批次号=i.批次号
and a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格
and a.出入库标志='出库'--更新库存表
update 库存表
set 商品数量=a.商品数量+b.商品数量,
商品金额=a.商品金额+b.商品金额,
最后更新日期=b.日期,
最后编号=b.出入库单编号
from 库存表 a,(
select
商品类别,
商品名称,
商品规格,
sum(case when 出入库标志='出库' then -商品数量 else 商品数量 end) as 商品数量,
sum(case when 出入库标志='出库' then -商品数量*商品价格 else 商品数量*商品价格 end) as 商品金额,
max(日期) as 日期,
max(出入库单编号) as 出入库单编号
from inserted
group by
商品类别,
商品名称,
商品规格
) as b
where
a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格insert 库存表 (
商品类别,
商品名称,
商品规格,
商品数量,
商品金额,
最后更新日期,
最后编号
)
select
商品类别,
商品名称,
商品规格,
sum(case when 出入库标志='出库' then -商品数量 else 商品数量 end) as 商品数量,
sum(case when 出入库标志='出库' then -商品数量*商品价格 else 商品数量*商品价格 end) as 商品金额,
max(日期) as 日期,
max(出入库单编号) as 出入库单编号
from inserted b
where not exists (
select 1 from 库存表 a
where
a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格
)
group by
商品类别,
商品名称,
商品规格go
--delete触发器
create trigger tr_出入库表_delete
on 出入库表
for delete
as
--更新库存表
update 库存表
set 商品数量=a.商品数量+b.商品数量,
商品金额=a.商品金额+b.商品金额,
最后更新日期=b.日期,
最后编号=b.出入库单编号
from 库存表 a,(
select
商品类别,
商品名称,
商品规格,
sum(case when 出入库标志='出库' then 商品数量 else -商品数量 end) as 商品数量,
sum(case when 出入库标志='出库' then 商品数量*商品价格 else -商品数量*商品价格 end) as 商品金额,
max(日期) as 日期,
max(出入库单编号) as 出入库单编号
from deleted
group by
商品类别,
商品名称,
商品规格
) as b
where
a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格--这个insert语句疑似多余
insert 库存表 (
商品类别,
商品名称,
商品规格,
商品数量,
商品金额,
最后更新日期,
最后编号
)
select
商品类别,
商品名称,
商品规格,
sum(case when 出入库标志='出库' then 商品数量 else -商品数量 end) as 商品数量,
sum(case when 出入库标志='出库' then 商品数量*商品价格 else -商品数量*商品价格 end) as 商品金额,
max(日期) as 日期,
max(出入库单编号) as 出入库单编号
from deleted b
where not exists (
select 1 from 库存表 a
where
a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格
)
group by
商品类别,
商品名称,
商品规格go
--update触发器
create trigger tr_出入库表_update
on 出入库表
for update
as--本来,修改出库记录应该会影响出库记录的单价,但我想情况太复杂,
--不修改单价也许是简单的方法,反正总的数量和金额都不会跑了
--更新库存表
update 库存表
set 商品数量=a.商品数量+b.商品数量,
商品金额=a.商品金额+b.商品金额,
最后更新日期=b.日期,
最后编号=b.出入库单编号
from 库存表 a,(
select
商品类别,
商品名称,
商品规格,
sum(商品数量) as 商品数量,
sum(商品金额) as 商品金额,
max(日期) as 日期,
max(出入库单编号) as 出入库单编号
from (
select
商品类别,
商品名称,
商品规格,
case when 出入库标志='出库' then 商品数量 else -商品数量 end as 商品数量,
case when 出入库标志='出库' then 商品数量*商品价格 else -商品数量*商品价格 end as 商品金额,
日期,
出入库单编号
from deleted
union all
select
商品类别,
商品名称,
商品规格,
case when 出入库标志='出库' then -商品数量 else 商品数量 end as 商品数量,
case when 出入库标志='出库' then -商品数量*商品价格 else 商品数量*商品价格 end as 商品金额,
日期,
出入库单编号
from inserted
) as t
group by
商品类别,
商品名称,
商品规格
) as b
where
a.商品类别=b.商品类别
and a.商品名称=b.商品名称
and a.商品规格=b.商品规格--这个insert语句疑似多余,不要了吧go--开始测试/*入库数据*/
insert into 出入库表 select 'a','a1','a11',10,100,'入库','2006-1-1','a-1'
insert into 出入库表 select 'a','a1','a11',10,100,'入库','2006-1-1','a-2'
insert into 出入库表 select 'a','a1','a11',20,200,'入库','2006-1-1','a-3'
insert into 出入库表 select 'a','a1','a11',30,300,'入库','2006-1-1','a-4'
insert into 出入库表 select 'a','a1','a11',40,400,'入库','2006-1-1','a-5'--查看库存结果
select * from 库存表/*
出库数据
假设要出库:时间为2006-1-2,类别为a,名称为a1,规格为a11的商品50件,则出库数据为
这里入库单是5张,所以有5个入库单号,出库单是1张,只有一个出库单号
*/insert into 出入库表 select 'a','a1','a11',50,0,'出库','2006-1-2','b-1'--查看单价结果
SELECT * FROM 出入库表--查看库存结果
select * from 库存表/*再出库5件*/insert into 出入库表 select 'a','a1','a11',5,300,'出库','2006-1-2','b-2'--查看单价结果
SELECT * FROM 出入库表--查看库存结果
select * from 库存表/*再入库*/
--顺便测试下一次插入多条记录
insert into 出入库表 select 'a','a1','a11',10,100,'入库','2006-1-3','a-6'
union all select 'a','a1','a11',10,100,'入库','2006-1-3','a-7'
union all select 'a','a1','a11',20,200,'入库','2006-1-3','a-8'
union all select 'a','a1','a11',30,300,'入库','2006-1-3','a-9'
union all select 'a','a1','a11',40,400,'入库','2006-1-3','a-10'--查看库存结果
select * from 库存表/*再出库50件*/insert into 出入库表 select 'a','a1','a11',50,300,'出库','2006-1-3','b-3'--查看单价结果
SELECT * FROM 出入库表--查看库存结果
select * from 库存表/*再出库30件*/insert into 出入库表 select 'a','a1','a11',30,300,'出库','2006-1-3','b-4'--查看单价结果
SELECT * FROM 出入库表--查看库存结果
select * from 库存表/*
这里如果录人错误,不是30件,而应该是40件
*/
update 出入库表
set 商品数量=40
where 批次号=14--查看单价结果
SELECT * FROM 出入库表--查看库存结果
select * from 库存表
--删除环境
drop table 出入库表
drop table 库存表
-------------------------
老兄辛苦了,十分感谢
----------------------------
如果用"一般平均"的话,那昨天写的"存储过程"只能作为参考了.
:(
如果使用"一般平均"法的话,那么冲销处理该怎样解决?
-------------------------这张帖子我得好好收藏,启发很大