在 SQL Server 中，OUTPUT 子句允許你捕獲由 INSERT、UPDATE 或 DELETE 語句影響的行，并將這些行作為結(jié)果集返回。這對于需要同時獲取修改的行和執(zhí)行修改操作本身非常有用。

參考官方地址
https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

1、本文內(nèi)容

• 語法
• 參數(shù)
• 注解
• 將從 OUTPUT 子句返回的數(shù)據(jù)插入表
• 并行度
• 觸發(fā)器
• 數(shù)據(jù)類型
• 權(quán)限
• 示例
• 相關(guān)內(nèi)容

適用于：

• SQL Server
• Azure SQL 數(shù)據(jù)庫
• Azure SQL 托管實例

返回受 INSERT、UPDATE、DELETE 或 MERGE 語句影響的各行中的信息，或返回基于受這些語句影響的各行的表達式。 這些結(jié)果可以返回到處理應(yīng)用程序，以供在確認消息、存檔以及其他類似的應(yīng)用程序要求中使用。 也可以將這些結(jié)果插入表或表變量。 另外，你可以捕獲嵌入的 INSERT、UPDATE、DELETE 或 MERGE 語句中 OUTPUT 子句的結(jié)果，然后將這些結(jié)果插入目標表或視圖。

備注對于具有 OUTPUT 子句的 UPDATE、INSERT 或 DELETE 語句，即使在遇到錯誤需要回滾時，也會將行返回到客戶端。 如果在運行語句的過程中出現(xiàn)任何錯誤，都不應(yīng)使用該結(jié)果。

2、語法

<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 }
    | $action

3、參數(shù)

@table_variable
指定 table 變量，返回的行將插入此變量，而不是返回給調(diào)用方。 @table_variable 必須在 INSERT、UPDATE、DELETE 或 MERGE 語句前聲明。

如果未指定 column_list，則 table 變量必須與 OUTPUT 結(jié)果集具有相同的列數(shù)。 標識列和計算列除外，這兩種列必須跳過。 如果指定了 column_list，則任何省略的列都必須允許 NULL 值，或者都分配有默認值。

有關(guān) table 變量的詳細信息，請參閱 table (Transact-SQL)。

output_table
指定一個表，返回的行將插入該表中而不是返回到調(diào)用方。 output_table 可以為臨時表。

如果未指定 column_list，則 table 必須與 OUTPUT 結(jié)果集具有相同的列數(shù)。 標識列和計算列例外， 必須跳過這兩種列。 如果指定了 column_list，則任何省略的列都必須允許 NULL 值，或者都分配有默認值。

output_table 無法：

• 具有啟用的對其定義的觸發(fā)器。
• 參與 FOREIGN KEY 約束的任意一方。
• 具有 CHECK 約束或啟用的規(guī)則。

column_list
INTO 子句目標表上列名的可選列表。 它類似于 INSERT 語句中允許使用的列列表。

• scalar_expression
  計算結(jié)果為單個值的任何符號和運算符的組合。 scalar_expression 中不允許使用聚合函數(shù)。

  對修改的表中的列的任何引用都必須使用 INSERTED 或 DELETED 前綴限定。

• column_alias_identifier
  用于引用列名的替換名稱。

• DELETED
  指定由更新或刪除操作刪除的值的列前綴。 以 DELETED 為前綴的列反映了 UPDATE、DELETE 或 MERGE 語句完成之前的值。

  不能在 INSERT 語句中同時使用 DELETED 與 OUTPUT 子句。

• INSERTED
  指定由插入作或更新操作添加的值的列前綴。 以 INSERTED 為前綴的列反映了在 UPDATE、INSERT 或 MERGE 語句完成之后但在觸發(fā)器執(zhí)行之前的值。

  不能在 DELETE 語句中同時使用 INSERTED 與 OUTPUT 子句。

• from_table_name
  一個列前綴，指定 DELETE、UPDATE 或 MERGE 語句（用于指定要更新或刪除的行）的 FROM 子句中包含的表。

  如果還在 FROM 子句中指定了要修改的表，則對該表中的列的任何引用都必須使用 INSERTED 或 DELETED 前綴限定。

例如，以下 DELETE 語句中的 OUTPUT DELETED.* 將返回 ShoppingCartItem 表中所有已刪除的列：

