c语言中的output用法,OUTPUT 子句

返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。

OUTPUT子句的基本原理

1、返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT子句可以在客户端应用程序中返回这些记录行,然后将它们插入到一个持久的或临时的表中,也可以将记录插入到一个表变量中,或直接返回到处理应用程序,也就是用于输出。

2、OUTPUT子句的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。

3、OUTPUT子句中可以引用INSERTED或DELETED虚拟表,这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据,这跟使用触发器去修改数据的操作是很相似的。

4、不能在一个INSERT语句中引用DELETED,也不能在一个DELETED语句中引用INSERTED,因为这些虚拟表在这两种情况下逻辑上是没有意义的,所以SQL Server不会去创建。

5、对于具有 OUTPUT 子句的 UPDATE、INSERT 或 DELETE 语句,即使在遇到错误需要回滚时,也会将行返回到客户端。 如果在运行语句的过程中出现任何错误,都不应使用该结果。

语法

::=

{

[OUTPUT INTO {@table_variable|output_table} [(column_list)]]

[OUTPUT ]

}

::=

{ | scalar_expression } [ [AS] column_alias_identifier ]

[ ,...n ]

::=

{ DELETED | INSERTED | from_table_name } . { * | column_name }

| $action使用OUTPUT INTO子句的话,是把返回的结果插入到表变量(@table_variable)或表(output_table)中。

使用OUTPUT子句的话,就是直接把结果直接返回到处理应用程序。

@table_variable必须先声明才能插入、 更新、 DELETE 或 MERGE 语句。

output_table指定一个表,返回的行将被插入该表中而不是返回到调用方。output_table可能是临时表。

output_table不能︰

具有启用的对其定义的触发器。

参与 FOREIGN KEY 约束的任意一方。

具有 CHECK 约束或启用的规则。

column_list

INTO 子句目标表(上面的@table_variable | output_table)上列名的可选列表。如果column_list未指定,表变量/表必须具有和输出结果集相同的列数。标识列和计算列除外,这两种列必须跳过。

scalar_expression:可取计算结果为单个值的任何符号和运算符的组合。

column_alias_identifier:列别名。指定受删除、插入或更新操作影响的所有列都将按照它们在表中的顺序返回。

在OUTPUT子句中,可以引用特殊表INSERTED和DELETED,这与在触发器中使用临时表inserted和deleted非常相似。但是OUTPUT引用的INSERTED、DELETED和触发器产生的是不一样的。

DELETED:指定由更新或删除操作删除的值的列前缀。 以 DELETED 为前缀的列反映了 UPDATE、DELETE 或 MERGE 语句完成之前的值。

INSERTED:列的前缀,指定由插入操作或更新操作添加的值。 以 INSERTED 为前缀的列反映了在 UPDATE、INSERT 或 MERGE 语句完成之后但在触发器执行之前的值。

from_table_name:是一个列前缀,还可以把FROM 子句中包含的表的值OUTPUT INTO到表或表变量中。如: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;$action:仅可用于 MERGE 语句。在 MERGE 语句的 OUTPUT 子句中指定一个 nvarchar(10) 类型的列,该子句为每行返回以下三个值一:'INSERT'、'UPDATE' 或 'DELETE',返回哪个值取决于对该行执行的操作。

不支持 OUTPUT 子句

1、引用本地分区视图、分布式分区视图或远程表的 DML 语句。

2、包含 EXECUTE 语句的 INSERT 语句。

3、当数据库兼容级别设为 100 时,不允许在 OUTPUT 子句中使用全文谓词。

4、不能将 OUTPUT INTO 子句插入视图或行集函数。

5、如果用户定义的函数包含一个以表为目标的 OUTPUT INTO 子句,则不能创建该函数。

将从 OUTPUT 子句返回的数据插入表

在捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果并将这些结果插入目标表时,请牢记以下信息:整个操作是原子的。INSERT 语句和包含 OUTPUT 子句的嵌套 DML 语句要么都执行,要么整个语句都失败。

以下限制适用于外层 INSERT 语句的目标:

目标不能为远程表、视图或公用表表达式。

目标不能有 FOREIGN KEY 约束,或者被 FOREIGN KEY 约束所引用。

不能对目标定义触发器。

目标不能参与合并复制或事务复制的可更新订阅。

在包含 子句的 INSERT 语句中不支持 OUTPUT INTO 子句,只能使用OUTPUT语句。如:

INSERT INTO Production.ScrapReason (Name, ModifiedDate)

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,

INSERTED.ModifiedDate

SELECT Name, getdate()

FROM INSERTED;@@ROWCOUNT 返回仅由外层 INSERT 语句插入的行。

在 子句中,SELECT 和 WHERE 子句不能包括子查询、聚合函数、排名函数、全文谓词、执行数据访问的用户定义函数或是 TEXTPTR 函数

使用包含 OUTPUT 的 DML 触发器

