SQL?Server中帶有OUTPUT子句的INSERT,DELETE,UPDATE應(yīng)用
OUTPUT是SQL SERVER2005的新特性，可以從數(shù)據(jù)修改語句中返回輸出,可以看作是"返回結(jié)果的DML"。
INSERT、DELETE、UPDATE均支持OUTPUT子句。
在OUTPUT子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表與在觸發(fā)器中使用的非常相似。
在INSERT,DELETE,UPDATE中OUTPUT的區(qū)別
- 對(duì)于INSERT，可以引用inserted表以查詢新行的屬性。
- 對(duì)于DELETE，可以引用deleted表以查詢舊行的屬性。
- 對(duì)于UPDATE，使用deleted表查詢被更新行在更改前的屬性,用inserted表標(biāo)識(shí)被更新行在更改后的值。
輸出方式:
- 輸出給調(diào)用方(客戶端應(yīng)用程序)
- 輸出給表
一、應(yīng)用:
1、帶有OUTPUT的INSERT的應(yīng)用
對(duì)于包含自增列的表執(zhí)行多行insert語句,同時(shí)想知道新的標(biāo)識(shí)值時(shí),在INSERT中使用OUTPUT子句非常方便。
1、對(duì)于單行INSERT語句，這不成問題:SCOPE_IDENTITY函數(shù)即可實(shí)現(xiàn)。SCOPE_IDENTITY函數(shù)：返回為當(dāng)前會(huì)話和當(dāng)前作用域中的任何表最后生成的標(biāo)識(shí)值。
-- Generating Surrogate Keys for Customers
USE tempdb;
GO
IF OBJECT_ID('dbo.CustomersDim') IS NOT NULL
DROP TABLE dbo.CustomersDim;
GO
CREATE TABLE dbo.CustomersDim
(
KeyCol INT NOT NULL IDENTITY PRIMARY KEY,
CustomerID NCHAR(5) NOT NULL,
CompanyName NVARCHAR(40) NOT NULL,
);
-- Insert New Customers and Get their Surrogate Keys
DECLARE @NewCusts TABLE
(
CustomerID NCHAR(5) NOT NULL PRIMARY KEY,
KeyCol INT NOT NULL UNIQUE
);
INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)
OUTPUT inserted.CustomerID, inserted.KeyCol INTO @NewCusts
-- OUTPUT inserted.CustomerID, inserted.KeyCol
SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Country = N'UK';
SELECT CustomerID, KeyCol FROM @NewCusts;
GO注意代碼中被注釋掉的第二個(gè)OUTPUT子句，后面沒有INTO子句。如果還要輸出返回給調(diào)用方，取消注釋即可。這樣INSERT語句將包含兩個(gè)OUTPUT子句。
2、多行INSERT語句
USE AdventureWorks;
GO
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
SELECT * FROM @TmpTable
SELECT * FROM TestTable
DROP TABLE TestTable
GO2、帶有OUTPUT的DELETE的應(yīng)用.
如果要?jiǎng)h除數(shù)據(jù)的同時(shí)，還需要記錄日志，或者歸檔數(shù)據(jù)，在DELETE中使用OUTPUT子句在適合不過了。
USE AdventureWorks; GO CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100)) DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100)) INSERT TestTable (ID, TEXTVal) VALUES (1,'FirstVal') INSERT TestTable (ID, TEXTVal) VALUES (2,'SecondVal') DELETE FROM TestTable OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable WHERE ID IN (1,2) SELECT * FROM @TmpTable SELECT * FROM TestTable DROP TABLE TestTable GO
3、帶有OUTPUT的UPDATE的應(yīng)用
USE AdventureWorks; GO CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100)) DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100)) INSERT TestTable (ID, TEXTVal) VALUES (1,'FirstVal') INSERT TestTable (ID, TEXTVal) VALUES (2,'SecondVal') UPDATE TestTable SET TEXTVal = 'NewValue' OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable WHERE ID IN (1,2) SELECT * FROM @TmpTable SELECT * FROM TestTable DROP TABLE TestTable GO
4、在 UPDATE 語句中使用包含 from_table_name 的 OUTPUT INTO
以下示例使用指定的ProductID和ScrapReasonID，針對(duì)WorkOrder表中的所有工作順序更新ScrapReasonID列。
OUTPUT INTO子句返回所更新表 (WorkOrder) 中的值以及Product表中的值。 在Product子句中使用FROM表來指定要更新的行。
由于WorkOrder表上定義了AFTER UPDATE觸發(fā)器，因此需要INTO關(guān)鍵字。
USE AdventureWorks2012;
GO
DECLARE @MyTestVar TABLE (
OldScrapReasonID INT NOT NULL,
NewScrapReasonID INT NOT NULL,
WorkOrderID INT NOT NULL,
ProductID INT NOT NULL,
ProductName NVARCHAR(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT deleted.ScrapReasonID,
inserted.ScrapReasonID,
inserted.WorkOrderID,
inserted.ProductID,
p.Name
INTO @MyTestVar
FROM Production.WorkOrder AS wo
INNER JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
AND wo.ScrapReasonID= 16
AND p.ProductID = 733;
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
ProductID, ProductName
FROM @MyTestVar;
GO4、MERGE語句
下面的示例捕獲從OUTPUT語句的MERGE子句返回的數(shù)據(jù)，并將這些數(shù)據(jù)插入另一個(gè)表。
MERGE語句每天根據(jù)在Quantity表中處理的訂單更新ProductInventory表的SalesOrderDetail列。 如果產(chǎn)品的庫(kù)存降至0或更低，它還會(huì)刪除與這些產(chǎn)品對(duì)應(yīng)的行。
本示例捕獲已刪除的行并將這些行插入另一個(gè)表ZeroInventory中，該表跟蹤沒有庫(kù)存的產(chǎn)品。
USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = '20070401'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $action, deleted.ProductID)
AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;二、使用OUTPUT子句的注意事項(xiàng)：
以下語句中不支持 OUTPUT 子句：
- 引用本地分區(qū)視圖、分布式分區(qū)視圖或遠(yuǎn)程表的 DML 語句。
- 包含 EXECUTE 語句的 INSERT 語句。
- 不能將 OUTPUT INTO 子句插入視圖或行集函數(shù)。
- 參數(shù)或變量作為 UPDATE 語句的一部分進(jìn)行了修改，則 OUTPUT 子句將始終返回語句執(zhí)行之前的參數(shù)或變量的值而不是已修改的值
三、C#中使用cmd.ExecuteScalar（單列）、cmdExecuteReader（多行或多列）
返回單列：
using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
{
cmd.Parameters.AddWithValue("@na", Mem_NA);
cmd.Parameters.AddWithValue("@occ", Mem_Occ);
con.Open();
int modified =(int)cmd.ExecuteScalar();
if (con.State == System.Data.ConnectionState.Open)
con.Close();
return modified;
}返回多行或者多列：
create table Suspension (pkey int not null identity(1, 1),
pallet_position int,
processing_pallet_pkey int,
datetime_created datetime,
datetime_updated datetime,
[this.created_by] int,
[this.updated_by] int);
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
const string insertQuery = @"
INSERT INTO dbo.Suspension
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
[this.created_by], [this.updated_by])
OUTPUT INSERTED.pkey VALUES
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2);";
// 通過數(shù)據(jù)庫(kù)
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
using (var insertedOutput = cmd.ExecuteReader())
{
dt.Load(insertedOutput);
}
Console.WriteLine(dt.Rows.Count); // 4
// 通過手工讀取
var list = new List<int>();
using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
using (var insertedOutput = cmd.ExecuteReader())
{
while(insertedOutput.Read())
{
list.Add(insertedOutput.GetInt32(0));
}
}
Console.WriteLine(list.Count); // 4
// 通過dapper
var ids = conn.Query<int>(insertQuery).ToList();
Console.WriteLine(ids.Count); // 4
}四、參考：
https://docs.microsoft.com/zh-cn/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持本站。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信