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

庫存狀況如下：

訂單狀況：

A、B、C、D、M、B、B1、B2、D1、D2                                                       
D3、E、F實際庫存情況見表storage，現(xiàn)要滿足銷售訂單001需要各原材料多少?                                                       

如下存儲過程完成以上要求：

代碼拷貝框
CREATE PROCEDURE EX_ORDERS /*本程序根據(jù)訂單自動計算得出所需要采購的材料和應生產(chǎn)的產(chǎn)品數(shù)量*/ /*表BOM_need中l(wèi)ayer=-1的即為要采購的材料或部件,其它的為要生產(chǎn)的產(chǎn)品*/ @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 /*訂單數(shù)量與倉庫產(chǎn)品數(shù)量做差放入需求表 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]

◎進入論壇網(wǎng)絡編程版塊參加討論