萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

T-SQL DML 影響資訊獲取:OUTPUT 子句在 INSERT/UPDATE/DELETE 中的應用

前言

SQL Server 2005+ 引入 OUTPUT 子句,可在 DML 執行時直接回傳受影響的資料列(舊值/新值),無需額外查詢,極大簡化稽核、同步與邏輯處理。

一、多欄位更新

UPDATE testbook
SET price = price * 0.3,      -- 打 3 折
    remark = '過時圖書'        -- 備註
WHERE date <= '2000-01-01';

二、大型物件 (BLOB) 更新

UPDATE file_table
SET FileName = 'Img100.jpg',
    DOCUMENT = (
        SELECT * FROM OPENROWSET(BULK 'C:/Img100.jpg', SINGLE_BLOB) AS Document
    )
WHERE FileType = '.jpg';

三、刪除資料

部分刪除

DELETE FROM book2 WHERE DATE < '2000/1/1';

全表刪除

DELETE FROM book2;  -- 逐列刪除、記錄日誌、可回滾
TRUNCATE TABLE book2;  -- 一次釋放資料頁、不記日誌、不可回滾、速度極快
特性DELETETRUNCATE
日誌記錄逐列記錄僅記錄頁釋放
速度較慢極快
可回滾是否
觸發器啟動不啟動
身分識別種子保留重置

四、查看資料表結構

SELECT a.name AS 欄位名, b.name AS 資料類型, a.max_length AS 欄位長度
FROM sys.all_columns AS a
JOIN sys.types AS b ON a.system_type_id = b.system_type_id
WHERE object_id = OBJECT_ID('dbo.student');

五、OUTPUT 子句實戰

1. INSERT ... OUTPUT

-- 直接回傳
INSERT book
OUTPUT INSERTED.*
VALUES (106, '數字圖像處理', '宋青書', '科學出版社', '2006/7/1', '200607100', 25.00, '較差');

-- 導入資料表/表變數(不可用純變數接收)
IF OBJECT_ID('T2', 'U') IS NOT NULL DROP TABLE T2;
CREATE TABLE T2 (bkno INT, bkname NVARCHAR(50), author NVARCHAR(8));

INSERT book
OUTPUT INSERTED.bkno, INSERTED.bkname, INSERTED.author INTO T2
SELECT 104, 'JSP網站開發教程', '趙敏', '電子工業出版社', '2002/10/11', '200210201', 35.00, '較好'
UNION ALL
SELECT 105, 'C++Builder程式設計', '俞蓮舟', '人民郵電出版社', '2007/1/1', '200701111', 53.00, '一般';

2. DELETE ... OUTPUT

DELETE FROM book
OUTPUT DELETED.*
WHERE bkno = 104;

3. UPDATE ... OUTPUT

DECLARE @MyTableVar TABLE (
    preprice NUMERIC(10,2),
    posprice NUMERIC(10,2)
);

UPDATE book
SET price = price * 1.3
OUTPUT DELETED.price, INSERTED.price INTO @MyTableVar;

SELECT * FROM @MyTableVar;  -- 查看調整前後價格

六、OUTPUT 關鍵點總結

  • INSERTED:新資料列(INSERT/UPDATE 後的值)
  • DELETED:舊資料列(DELETE/UPDATE 前的值)
  • 可導向 TABLE、@TableVariable,不可導向純量變數
  • 搭配 MERGE 可同時處理 INSERT/UPDATE/DELETE 的輸出
  • 不支援觸發器巢狀參考的 INSERTED/DELETED 表

總結

OUTPUT 子句讓 DML 操作「所見即所得」,是實作稽核軌跡、資料同步、ETL 流程的利器。善用表變數暫存輸出結果,可避免建立永久稽核表的額外負擔。

copyright © 萬盛學電腦網 all rights reserved