DELETE Sales.ShoppingCartItem OUTPUT DELETED.*;

• column_name
  顯式列引用。 任何對正在修改的表的引用都必須使用相應(yīng)的 INSERTED 或 DELETED 前綴正確限定，例如：INSERTED.column_name。

• $action
  僅可用于 MERGE 語句。 在 MERGE 語句的 OUTPUT 子句中指定一個 nvarchar(10) 類型的列，該子句為每行返回以下三個值之一：“INSERT”、“UPDATE”或“DELETE”，返回哪個值取決于對該行執(zhí)行的操作。

4、注解

OUTPUT <dml_select_list> 子句和 OUTPUT <dml_select_list> INTO { @table_variable | output_table } 子句可以在單個 INSERT、UPDATE、DELETE 或 MERGE 語句中定義。

備注

除非另行指定，否則，引用 OUTPUT 子句將同時引用 OUTPUT 子句和 OUTPUT INTO 子句。

OUTPUT 子句對于在 INSERT 或 UPDATE 操作之后檢索標識列或計算列的值可能非常有用。

當 <dml_select_list> 中包含計算列時，輸出表或表變量中的相應(yīng)列并不是計算列。 新列中的值是在執(zhí)行該語句時計算出的值。

無法保證將更改應(yīng)用于表的順序與將行插入輸出表或表變量的順序相對應(yīng)。

如果將參數(shù)或變量作為 UPDATE 語句的一部分進行了修改，則 OUTPUT 子句將始終返回語句執(zhí)行之前的參數(shù)或變量的值而不是已修改的值。

在使用 WHERE CURRENT OF 語法通過游標定位的 UPDATE 或 DELETE 語句中，可以使用 OUTPUT。

以下語句中不支持 OUTPUT 子句：

• 引用本地分區(qū)視圖、分布式分區(qū)視圖或遠程表的 DML 語句。

• 包含 EXECUTE 語句的 INSERT 語句。

• 當數(shù)據(jù)庫兼容級別設(shè)置為 100 時，不允許在 OUTPUT 子句中使用全文謂詞。

• 不能將 OUTPUT INTO 子句插入視圖或行集函數(shù)。

• 如果用戶定義的函數(shù)包含一個以表為目標的 OUTPUT INTO 子句，則不能創(chuàng)建該函數(shù)。

若要防止出現(xiàn)不確定的行為，OUTPUT 子句不能包含以下引用：

執(zhí)行用戶或系統(tǒng)數(shù)據(jù)訪問的子查詢或用戶定義函數(shù)，或者被認定會執(zhí)行此類訪問的子查詢或用戶定義函數(shù)。 如果用戶定義函數(shù)未綁定到架構(gòu)，則認定它會執(zhí)行數(shù)據(jù)訪問。

視圖或內(nèi)嵌表值函數(shù)中的一個列（如果該列由以下方法之一定義）：

如果 SQL Server 在 OUTPUT 子句中檢測到了此類列，將引發(fā)錯誤 4186。

• 子查詢。

• 執(zhí)行用戶數(shù)據(jù)訪問或系統(tǒng)數(shù)據(jù)訪問或者被認為執(zhí)行此種訪問的用戶定義函數(shù)。

• 定義中包含執(zhí)行用戶數(shù)據(jù)訪問或系統(tǒng)數(shù)據(jù)訪問的用戶定義函數(shù)的計算列。

5、將從 OUTPUT 子句返回的數(shù)據(jù)插入表

在捕獲嵌套的 INSERT、UPDATE、DELETE 或 MERGE 語句中 OUTPUT 子句的結(jié)果并將這些結(jié)果插入目標表時，請牢記以下信息：

整個操作是原子的。 INSERT 語句和包含 OUTPUT 子句的嵌套 DML 語句要么都執(zhí)行，要么整個語句都失敗。

以下限制適用于外層 INSERT 語句的目標：

• 目標不能為遠程表、視圖或公用表表達式。

• 目標不能有 FOREIGN KEY 約束，或者被 FOREIGN KEY 約束所引用。

• 不能對目標定義觸發(fā)器。

• 目標不能參與合并復(fù)制或事務(wù)復(fù)制的可更新訂閱。

對于嵌套的 DML 語句有以下限制：

• 目標不能為遠程表或分區(qū)視圖。

• 源本身不能包含 <dml_table_source> 子句。

