Sql server 2005中output用法解析

kitesky發表於2013-05-21
SQLServer
SQL Server 2005中的output子句，可以使你從修改語句（INSERT、UPDATE、DELETE）中將資料返回到表變數中。帶結果的 DML 的有用方案包括清除和存檔、訊息處理應用程式以及其他方案。 [@more@]
一、關於output子句
SQL Server 2005中的output子句，可以使你從修改語句（INSERT、UPDATE、DELETE）中將資料返回到表變數中。帶結果的 DML 的有用方案包括清除和存檔、訊息處理應用程式以及其他方案。這一新的 OUTPUT 子句的語法為：
OUTPUT INTO @table_variable
可以透過引用插入的表和刪除的表來訪問被修改的行的舊/新映像，其方式與訪問觸發器類似。在 INSERT 語句中，只能訪問插入的表。在 DELETE 語句中，只能訪問刪除的表。在 UPDATE 語句中，可以訪問插入的表和刪除的表。
二、例項說明
1、將 OUTPUT INTO 用於簡單 INSERT 語句
以下示例將行插入 ScrapReason 表，並使用 OUTPUT 子句將語句的結果返回到 @MyTableVar table 變數。由於 ScrapReasonID 列使用 IDENTITY 屬性定義，因此未在 INSERT 語句中為該列指定一個值。但請注意，將在列 INSERTED.ScrapReasonID 內的 OUTPUT 子句中返回由資料庫引擎 為該列生成的值。
use AdventureWorks
go
--定義一個表格變數
declare @mytablevar table( ScrapReasonID smallint,
Name1 varchar(50),
ModifiedDate datetime)
insert into Production.ScrapReason
output inserted.ScrapReasonID,inserted.[Name],inserted.ModifiedDate into @mytablevar
values ('operator error',getdate());
--顯示@mytablevar中的資料
select * from @mytablevar
--顯示Production.ScrapReason表中的資料
select * from Production.ScrapReason
go
以上語句中inserted是一個臨時表，當我們往資料表中插入一條資料的時候資料庫會建立一個臨時表inserted儲存插入的記錄；當我們刪除某條記錄的時候，資料庫會建立一個臨時表deleted儲存刪除的記錄。以上語句把新插入的資料填充到表變數@mytablevar中，然後輸出資料，可以看到@mytablevar中的記錄和Production.ScrapReason中新插入的資料是一樣的。
2、 將 OUTPUT 用於 INSERT…SELECT 語句
以下示例建立 EmployeeSales 表，然後透過使用 SELECT 語句檢索源表中的資料將幾行插入該表。同時，也計算了列 ProjectedSales 的值並將其插入該表中。OUTPUT 子句將 INSERT 語句的結果返回到執行呼叫的應用程式。最後的 SELECT 語句驗證新 EmployeeSales 表的內容是否與 OUTPUT 子句的結果匹配。
use AdventureWorks
go
if object_id('dbo.EmployeeSales','u') is not null
drop table dbo.EmployeeSales
go
create table dbo.EmployeeSales
(
EmployeeID nvarchar(11) not null,
LastName nvarchar(20) not null,
FirstName nvarchar(20) not null,
CurrentSales money not null,
ProjectedSales money not null
)
go
insert into dbo.EmployeeSales
output inserted.EmployeeID,inserted.LastName,inserted.FirstName,inserted.CurrentSales,inserted.ProjectedSales
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
3、 將 OUTPUT 用於 DELETE 語句
以下示例將刪除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 語句的結果（即已刪除的行中的所有列）返回到執行呼叫的應用程式。後面的 SELECT 語句驗證對 ShoppingCartItem 表所執行的刪除操作的結果。
USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;
--驗證表中所有資料都被刪除
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
4、 將 OUTPUT INTO 用於 UPDATE
下面的示例將 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句將返回 VacationHours 值，該值在將列 DELETED.VacationHours 中的 UPDATE 語句和列 INSERTED.VacationHours 中的已更新值應用於 @MyTableVar table 變數之前存在。
在它後面的兩個 SELECT 語句返回 @MyTableVar 中的值以及 Employee 表中更新操作的結果。請注意，INSERTED.ModifiedDate 列中的結果與 Employee 表中的 ModifiedDate 列不具有相同的值。這是因為對 Employee 表定義了將 ModifiedDate 的值更新為當前日期的 AFTER UPDATE 觸發器。但是，從 OUTPUT 中返回的列反映觸發器激發之前的資料。
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--顯示@MyTableVar的值
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--顯示插入表的值
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
5、 使用 OUTPUT INTO 返回表示式
以下示例在示例 D 的基礎上生成，方法是透過將 OUTPUT 子句中的表示式定義為已更新的 VacationHours 值與應用更新之前的 VacationHours 值之間的差異。該表示式的值返回到列 VacationHoursDifference 中的 @MyTableVar table 變數。
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
VacationHoursDifference int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.VacationHours - DELETED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--顯示錶變數中的資料
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
6、 在 UPDATE 語句中使用包含 from_table_name 的 OUTPUT INTO
以下示例使用指定的 ProductID 和 ScrapReasonID，針對 WorkOrder 表中的所有工作順序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表來指定要更新的行。由於 WorkOrder 表具有對其定義的 AFTER UPDATE 觸發器，因此需要 INTO 關鍵字。
USE AdventureWorks;
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;
GO
7、在 DELETE 語句中使用包含 from_table_name 的 OUTPUT INTO
以下示例將按照在 DELETE 語句的 FROM 子句中所定義的搜尋條件刪除 ProductProductPhoto 表中的行。OUTPUT 子句返回所刪除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用該表來指定要刪除的行。
USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO
8、 將 OUTPUT INTO 用於大型物件資料型別s
以下示例使用 .WRITE 子句更新 Production.Document 表內 DocumentSummary 這一 nvarchar(max) 列中的部分值。透過指定替換單詞、現有資料中要替換的單詞的開始位置（偏移量）以及要替換的字元數（長度），將單詞 components 替換為單詞 features。此示例使用 OUTPUT 子句將 DocumentSummary 列的前像和後像返回到 @MyTableVar table 變數。請注意，將返回 DocumentSummary 列的全部前像和後像。
USE AdventureWorks;
GO
DECLARE @MyTableVar table (
DocumentID int NOT NULL,
SummaryBefore nvarchar(max),
SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
DELETED.DocumentSummary,
INSERTED.DocumentSummary
INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
9、 在 INSTEAD OF 觸發器中使用 OUTPUT
以下示例在觸發器中使用 OUTPUT 子句返回觸發器操作的結果。首先，建立一個 ScrapReason 表的檢視，然後對該檢視定義 INSTEAD OF INSERT 觸發器，從而使使用者只修改基表的 Name 列。由於列 ScrapReasonID 是基表中的 IDENTITY 列，因此觸發器將忽略使用者提供的值。這允許資料庫引擎 自動生成正確的值。同樣，使用者為 ModifiedDate 提供的值也被忽略並設定為正確的日期。OUTPUT 子句返回實際插入 ScrapReason 表中的值。
UUSE AdventureWorks;
GO
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL
DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS (SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason);
GO
CREATE TRIGGER dbo.io_ScrapReason
ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted
--because it is an IDENTITY column.
INSERT INTO Production.ScrapReason (Name, ModifiedDate)
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
INSERTED.ModifiedDate
SELECT Name, getdate()
FROM inserted;
END
GO
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
VALUES (99, N'My scrap reason','20030404');
GO
10、 將 OUTPUT INTO 用於標識列和計算列
下面的示例建立 EmployeeSales 表，然後使用 INSERT 語句向其中插入若干行，並使用 SELECT 語句從源表中檢索資料。EmployeeSales 表包含標識列 (EmployeeID) 和計算列 (ProjectedSales)。由於這些值是在插入操作期間由資料庫引擎生成的，因此，不能在 @MyTableVar 中定義上述兩列。
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID int IDENTITY (1,5)NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar table(
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66009/viewspace-1060527/，如需轉載，請註明出處，否則將追究法律責任。

相關文章