中文字幕二区_国产精品免费在线观看_黄色网站观看_人人草人人澡_日本真实娇小xxxx

講解

1.清空臨時(shí)表BOT_T和需求表BOM_need,并在BOM表中找出最大LAYER值放入一個(gè)變量,做為要循環(huán)的次數(shù)

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

2.訂單數(shù)量與倉庫可用量做差放入需求表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   

此時(shí)表BOM_need中內(nèi)容如下：             

3.循環(huán)直至@layer>@eee,每次@layer自加一   

       3.1在ＢＯＭ表中找出layer=@layer的記錄并且把數(shù)量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

此時(shí)表BOM_T中內(nèi)容如下：              

       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

此時(shí)表BOM_need中內(nèi)容如下：              

       3.3清空表BOM_T并讓@layer加一

delete BOM_T       
set @layer=@layer+1  

出處：藍(lán)色理想
責(zé)任編輯：moby

上一頁 存儲過程之BOM初探 [3] 下一頁 存儲過程之BOM初探 [5]

◎進(jìn)入論壇網(wǎng)絡(luò)編程版塊參加討論