• 包含 <dml_table_source> 子句的 INSERT 語句中不支持 OUTPUT INTO 子句。

• @@ROWCOUNT 返回僅由外層 INSERT 語句插入的行。

• @@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 僅返回由嵌套的 DML 語句生成的標識值，而不返回由外層 INSERT 語句生成的標識值。

• 查詢通知將語句作為單個實體進行處理，并且即使重大更改是來自外層 INSERT 語句本身，所創(chuàng)建的任何消息的類型也將是嵌套 DML 的類型。

• 在 <dml_table_source> 子句中，SELECT 和 WHERE 子句不能包括子查詢、聚合函數(shù)、排名函數(shù)、全文謂詞、執(zhí)行數(shù)據(jù)訪問的用戶定義函數(shù)或 TEXTPTR() 函數(shù)。

6、并行度

可將結(jié)果返回到客戶端或表變量的 OUTPUT 子句將始終使用串行計劃。

在兼容性級別設(shè)置為 130 或更高的數(shù)據(jù)庫的上下文中，如果 INSERT…SELECT 操作使用 SELECT 語句的 WITH (TABLOCK) 提示，并且使用 OUTPUT…INTO 插入臨時表或用戶表，則 INSERT…SELECT 的目標表將可以進行并行操作（具體取決于子樹成本）。 OUTPUT INTO 子句中引用的目標表不能進行并行操作。

7、觸發(fā)器

從 OUTPUT 返回的列反映 INSERT、UPDATE 或 DELETE 語句完成之后、觸發(fā)器執(zhí)行之前的數(shù)據(jù)。

對于 INSTEAD OF 觸發(fā)器，即使沒有因為觸發(fā)器的操作而發(fā)生修改，也會如同實際執(zhí)行 INSERT、UPDATE 或 DELETE 那樣生成返回的結(jié)果。 如果在觸發(fā)器的主體內(nèi)使用包含 OUTPUT 子句的語句，則必須使用表別名來引用插入的觸發(fā)器和刪除的表，以免使用與 OUTPUT 關(guān)聯(lián)的 INSERTED 和 DELETED 表復(fù)制列引用。

如果指定了 OUTPUT 子句但未同時指定 INTO 關(guān)鍵字，則對于給定的 DML 操作，DML 操作的目標不能啟用對其定義的任何觸發(fā)器。 例如，如果在 UPDATE 語句中定義了 OUTPUT 子句，則目標表不能具有任何啟用的 UPDATE 觸發(fā)器。

如果設(shè)置了 sp_configure 選項 disallow results from triggers，則從觸發(fā)器內(nèi)調(diào)用語句時，不帶 INTO 子句的 OUTPUT 子句將導(dǎo)致該語句失敗。

8、數(shù)據(jù)類型

OUTPUT 子句支持大型對象數(shù)據(jù)類型：nvarchar(max)、varchar(max)、varbinary(max)、text、ntext、image 和 xml。 當在 UPDATE 語句中使用 .WRITE 子句修改 nvarchar(max)、varchar(max) 或 varbinary(max) 列時，如果引用了值的全部前像和后像，則將其返回。 在 OUTPUT 子句中，TEXTPTR() 函數(shù)不能作為 text、ntext 或 image 列的表達式的一部分出現(xiàn)。

9、權(quán)限

要求對通過 <dml_select_list> 檢索或在 <scalar_expression> 中使用的任何列具有 SELECT 權(quán)限。

要求對 <output_table> 中指定的任何表具有 INSERT 權(quán)限。

10、示例

10.1、 將 OUTPUT INTO 用于 INSERT 語句

下面的示例將行插入到 T_ScrapReason 表中，并使用 OUTPUT 子句將語句的結(jié)果返回到 @MyTableVar 表變量。 由于 T_ScrapReason列使用 IDENTITY 屬性定義，因此未在 INSERT 語句中為該列指定一個值。 但是，將在列 INSERTED.ScrapReasonID 內(nèi)的 OUTPUT 子句中返回由數(shù)據(jù)庫引擎為該列生成的值。

