讲解

1.清空临时表BOT_T和需求表BOM_need,并在BOM表中找出最大LAYER值放入一个变量,做为要循环的次数

set @layer=0               
set @eee=(select max(layer) from bom )               
delete BOM_T               
delete BOM_need    

2.订单数量与仓库可用量做差放入需求表BOM_need

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   

此时表BOM_need中内容如下：             

3.循环直至@layer>@eee,每次@layer自加一   

       3.1在ＢＯＭ表中找出layer=@layer的记录并且把数量counts与up_internel等于ＢＯＭ_need中的internel需求量need相乘

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

此时表BOM_T中内容如下：              

       3.2汇总B OM_T并与可用量相减更新BOM_need表

    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

此时表BOM_need中内容如下：              

       3.3清空表BOM_T并让@layer加一

delete BOM_T       
set @layer=@layer+1  

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

上一页 存储过程之BOM初探 [3] 下一页 存储过程之BOM初探 [5]

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