从 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 子句将导致该语句失败。

队列

SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。应用程序负责包括可保证所需语义的适当 WHERE 子句,或者理解当针对 DML 操作可能限定多行时,没有保证的顺序。以下示例使用子查询,并假定 DatabaseLogID 列具有唯一性特征才能实现所需的排序语义。USE tempdb;

GO

CREATE TABLE dbo.table1

(

id INT,

employee VARCHAR(32)

)

go

INSERT INTO dbo.table1 VALUES

(1, 'Fred')

,(2, 'Tom')

,(3, 'Sally')

,(4, 'Alice');

GO

DECLARE @MyTableVar TABLE

(

id INT,

employee VARCHAR(32)

);

PRINT 'table1, before delete'

SELECT * FROM dbo.table1;

DELETE FROM dbo.table1

OUTPUT DELETED.* INTO @MyTableVar

WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'

SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'

SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results

--table1, before delete

--id employee

------------- ------------------------------

--1 Fred

--2 Tom

--3 Sally

--4 Alice

--

--table1, after delete

--id employee

------------- ------------------------------

--1 Fred

--3 Sally

--@MyTableVar, after delete

--id employee

------------- ------------------------------

--2 Tom

--4 Alice

【示例】

A、使用 OUTPUT INTO 返回表达式

USE AdventureWorks2008R2;

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,

ModifiedDate = GETDATE()

OUTPUT inserted.BusinessEntityID,

deleted.VacationHours,

inserted.VacationHours,

-- scalar_expression

inserted.VacationHours - deleted.VacationHours,

inserted.ModifiedDate

INTO @MyTableVar;

B、使用包含 from_table_name 的 OUTPUT INTOUSE AdventureWorks2008R2;

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;

C、将 OUTPUT INTO 用于大型对象数据类型

以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置(偏移量)以及要替换的字符数(长度),将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVartable 变量。

USE AdventureWorks2008R2;

GO

DECLARE @MyTableVar table (

SummaryBefore nvarchar(max),

SummaryAfter nvarchar(max));

UPDATE Production.Document

SET DocumentSummary .WRITE (N'features',28,10)

OUTPUT deleted.DocumentSummary,

inserted.DocumentSummary

INTO @MyTableVar

WHERE Title = N'Front Reflector Bracket Installation';

D、将 OUTPUT INTO 用于标识列和计算列

下例创建了 EmployeeSales 表,然后使用 INSERT 语句向该表中插入若干行,其中 SELECT 语句用来从源表中检索数据。EmployeeSales表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。由于这些值是在插入操作期间由 SQL Server 数据库引擎生成的,因此,不能在 @MyTableVar中定义上述两列。USE AdventureWorks2008R2 ;

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 Sales.SalesPerson AS sp

INNER JOIN Person.Person AS c

ON sp.BusinessEntityID = c.BusinessEntityID

WHERE sp.BusinessEntityID LIKE '2%'

ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales

FROM @MyTableVar;

GO

E、在单个语句中使用 OUTPUT 和 OUTPUT INTO

以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除ProductProductPhoto表中的行。OUTPUT INTO 子句将被删除表中的列(deleted.ProductID、deleted.ProductPhotoID)及Product表中的列返回给@MyTableVartable变量。

OUTPUT 子句将 ProductProductPhoto 表中的 deleted.ProductID、deleted.ProductPhotoID 列以及行的删除日期和时间返回到执行调用的应用程序。

USE AdventureWorks2008R2;

GO

DECLARE @MyTableVar table (

ProductID int NOT NULL,

ProductName nvarchar(50)NOT NULL,

ProductModelID int NOT NULL,

PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto

-- 用于将被删除的列返回给@MyTableVartable变量

OUTPUT DELETED.ProductID,

p.Name,

p.ProductModelID,

DELETED.ProductPhotoID

INTO @MyTableVar

-- 用于把指定信息信息返回到执行调用的应用程序

OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate

FROM Production.ProductProductPhoto AS ph

JOIN Production.Product as p

ON ph.ProductID = p.ProductID

WHERE p.ProductID BETWEEN 800 and 810;

GO

F、插入从 OUTPUT 子句返回的数据

下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据,并将这些数据插入另一个表。CREATE TABLE table1 (id INT, employee VARCHAR(32));

CREATE TABLE table2(id INT,person VARCHAR(32));

GO

INSERT INTO table1

SELECT a.id,a.person FROM (

INSERT table2

OUTPUT INSERTED.id,INSERTED.person

VALUES(1,'Ada')

) AS a

GO

G、在 INSTEAD OF 触发器中使用 OUTPUT

下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。首先,创建一个 ScrapReason 表的视图,然后对该视图定义 INSTEAD OF INSERT 触发器,从而使用户只修改基表的 Name 列。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

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

-- inserted表是触发器执行的时候临时产生的

-- INSERTED表是DML操作(这里为INSERT)完成之后产生的

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

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值