库存物资管理（参考案例）

insert into stocking values('100','100005','2010001',200,'2006-01-02','小明'); insert into stocking values('101','100005','2010003',200,'2006-01-02','小兰'); insert into stocking values('102','100001','2010003',200,'2006-01-02','小东'); insert into stocking values('103','100002','2010001',200,'2006-01-02','小红'); insert into stocking values('104','100003','2010001',200,'2007-01-14','小风'); insert into stocking values('105','100005','2010002',200,'2006-01-25','小明'); insert into stocking values('106','100000','2010002',200,'2006-02-02','小明'); insert into stocking values('107','100001','2010001',200,'2005-03-02','小东'); insert into stocking values('108','100002','2010002',200,'2007-04-02','小红'); insert into stocking values('109','100003','2010003',200,'2006-04-02','小风'); insert into stocking values('110','100004','2010001',200,'2006-06-09','小敏'); insert into stocking values('111','100004','2010002',200,'2005-06-02','小风'); insert into stocking values('112','100005','2010001',200,'2005-06-02','小青'); insert into stocking values('113','100000','2010003',200,'2005-06-02','小明'); insert into stocking values('114','100001','2010001',200,'2005-06-02','小东'); insert into stocking values('115','100002','2010002',200,'2007-07-02','小红'); insert into stocking values('116','100003','2010003',200,'2006-08-02','小风');

第 13 页 共 27 页

insert into stocking values('117','100000','2010001',200,'2006-08-02','小明'); insert into stocking values('118','100001','2010001',200,'2005-09-04','小东'); insert into stocking values('119','100002','2010002',200,'2005-06-02','小红'); insert into stocking values('120','100003','2010003',200,'2005-09-11','小风'); insert into stocking values('121','100002','2010002',200,'2005-09-01','小明'); insert into stocking values('122','100003','2010002',200,'2005-07-08','小敏'); insert into stocking values('123','100002','2010003',200,'2005-04-11','小敏'); insert into stocking values('124','100001','2010001',200,'2007-07-08','小红'); insert into stocking values('125','100001','2010003',200,'2005-08-21','小青'); insert into stocking values('126','100005','2010001',200,'2005-07-08','小青'); insert into stocking values('127','100003','2010003',200,'2005-09-01','小兰'); insert into stocking values('128','100002','2010002',200,'2007-07-08','小兰'); insert into stocking values('129','100004','2010001',200,'2007-07-08','小兰'); insert into stocking values('130','100005','2010002',200,'2007-07-08','小红');

六、 实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品

库存台帐的更新。（龙XX负责部分）

出库表触发器

create trigger outbound_d on Outbound --这是出库表的触发器 after insert as begin

declare @a char(10),@b char(10),@d int ; select @a=i.DNo,@b=i.WNo,@d=i.OAmount from inserted as i

if(select s.WNo from Stock as s where s.DNo=@a and s.WNo=@b ) is not null begin

if(select s.WAmount from Stock as s ,inserted where s.WAmount>=inserted.OAmount and s.WNo=inserted.WNo and s.DNo=inserted.DNo) is not null begin

update Stock

set Stock.WAmount=Stock.WAmount-@d where Stock.DNo=@a and Stock.WNo=@b end else begin

print '库存量不够'

rollback

第 14 页 共 27 页

end end else begin

print '库存中没有这种产品' rollback end end

insert into Outbound values('1','100005','2010001',50,'2010-01-02','小红'); insert into Outbound values('2','100005','2010002',30,'2010-01-02','小红'); insert into Outbound values('3','100001','2010003',50,'2010-01-02','小风'); insert into Outbound values('4','100005','2010001',30,'2010-01-02','小红');

第 15 页 共 27 页

insert into Outbound values('5','100002','2010002',20,'2010-02-08','小风'); insert into Outbound values('6','100003','2010003',50,'2010-03-09','小红'); insert into Outbound values('7','100000','2010002',30,'2009-04-15','小风'); insert into Outbound values('8','100004','2010002',50,'2009-04-15','小风'); insert into Outbound values('9','100005','2010001',30,'2010-06-02','小红'); insert into Outbound values('10','100005','2010002',50,'2009-04-15','小红'); insert into Outbound values('11','100001','2010003',50,'2010-06-03','小风'); insert into Outbound values('12','100005','2010001',30,'2009-04-15','小红'); insert into Outbound values('13','100002','2010002',20,'2010-07-05','小风'); insert into Outbound values('14','100003','2010003',50,'2009-07-05','小红'); insert into Outbound values('15','100000','2010002',40,'2010-07-05','小风'); insert into Outbound values('16','100004','2010002',50,'2009-07-05','小风'); insert into Outbound values('17','100005','2010002',20,'2008-09-01','小明'); insert into Outbound values('18','100001','2010003',30,'2010-09-01','小敏'); insert into Outbound values('19','100000','2010002',40,'2010-09-01','小敏'); insert into Outbound values('20','100002','2010002',50,'2008-09-01','小红'); insert into Outbound values('21','100000','2010002',20,'2010-08-21','小青'); insert into Outbound values('22','100003','2010001',30,'2008-09-01','小青'); insert into Outbound values('23','100002','2010002',40,'2008-09-01','小兰'); insert into Outbound values('24','100003','2010003',50,'2008-12-30','小兰'); insert into Outbound values('25','100004','2010002',20,'2009-12-30','小兰'); insert into Outbound values('26','100005','2010001',40,'2008-12-08','小红'); insert into Outbound values('27','100001','2010003',10,'2010-12-30','小敏'); insert into Outbound values('28','100002','2010002',30,'2008-12-08','小明'); insert into Outbound values('29','100003','2010003',30,'2009-12-18','小红'); insert into Outbound values('30','100004','2010001',40,'2010-09-01','小敏');

七、 实现按商品名称查询商品的入库情况及目前的库存量。（梁XX负责部

分）

/*方法一*/

select SNo,WName,s.WNo,s.DNo,SAmount,WAmount from Stock as s,Ware as w,Stocking as si

where WName='电冰箱' and s.WNo=w.WNo and w.WNo=si.WNo and s.DNo=si.DNo

/*方法二*/

declare @WName varchar(32)

SET @WName ='电冰箱' SELECT *

FROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName = @WName);

第 16 页 共 27 页