[SQL] OUTPUT 語法

SQL Server 2005 功能 - OUTPUT，類似於 Trigger 的概念，可以在增刪修後，透過 inserted 和 deleted 找出增刪修前後的欄位資料

下列在 AdventureWorks 中利用產品（Production）、庫存量（StockQty）和入領料（Stock、StockDetail）來說明 OUTPUT 用法，以 SQL Server 2005 為主

• 建立基礎 Table 並插入 Sample Data

IF OBJECT_ID('StockQty') IS NOT NULL
  DROP TABLE StockQty
 
IF OBJECT_ID('Stock') IS NOT NULL
  DROP TABLE Stock

IF OBJECT_ID('StockDetail') IS NOT NULL
  DROP TABLE StockDetail
 
IF OBJECT_ID('Production') IS NOT NULL
  DROP TABLE Production

CREATE TABLE Production (ProdID int Primary key, ProdName nchar(50))
INSERT INTO Production VALUES(1,N'SQL Server 2014')
INSERT INTO Production VALUES(2,N'SQL Server 2012')
INSERT INTO Production VALUES(3,N'SQL Server 2008 R2')
INSERT INTO Production VALUES(4,N'SQL Server 2008')
INSERT INTO Production VALUES(5,N'SQL Server 2005')

CREATE TABLE StockQty (ProdID int Primary key, Qty int)
INSERT INTO StockQty VALUES(1,0)
INSERT INTO StockQty VALUES(2,0)
INSERT INTO StockQty VALUES(3,0)
INSERT INTO StockQty VALUES(4,0)
INSERT INTO StockQty VALUES(5,0)

CREATE TABLE Stock (SID int Primary key, [Date] date)
CREATE TABLE StockDetail (SID int Primary key(SID,ProdID), ProdID int , Qty int)
GO

• 建立 View：方便後續觀察執行結果

IF OBJECT_ID('vwStockQty') IS NOT NULL
  DROP VIEW vwStockQty

IF OBJECT_ID('vwStock') IS NOT NULL
  DROP VIEW vwStock
GO
 
CREATE VIEW vwStockQty
AS
  SELECT 
    P.ProdID ,
    P.ProdName ,
    Q.Qty
  FROM StockQty AS Q 
    JOIN Production AS P ON Q.ProdID = P.ProdID
GO

CREATE VIEW vwStock
AS
  SELECT 
    S.SID,
    S.[Date],
    SD.ProdID,
    P.ProdName,
    SD.Qty
  FROM Stock AS S
    JOIN StockDetail AS SD ON S.SID = SD.SID
    JOIN Production AS P ON SD.ProdID = P.ProdID
GO

• 透過 Store Procedure 來執行 DML

重點在於 dmlStockDetailI、dmlStockDetailD 和 dmlStockDetailU 中 OUTPUT 的使用

IF OBJECT_ID('dmlStockI') IS NOT NULL
  DROP PROCEDURE dmlStockI

IF OBJECT_ID('dmlStockD') IS NOT NULL
  DROP PROCEDURE dmlStockD
  
IF OBJECT_ID('dmlStockDetailI') IS NOT NULL
  DROP PROCEDURE dmlStockDetailI

IF OBJECT_ID('dmlStockDetailD') IS NOT NULL
  DROP PROCEDURE dmlStockDetailD

IF OBJECT_ID('dmlStockDetailU') IS NOT NULL
  DROP PROCEDURE dmlStockDetailU
GO

CREATE PROCEDURE dmlStockI
(
  @SID int
)
AS
  BEGIN
   
    INSERT INTO Stock (SID , [Date])
      VALUES(@SID,CAST(getdate() AS date))
 
  END
GO

CREATE PROCEDURE dmlStockD (@SID int)
AS
  BEGIN
    DELETE FROM Stock WHERE SID = @SID
  END
GO

CREATE PROCEDURE dmlStockDetailI
(
  @SID int ,
  @ProdID int ,
  @Qty int
)
AS
  BEGIN
   
    CREATE TABLE #Temp (ProdID int Primary Key,Qty int)
 
    INSERT INTO StockDetail (SID,ProdID,Qty)
      OUTPUT inserted.ProdID,inserted.Qty INTO #Temp
      VALUES(@SID,@ProdID,@Qty)
 
    UPDATE Q
      SET Q.Qty = Q.Qty + T.Qty
      FROM StockQty AS Q
        JOIN #Temp AS T ON Q.ProdID  = T.ProdID
 
    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
      DROP TABLE #Temp
   
  END
GO

CREATE PROCEDURE dmlStockDetailD (@SID int)
AS
  BEGIN
  
    CREATE TABLE #Temp (ProdID int Primary Key, Qty int)

    DELETE StockDetail
      OUTPUT deleted.ProdID , deleted.Qty INTO #Temp
      WHERE SID = @SID

    UPDATE Q
      SET Q.Qty = Q.Qty - T.Qty
      FROM StockQty AS Q
        JOIN #Temp AS T ON Q.ProdID = T.ProdID
  
    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
      DROP TABLE #Temp

  END
GO

CREATE PROCEDURE dmlStockDetailU
(
  @SID int,
  @ProdID int,
  @Qty int
)
AS
  BEGIN
  
    CREATE TABLE #Temp(ProdID int Primary Key, Qty int)

    UPDATE StockDetail
      SET Qty = @Qty
      OUTPUT deleted.ProdID , inserted.Qty - deleted.Qty INTO #Temp
      WHERE SID = @SID 
            AND ProdID = @ProdID 

    UPDATE Q
      SET Q.Qty =  Q.Qty + T.Qty
      FROM StockQty AS Q
        JOIN #Temp AS T ON Q.ProdID = T.ProdID

    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
      DROP TABLE #Temp

  END
GO

• 執行 1：第一筆入料單

EXEC dmlStockI @SID = 1
EXEC dmlStockDetailI @SID = 1 , @ProdID = 5 , @Qty = 10

• 執行 2：第二筆出料單

EXEC dmlStockI @SID = 2
EXEC dmlStockDetailI @SID = 2 , @ProdID = 5 , @Qty = -7

• 執行 3：修正第二筆出料單數量

EXEC dmlStockDetailU @SID = 2 , @ProdID = 5 , @Qty = -5

• 執行 4：刪除第二筆出料單

EXEC dmlStockd @SID = 2
EXEC dmlStockDetaild @SID = 2

• 延伸閱讀
• [SQL] Trigger - inserted & deleted Table
• [SQL] UPDATE 搭配 FROM 使用
• 參考資料
• OUTPUT
• 使用output into搬移資料表內容到另一資料表