SQL SERVER 2005中全新的OUTPUT子句添加數據記錄詳解(理論篇)
2024-08-31 00:53:14
供稿：網友
也許大家在數據庫開發的時候，會發現這樣一個現象：添加數據記錄后想查看其結果都必須通過select表達式來查詢實現。一定都要多此一舉才可以看到被添加的數據記錄嗎？答案是否定的。SQL SERVER 2005新提供的OUTPUT子句就幫您解決這個難題，它以比觸發器更簡潔的方式，在添加數據記錄的同時或者事后顯示所添加的數據記錄內容。下面是作者通過查找幫助文檔MSDN和章立民老師的《SQL Server 2005數據庫開發實戰》等資料后總結如下:
理論篇
OUTPUT子句返回受 INSERT、UPDATE 或 DELETE 語句影響的每行的信息，或者返回基于上述每行的表達式。這些結果可以返回到處理應用程序，以供在確認消息、存檔以及其他類似的應用程序要求中使用。此外，也可以將結果插入表或表變量。
用于：
DELETE
INSERT
UPDATE
Transact-SQL 語法約定
語法
<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_exPRession } [ [AS] column_alias_identifier ]
[ ,...n ]
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
備注
OUTPUT <dml_select_list> 子句和 OUTPUT <dml_select_list> INTO { @table_variable | output_table } 子句可以在單個 INSERT、UPDATE 或 DELETE 語句中定義。
注意：
除非另行指定，否則，對 OUTPUT 子句的引用將同時引用 OUTPUT 子句和 OUTPUT INTO 子句。
OUTPUT 子句對于在 INSERT 或 UPDATE 操作之后檢索標識列或計算列的值可能非常有用。
當 <dml_select_list> 中包含計算列時，輸出表或表變量中的相應列并不是計算列。新列中的值是在執行該語句時計算出的值。
以下語句中不支持 OUTPUT 子句：
引用本地分區視圖、分布式分區視圖或遠程表的 DML 語句。
包含 EXECUTE 語句的 INSERT 語句。
不能將 OUTPUT INTO 子句插入視圖或行集函數。
無法保證將更改應用于表的順序與將行插入輸出表或表變量的順序相對應。
如果將參數或變量作為 UPDATE 語句的一部分進行了修改，則 OUTPUT 子句將始終返回語句執行之前的參數或變量的值而不是已修改的值。
在使用 WHERE CURRENT OF 語法通過游標定位的 UPDATE 或 DELETE 語句中，可以使用 OUTPUT。
觸發器
從 OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 語句完成之后但在觸發器執行之前的數據。
對于 INSTEAD OF 觸發器，即使沒有因為觸發器的操作而發生修改，也會如同實際執行 INSERT、UPDATE 或 DELETE 那樣生成返回的結果。如果在觸發器的主體內使用包含 OUTPUT 子句的語句，則必須使用表別名來引用觸發器 inserted 和 deleted 表，以免使用與 OUTPUT 關聯的 INSERTED 和 DELETED 表復制列引用。
如果指定了 OUTPUT 子句但未同時指定 INTO 關鍵字，則對于給定的 DML 操作，DML 操作的目標不能啟用對其定義的任何觸發器。例如，如果在 UPDATE 語句中定義了 OUTPUT 子句，則目標表不能具有任何啟用的 UPDATE 觸發器。
如果設置了 sp_configure 選項 disallow results from triggers，則從觸發器內調用語句時，不帶 INTO 子句的 OUTPUT 子句將導致該語句失敗。
數據類型
OUTPUT 子句支持下列大型對象數據類型：nvarchar(max)、varchar(max)、varbinary(max)、 text、ntext、image 和 xml。當在 UPDATE 語句中使用 .WRITE 子句修改 nvarchar(max)、varchar(max) 或 varbinary(max) 列時，如果引用了值的全部前像和后像，則將其返回。在 OUTPUT 子句中，TEXTPTR( ) 函數不能作為 text、ntext 或 image 列的表達式的一部分出現。
隊列
可以在將表用作隊列或將表用于保持中間結果集的應用程序中使用 OUTPUT。換句話說，應用程序不斷地在表中添加或刪除行。以下示例在 DELETE 語句中使用 OUTPUT 子句將已刪除的行返回到執行調用的應用程序。
復制代碼
USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
此示例從用作隊列的表中刪除一行，并使用單個操作將已刪除的值返回到處理應用程序。還可實現其他語義，例如使用表來實現堆棧。但是，SQL Server 并不保證由使用 OUTPUT 子句的 DML 語句處理和返回行的順序。應用程序負責包括可保證所需語義的適當 WHERE 子句，或者理解當針對 DML 操作可能限定多行時，沒有保證的順序。以下示例使用子查詢，并假定 DatabaseLogID 列具有唯一性特征才能實現所需的排序語義。
復制代碼
USE AdventureWorks;
GO
DELETE dbo.DatabaseLog
OUTPUT deleted.*
WHERE DatabaseLogID IN (SELECT TOP(5) DatabaseLogID FROM dbo.DatabaseLog ORDER BY PostTime);
GO
注意：
如果您的方案允許多個應用程序從一個表中執行析構性讀取，請在 UPDATE 和 DELETE 語句中使用 READPAST 表提示。這可防止在其他應用程序已經讀取表中第一個限定記錄的情況下出現鎖定問題。
參數
@table_variable
指定一個 table 變量，返回的行將插入該變量中而不是返回到調用方。@table_variable 必須在 INSERT、UPDATE 或 DELETE 語句之前聲明。
如果未指定 column_list，則 table 變量必須與 OUTPUT 結果集具有相同的列數。標識列和計算列除外，這兩種列必須跳過。如果指定了 column_list，則任何省略的列都必須允許空值，或者都分配有默認值。
output_table
指定一個表，返回的行將被插入該表中而不是返回到調用方。output_table 可以為臨時表。
如果未指定 column_list，則表必須與 OUTPUT 結果集具有相同的列數。標識列和計算列例外，必須跳過這兩種列。如果指定了 column_list，則任何省略的列都必須允許空值，或者都分配有默認值。
output_table 無法應用于以下情況：
具有啟用的對其定義的觸發器。
參與到外鍵約束雙方的任意一方。
具有 CHECK 約束或啟用的規則。
column_list
INTO 子句目標表上列名的可選列表。它類似于 INSERT 語句中允許使用的列列表。
scalar_expression
可取計算結果為單個值的任何符號和運算符的組合。只要子查詢返回單個值，便可使用子查詢。scalar_expression 中不允許使用聚合函數。
對修改的表中的列的任何引用都必須使用 INSERTED 或 DELETED 前綴限定。
column_alias_identifier
用于引用列名的替代名稱。
DELETED
指定由更新或刪除操作刪除的值的列前綴。以 DELETED 為前綴的列反映 UPDATE 或 DELETE 語句完成之前的值。
不能在 INSERT 語句中同時使用 DELETED 與 OUTPUT 子句。
INSERTED
列的前綴，指定由插入操作或更新操作添加的值。以 INSERTED 為前綴的列反映 UPDATE 或 INSERT 語句完成之后但在觸發器執行之前的值。
INSERTED 語句不能與 DELETE 語句的 OUTPUT 子句同時使用。
from_table_name
列的前綴，指定 DELETE 語句或 UPDATE 語句（用于指定要更新或刪除的行）的 FROM 子句中所包含的表。
如果還在 FROM 子句中指定了要修改的表，則對該表中的列的任何引用都必須使用 INSERTED 或 DELETED 前綴限定。
*
指定受刪除、插入或更新操作影響的所有列都將按照它們在表中的順序返回。
例如，以下 DELETE 語句中的 OUTPUT DELETED.* 將返回 ShoppingCartItem 表中所有已刪除的列：
復制代碼
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
顯式列引用。任何對修改的表的引用都必須使用相應的 INSERTED 或 DELETED 前綴正確限定，例如：INSERTED.column_name。
權限
要求對通過 <dml_select_list> 檢索的任何列或者在 <scalar_expression> 中使用的任何列具有 SELECT 權限。
要求對 <output_table> 中指定的任何表具有 INSERT 權限。
