库存状况如下：

订单状况：

A、B、C、D、M、B、B1、B2、D1、D2                                                       
D3、E、F实际库存情况见表storage，现要满足销售订单001需要各原材料多少?                                                       

如下存储过程完成以上要求：

代码拷贝框
CREATE PROCEDURE EX_ORDERS /*本程序根据订单自动计算得出所需要采购的材料和应生产的产品数量*/ /*表BOM_need中layer=-1的即为要采购的材料或部件,其它的为要生产的产品*/ @order_num nvarchar(50) AS declare @layer int; declare @eee int BEGIN set @layer=0 set @eee=(select max(layer) from bom ) delete BOM_T delete BOM_need /*订单数量与仓库产品数量做差放入需求表 step1*/ insert into BOM_need (internel,need) select orders_mx.internel,orders_mx.counts-storage.available_q as need from orders_mx,storage where orders_mx.internel=storage.num and orders_mx.order_num=@order_num /*step2*/ while not(@layer>@eee) begin insert into BOM_T (internel,up_internel,layer,counts) select bom.internel,bom.up_internel,bom.layer,bom.counts*n.need as counts from bom,BOM_need as n where bom.up_internel in (select internel from BOM_need where layer=@layer) and bom.up_internel=n.internel insert into BOM_need (internel,need,layer) select b.internel,sum(b.counts)-s.available_q as counts,b.layer from BOM_T as b,storage as s where s.num=b.internel group by internel,layer,s.available_q delete BOM_T set @layer=@layer+1 end END GO
[Ctrl+A 全部选择 然后拷贝]                                               

出处：蓝色理想
责任编辑：moby

上一页 存储过程之BOM初探 [2] 下一页 存储过程之BOM初探 [4]

◎进入论坛网络编程版块参加讨论