CREATE TABLE T_ScrapReason(ID SMALLINT IDENTITY(1,1) PRIMARY KEY, UnitMeasureCode  NCHAR(8),NameInfo NVARCHAR(25),ModifiedDate DATETIME);
INSERT INTO T_ScrapReason VALUES(N'Car00001',N'問界M5','2024-07-01'),(N'Car00004',N'問界M9','2024-07-01');
SELECT * FROM T_ScrapReason

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,
	UnitMeasureCode  NCHAR(8),
    NameInfo NVARCHAR(50),
    ModifiedDate DATETIME);
    
INSERT T_ScrapReason
    OUTPUT INSERTED.ID, INSERTED.UnitMeasureCode,INSERTED.NameInfo, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Car00002',N'寶馬X5','2024-08-03'),(N'Car00003',N'蔚來ET5','2024-07-21');
  
--Display the result set of the table variable.
SELECT NewScrapReasonID,UnitMeasureCode, NameInfo, ModifiedDate FROM @MyTableVar;

--Display the result set of the table.
SELECT ID,UnitMeasureCode, NameInfo, ModifiedDate FROM T_ScrapReason;

10.2、將 OUTPUT 與 DELETE 語句一起使用

以下示例將刪除 T_ScrapReason表中的所有行。 子句 OUTPUT DELETED.* 指定 DELETE 語句的結(jié)果（即已刪除的行中的所有列）將返回到執(zhí)行調(diào)用的應(yīng)用程序。 后面的 SELECT 語句驗證對 T_ScrapReason表所執(zhí)行的刪除操作的結(jié)果。

DELETE T_ScrapReason
OUTPUT DELETED.*
WHERE ID >=3;
  
--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM T_ScrapReason WHERE ID >=3;
GO

10.3、將 OUTPUT INTO 與 UPDATE 語句一起使用

下面的示例將 T_ScrapReason 表中 NameInfo 列的更新為NameInfo=豐田皇冠。
OUTPUT 子句將返回 NameInfo 值，該值在將 UPDATE 列中的 DELETED.NameInfo 語句和 INSERTED.NameInfo 列中的已更新值應(yīng)用于 @MyTableVar 表變量之前存在。

在它后面的兩個 SELECT 語句返回 @MyTableVar 中的值以及 T_ScrapReason 表中更新操作的結(jié)果。

DECLARE @MyTableVar TABLE (
    ID INT NOT NULL,
    OldNameInfo NVARCHAR(32),
    NewNameInfo NVARCHAR(32),
    ModifiedDate DATETIME);
  
UPDATE TOP (10) T_ScrapReason
SET NameInfo = N'豐田皇冠'
OUTPUT INSERTED.ID,
       DELETED.NameInfo,
       INSERTED.NameInfo,
       INSERTED.ModifiedDate
INTO @MyTableVar;
  
--Display the result set of the table variable.
SELECT ID,OldNameInfo,NewNameInfo, ModifiedDate FROM @MyTableVar;

--Display the result set of the table.
SELECT ID,UnitMeasureCode, NameInfo, ModifiedDate FROM T_ScrapReason;

10.4、在 UPDATE 語句中使用包含 from_table_name 的 OUTPUT INTO

以下示例針對 T_ScrapReason表更新NameInfo 列。 OUTPUT INTO 子句返回所更新表 (T_ScrapReason) 中的值以及 T_UnitMeasure 表中的值。

DECLARE @MyTestVar TABLE (
    ID INT NOT NULL,
    UnitMeasureCode NCHAR(8) NOT NULL,
    OldNameInfo NVARCHAR(32),
    NewNameInfo NVARCHAR(32),
    ProductName NVARCHAR(32)NOT NULL);
  
UPDATE T_ScrapReason
SET NameInfo = T2.NameInfo
OUTPUT DELETED.ID,
       DELETED.UnitMeasureCode,
       DELETED.NameInfo,
       INSERTED.NameInfo,
       T2.NameInfo
    INTO @MyTestVar
FROM T_ScrapReason AS T1
    INNER JOIN T_UnitMeasure AS T2 ON T2.UnitMeasureCode = T1.UnitMeasureCode;
  
SELECT ID, UnitMeasureCode, OldNameInfo,NewNameInfo, ProductName FROM @MyTestVar;

SELECT * FROM T_ScrapReason;

10.5、在 DELETE 語句中使用包含 from_table_name 的 OUTPUT INTO

以下示例將按照在 ProductProductPhoto 語句的 FROM 子句中所定義的搜索條件刪除 DELETE 表中的行。 OUTPUT 子句返回所刪除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。 在 FROM 子句中使用該表來指定要刪除的行。

