SQL SERVER 2005中全新的OUTPUT子句添加数据记录详解(理论篇)

2019-11-03 08:35:14

字体：大中小

来源：转载

供稿：网友

也许大家在数据库开发的时候，会发现这样一个现象：添加数据记录后想查看其结果都必须通过select表达式来查询实现。一定都要多此一举才可以看到被添加的数据记录吗？答案是否定的。SQL SERVER 2005新提供的OUTPUT子句就帮您解决这个难题，它以比触发器更简洁的方式，在添加数据记录的同时或者事后显示所添加的数据记录内容。下面是作者通过查找帮助文档MSDN和章立民老师的《SQL Server 2005数据库开发实战》等资料后总结如下:

理论篇

OUTPUT子句返回受 INSERT、UPDATE 或 DELETE 语句影响的每行的信息，或者返回基于上述每行的表达式。这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。此外，也可以将结果插入表或表变量。

用于：

DELETE

INSERT

UPDATE

Transact-SQL 语法约定

语法

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

备注
OUTPUT <dml_select_list> 子句和 OUTPUT <dml_select_list> INTO { @table_variable | output_table } 子句可以在单个 INSERT、UPDATE 或 DELETE 语句中定义。

注意：
除非另行指定，否则，对 OUTPUT 子句的引用将同时引用 OUTPUT 子句和 OUTPUT INTO 子句。

OUTPUT 子句对于在 INSERT 或 UPDATE 操作之后检索标识列或计算列的值可能非常有用。

当 <dml_select_list> 中包含计算列时，输出表或表变量中的相应列并不是计算列。新列中的值是在执行该语句时计算出的值。

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

引用本地分区视图、分布式分区视图或远程表的 DML 语句。
包含 EXECUTE 语句的 INSERT 语句。
不能将 OUTPUT INTO 子句插入视图或行集函数。

无法保证将更改应用于表的顺序与将行插入输出表或表变量的顺序相对应。

如果将参数或变量作为 UPDATE 语句的一部分进行了修改，则 OUTPUT 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值。

在使用 WHERE CURRENT OF 语法通过游标定位的 UPDATE 或 DELETE 语句中，可以使用 OUTPUT。

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

数据类型
OUTPUT 子句支持下列大型对象数据类型：nvarchar(max)、varchar(max)、varbinary(max)、 text、ntext、image 和 xml。当在 UPDATE 语句中使用 .WRITE 子句修改 nvarchar(max)、varchar(max) 或 varbinary(max) 列时，如果引用了值的全部前像和后像，则将其返回。在 OUTPUT 子句中，TEXTPTR( ) 函数不能作为 text、ntext 或 image 列的表达式的一部分出现。

队列
可以在将表用作队列或将表用于保持中间结果集的应用程序中使用 OUTPUT。换句话说，应用程序不断地在表中添加或删除行。以下示例在 DELETE 语句中使用 OUTPUT 子句将已删除的行返回到执行调用的应用程序。

  复制代码
USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

此示例从用作队列的表中删除一行，并使用单个操作将已删除的值返回到处理应用程序。还可实现其他语义，例如使用表来实现堆栈。但是，SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。应用程序负责包括可保证所需语义的适当 WHERE 子句，或者理解当针对 DML 操作可能限定多行时，没有保证的顺序。以下示例使用子查询，并假定 DatabaseLogID 列具有唯一性特征才能实现所需的排序语义。

  复制代码
USE AdventureWorks;
GO
DELETE dbo.DatabaseLog
OUTPUT deleted.*
WHERE DatabaseLogID IN (SELECT TOP(5) DatabaseLogID FROM dbo.DatabaseLog ORDER BY PostTime);
GO

注意：
如果您的方案允许多个应用程序从一个表中执行析构性读取，请在 UPDATE 和 DELETE 语句中使用 READPAST 表提示。这可防止在其他应用程序已经读取表中第一个限定记录的情况下出现锁定问题。

参数
@table_variable
指定一个 table 变量，返回的行将插入该变量中而不是返回到调用方。@table_variable 必须在 INSERT、UPDATE 或 DELETE 语句之前声明。

如果未指定 column_list，则 table 变量必须与 OUTPUT 结果集具有相同的列数。标识列和计算列除外，这两种列必须跳过。如果指定了 column_list，则任何省略的列都必须允许空值，或者都分配有默认值。

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

如果未指定 column_list，则表必须与 OUTPUT 结果集具有相同的列数。标识列和计算列例外，必须跳过这两种列。如果指定了 column_list，则任何省略的列都必须允许空值，或者都分配有默认值。

output_table 无法应用于以下情况：

具有启用的对其定义的触发器。
参与到外键约束双方的任意一方。
具有 CHECK 约束或启用的规则。
column_list
INTO 子句目标表上列名的可选列表。它类似于 INSERT 语句中允许使用的列列表。

scalar_expression
可取计算结果为单个值的任何符号和运算符的组合。只要子查询返回单个值，便可使用子查询。scalar_expression 中不允许使用聚合函数。

对修改的表中的列的任何引用都必须使用 INSERTED 或 DELETED 前缀限定。

column_alias_identifier
用于引用列名的替代名称。

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

不能在 INSERT 语句中同时使用 DELETED 与 OUTPUT 子句。

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

INSERTED 语句不能与 DELETE 语句的 OUTPUT 子句同时使用。

from_table_name
列的前缀，指定 DELETE 语句或 UPDATE 语句（用于指定要更新或删除的行）的 FROM 子句中所包含的表。

如果还在 FROM 子句中指定了要修改的表，则对该表中的列的任何引用都必须使用 INSERTED 或 DELETED 前缀限定。

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

例如，以下 DELETE 语句中的 OUTPUT DELETED.* 将返回 ShoppingCartItem 表中所有已删除的列：

  复制代码
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name
显式列引用。任何对修改的表的引用都必须使用相应的 INSERTED 或 DELETED 前缀正确限定，例如：INSERTED.column_name。

权限
要求对通过 <dml_select_list> 检索的任何列或者在 <scalar_expression> 中使用的任何列具有 SELECT 权限。

要求对 <output_table> 中指定的任何表具有 INSERT 权限。

上一篇：SQL Server和Oracle的常用函数对比

下一篇：SQL注入漏洞全接触--入门篇