PROCEDURE

本实例涉及存储构成的创建，游标使用，循环嵌套，TABLE 变量使用，函数调用fuc_ReplenishData。

USE [WMS] GO /****** Object: StoredProcedure [dbo].[pro_getReplenishScheme] Script Date: 12/27/2010 22:28:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[pro_getReplenishScheme] AS BEGIN DELETE ReplenishSchemeTemp DECLARE @pdlitm VARCHAR(100) , @bin VARCHAR(100) , @Replenishqty DECIMAL(10, 4) , @StockName VARCHAR(100) , @StockQty DECIMAL(10, 4) , @stockId UNIQUEIDENTIFIER , @branch VARCHAR(100) DECLARE @table TABLE ( id UNIQUEIDENTIFIER , pdlitm VARCHAR(100) , StockQty DECIMAL(10, 4) , RepQty DECIMAL(10, 4) , bin VARCHAR(100) , stockName VARCHAR(100) , branch VARCHAR(100) ) ; DECLARE rem_cursor CURSOR FOR SELECT pdlitm,bin,Replenishqty FROM dbo.fuc_ReplenishData('%','%','%') WHERE stockqty-Replenishqty>0 AND pdlitm+bin NOT IN (SELECT DISTINCT(PDLITM+ToBin) FROM dbo.ReplenishCache) OPEN rem_cursor FETCH NEXT FROM rem_cursor INTO @pdlitm, @bin,@Replenishqty WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Replenishqty DECLARE stock_cursor CURSOR FOR SELECT id,Location,Qty,Branch FROM ( SELECT stock.id,dbo.Location.Location,Qty,Location.Branch,ReceivingTime, CASE WHEN dbo.Location.Location='MAG' THEN 0 ELSE 1 END AS palce FROM dbo.Stock INNER JOIN dbo.Location ON dbo.Stock.Location = dbo.Location.id WHERE Type = 0 AND PDLITM = @pdlitm ) t ORDER BY palce, ReceivingTime OPEN stock_cursor FETCH NEXT FROM stock_cursor INTO @stockId,@StockName,@StockQty,@branch WHILE @@FETCH_STATUS = 0 AND @Replenishqty > 0 BEGIN IF ( @StockQty > @Replenishqty ) BEGIN INSERT INTO @table VALUES ( @stockid, @pdlitm, @StockQty, @Replenishqty, @bin, @StockName, @branch ) SET @Replenishqty = 0 PRINT @stockid END ELSE BEGIN SET @Replenishqty = @Replenishqty - @StockQty INSERT INTO @table VALUES ( @stockid, @pdlitm, @StockQty, @StockQty, @bin, @StockName, @branch ) PRINT @stockid END FETCH NEXT FROM stock_cursor INTO @stockId,@StockName,@StockQty,@branch END CLOSE stock_cursor DEALLOCATE stock_cursor FETCH NEXT FROM rem_cursor INTO @pdlitm, @bin,@Replenishqty END CLOSE rem_cursor DEALLOCATE rem_cursor INSERT INTO ReplenishSchemeTemp SELECT * FROM @table END --pro_getReplenishScheme