DECLARE @MyTableVar TABLE (
    ID INT NOT NULL,
    UnitMeasureCode NCHAR(8) NOT NULL,
    NameInfo NVARCHAR(32),
	Code NCHAR(8) NOT NULL,
    ProductName NVARCHAR(32)NOT NULL);

DELETE T_ScrapReason
OUTPUT DELETED.ID,
	   DELETED.UnitMeasureCode,
	   DELETED.NameInfo,
       T2.UnitMeasureCode,
       T2.NameInfo      
    INTO @MyTableVar
FROM T_ScrapReason AS T1
    INNER JOIN T_UnitMeasure AS T2 ON T2.UnitMeasureCode = T1.UnitMeasureCode;;
  
--Display the results of the table variable.
SELECT ID, UnitMeasureCode, NameInfo,Code, ProductName FROM @MyTableVar;

SELECT * FROM T_ScrapReason;

10.6、在 INSTEAD OF 觸發(fā)器中使用 OUTPUT

下例在觸發(fā)器中使用 OUTPUT 子句來返回觸發(fā)器操作的結(jié)果。 首先，創(chuàng)建一個 ScrapReason 表的視圖，然后對該視圖定義 INSTEAD OF INSERT 觸發(fā)器，從而使用戶只修改基表的 Name 列。 由于 ScrapReasonID 列在基表中是 IDENTITY 列，因此觸發(fā)器忽略用戶提供的值。 這允許數(shù)據(jù)庫引擎自動生成正確的值。 同樣，用戶為 ModifiedDate 提供的值也被忽略并設(shè)置為正確的日期。 OUTPUT 子句返回實際插入 ScrapReason 表中的值。

CREATE TRIGGER dbo.TR_ScrapReason ON dbo.T_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
    --ID is not specified in the list of columns to be inserted
    --because it is an IDENTITY column.
    INSERT INTO T_ScrapReason (UnitMeasureCode,NameInfo,ModifiedDate)
    OUTPUT INSERTED.ID,
	    INSERTED.UnitMeasureCode,
        INSERTED.NameInfo,
        INSERTED.ModifiedDate
    SELECT UnitMeasureCode,NameInfo,GETDATE()
    FROM INSERTED;
END
GO

INSERT INTO T_ScrapReason (UnitMeasureCode,NameInfo,ModifiedDate)
VALUES (N'Car00001',N'寶馬X5','2024-08-03');
GO

10.7、插入從 OUTPUT 子句返回的數(shù)據(jù)

下面的示例捕獲從 OUTPUT 語句的 MERGE 子句返回的數(shù)據(jù)，并將這些數(shù)據(jù)插入另一個表。 MERGE 語句根據(jù)在 T_UnitMeasure 表中NameInfo列信息更新 T_ScrapReason 表的 NameInfo列。 本示例捕獲已刪除的行并將這些行插入另一個表 T_ZeroInventory中。

CREATE TABLE T_ZeroInventory (RemovedOnDate DATETIME,UnitMeasureCode NCHAR(8) NOT NULL);
GO

INSERT INTO T_ZeroInventory(RemovedOnDate,UnitMeasureCode)
SELECT GETDATE(),UnitMeasureCode
FROM (
    MERGE T_ScrapReason AS T1
    USING T_UnitMeasure AS T2
        ON (T1.UnitMeasureCode = T2.UnitMeasureCode)
    WHEN MATCHED THEN 
	  DELETE
    WHEN NOT MATCHED THEN
	  INSERT (UnitMeasureCode,NameInfo,ModifiedDate) VALUES (T2.UnitMeasureCode, T2.NameInfo,GETDATE())
    OUTPUT $ACTION,
        DELETED.UnitMeasureCode
    ) AS Changes(Action,UnitMeasureCode)
WHERE Action = 'DELETE';

IF @@ROWCOUNT = 0
    PRINT 'Warning: No rows were inserted';
GO

SELECT RemovedOnDate,UnitMeasureCode FROM T_ZeroInventory;
GO

相關(guān)內(nèi)容

DELETE (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
表 (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
sp_configure (Transact-SQL)

到此這篇關(guān)于SQLServer OUTPUT子句的具體使用的文章就介紹到這了,更多相關(guān)SQL OUTPUT子句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論