- 論壇徽章:
- 0
|
ql server 2005中output用法解析
一、關(guān)于output子句
SQL Server 2005中的output子句，可以使你從修改語(yǔ)句（INSERT、UPDATE、DELETE）中將數(shù)據(jù)返回到表變量中。帶結(jié)果的 DML 的有用方案包括清除和存檔、消息處理應(yīng)用程序以及其他方案。這一新的 OUTPUT 子句的語(yǔ)法為：
OUTPUT INTO @table_variable
可以通過(guò)引用插入的表和刪除的表來(lái)訪問(wèn)被修改的行的舊/新映像，其方式與訪問(wèn)觸發(fā)器類似。在 INSERT 語(yǔ)句中，只能訪問(wèn)插入的表。在 DELETE 語(yǔ)句中，只能訪問(wèn)刪除的表。在 UPDATE 語(yǔ)句中，可以訪問(wèn)插入的表和刪除的表。
二、實(shí)例說(shuō)明
1、將 OUTPUT INTO 用于簡(jiǎn)單 INSERT 語(yǔ)句
以下示例將行插入 ScrapReason 表，并使用 OUTPUT 子句將語(yǔ)句的結(jié)果返回到 @MyTableVar table 變量。由于 ScrapReasonID 列使用 IDENTITY 屬性定義，因此未在 INSERT 語(yǔ)句中為該列指定一個(gè)值。但請(qǐng)注意，將在列 INSERTED.ScrapReasonID 內(nèi)的 OUTPUT 子句中返回由數(shù)據(jù)庫(kù)引擎 為該列生成的值。
代碼
use AdventureWorks
go
--定義一個(gè)表格變量
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中的數(shù)據(jù)
select * from @mytablevar
--顯示Production.ScrapReason表中的數(shù)據(jù)
select * from Production.ScrapReason
go
以上語(yǔ)句中inserted是一個(gè)臨時(shí)表，當(dāng)我們往數(shù)據(jù)表中插入一條數(shù)據(jù)的時(shí)候數(shù)據(jù)庫(kù)會(huì)創(chuàng)建一個(gè)臨時(shí)表inserted保存插入的記錄；當(dāng)我們刪除某條記錄的時(shí)候，數(shù)據(jù)庫(kù)會(huì)創(chuàng)建一個(gè)臨時(shí)表deleted保存刪除的記錄。以上語(yǔ)句把新插入的數(shù)據(jù)填充到表變量@mytablevar中，然后輸出數(shù)據(jù)，可以看到@mytablevar中的記錄和Production.ScrapReason中新插入的數(shù)據(jù)是一樣的。
2、 將 OUTPUT 用于 INSERT…SELECT 語(yǔ)句
以下示例創(chuàng)建 EmployeeSales 表，然后通過(guò)使用 SELECT 語(yǔ)句檢索源表中的數(shù)據(jù)將幾行插入該表。同時(shí)，也計(jì)算了列 ProjectedSales 的值并將其插入該表中。OUTPUT 子句將 INSERT 語(yǔ)句的結(jié)果返回到執(zhí)行調(diào)用的應(yīng)用程序。最后的 SELECT 語(yǔ)句驗(yàn)證新 EmployeeSales 表的內(nèi)容是否與 OUTPUT 子句的結(jié)果匹配。
代碼
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 語(yǔ)句
以下示例將刪除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 語(yǔ)句的結(jié)果（即已刪除的行中的所有列）返回到執(zhí)行調(diào)用的應(yīng)用程序。后面的 SELECT 語(yǔ)句驗(yàn)證對(duì) ShoppingCartItem 表所執(zhí)行的刪除操作的結(jié)果。
USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;
--驗(yàn)證表中所有數(shù)據(jù)都被刪除
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
4、 將 OUTPUT INTO 用于 UPDATE
下面的示例將 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句將返回 VacationHours 值，該值在將列 DELETED.VacationHours 中的 UPDATE 語(yǔ)句和列 INSERTED.VacationHours 中的已更新值應(yīng)用于 @MyTableVar table 變量之前存在。
在它后面的兩個(gè) SELECT 語(yǔ)句返回 @MyTableVar 中的值以及 Employee 表中更新操作的結(jié)果。請(qǐng)注意，INSERTED.ModifiedDate 列中的結(jié)果與 Employee 表中的 ModifiedDate 列不具有相同的值。這是因?yàn)閷?duì) Employee 表定義了將 ModifiedDate 的值更新為當(dāng)前日期的 AFTER UPDATE 觸發(fā)器。但是，從 OUTPUT 中返回的列反映觸發(fā)器激發(fā)之前的數(shù)據(jù)。
代碼
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á)式
以下示例在示例 D 的基礎(chǔ)上生成，方法是通過(guò)將 OUTPUT 子句中的表達(dá)式定義為已更新的 VacationHours 值與應(yīng)用更新之前的 VacationHours 值之間的差異。該表達(dá)式的值返回到列 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;
--顯示表變量中的數(shù)據(jù)
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
6、 在 UPDATE 語(yǔ)句中使用包含 from_table_name 的 OUTPUT INTO
以下示例使用指定的 ProductID 和 ScrapReasonID，針對(duì) WorkOrder 表中的所有工作順序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表來(lái)指定要更新的行。由于 WorkOrder 表具有對(duì)其定義的 AFTER UPDATE 觸發(fā)器，因此需要 INTO 關(guān)鍵字。
代碼
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 語(yǔ)句中使用包含 from_table_name 的 OUTPUT INTO
以下示例將按照在 DELETE 語(yǔ)句的 FROM 子句中所定義的搜索條件刪除 ProductProductPhoto 表中的行。OUTPUT 子句返回所刪除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用該表來(lái)指定要?jiǎng)h除的行。
代碼
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 用于大型對(duì)象數(shù)據(jù)類型s
以下示例使用 .WRITE 子句更新 Production.Document 表內(nèi) DocumentSummary 這一 nvarchar(max) 列中的部分值。通過(guò)指定替換單詞、現(xiàn)有數(shù)據(jù)中要替換的單詞的開(kāi)始位置（偏移量）以及要替換的字符數(shù)（長(zhǎng)度），將單詞 components 替換為單詞 features。此示例使用 OUTPUT 子句將 DocumentSummary 列的前像和后像返回到 @MyTableVar table 變量。請(qǐng)注意，將返回 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 觸發(fā)器中使用 OUTPUT
以下示例在觸發(fā)器中使用 OUTPUT 子句返回觸發(fā)器操作的結(jié)果。首先，創(chuàng)建一個(gè) ScrapReason 表的視圖，然后對(duì)該視圖定義 INSTEAD OF INSERT 觸發(fā)器，從而使用戶只修改基表的 Name 列。由于列 ScrapReasonID 是基表中的 IDENTITY 列，因此觸發(fā)器將忽略用戶提供的值。這允許數(shù)據(jù)庫(kù)引擎 自動(dòng)生成正確的值。同樣，用戶為 ModifiedDate 提供的值也被忽略并設(shè)置為正確的日期。OUTPUT 子句返回實(shí)際插入 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 用于標(biāo)識(shí)列和計(jì)算列
下面的示例創(chuàng)建 EmployeeSales 表，然后使用 INSERT 語(yǔ)句向其中插入若干行，并使用 SELECT 語(yǔ)句從源表中檢索數(shù)據(jù)。EmployeeSales 表包含標(biāo)識(shí)列 (EmployeeID) 和計(jì)算列 (ProjectedSales)。由于這些值是在插入操作期間由數(shù)據(jù)庫(kù)引擎生成的，因此，不能在 @MyTableVar 中定義上述兩列。
代碼 |
|
免費(fèi)注冊(cè)
發(fā)表于 2011-03-09 19:50
作為語(yǔ)句終止符。如果運(yùn)行沒(méi)有終止符的 MERGE 語(yǔ)句，將引發(fā)錯(cuò)誤 10713。