你的位置:首页 > 数据库

[数据库]说一下output子句


Output子句日常灰常有用,而且用的地方也挺多,但是确好多时候被我们忽视,今天我就也简单扫盲一下这个语句的用法。

Output子句

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

下面做下这4种类型的output 用法

先搞个测试表

CREATE TABLE [dbo].[AAA1]([ID] [int] PRIMARY KEY,[Col2] VARCHAR(100)) ON [PRIMARY]GOCREATE TABLE [dbo].[AAA2]([ID] [int] PRIMARY KEY,[Col2] VARCHAR(100)) ON [PRIMARY]GO

1、 insert 

INSERT INTO dbo.AAA1    ( ID, Col2 )OUTPUT Inserted.ID,Inserted.Col2 INTO AAA2(ID,Col2)VALUES ( 4,'1' )

这样就可以在插入AAA1 的同时将插入的结果输出插入到 AAA2 里面。

我想到有2个常用的场景

1、有些功能想要写入记录的时候也同时插入一个记录表来记录操作,很多时候会想起触发器。如果只是如此单纯的操作,那么真还不如使用一句output来得实惠了。但是这个也看具体场景,不扯太远。

2、当我们单条插入的时候,要捕获ID的话还可以使用  SCOPE_IDENTITY() 来获取,但是如果批量的时候,要获取插入的自增列对应的列,就可以使用OutPut 来捕获了~

 

2、update 

UPDATE AAA1 SET col2 = 'BB'  OUTPUT Deleted.ID,Deleted.Col2,Inserted.ID,Inserted.Col2  WHERE ID = 1

在update 里面呢,就会存在有 Deleted 和 inserted 2个临时表,这个就类似于 触发器里面的 deleted表和 inserted 表了。可以捕捉到更新前后的值

 

3、deleted 

DELETE FROM dbo.AAA1  OUTPUT Deleted.ID,Deleted.Col2  WHERE ID = 1

delete 也就是差不多,语法是一样的。

 

4、 Merge 

MERGE dbo.AAA1 AS TARUSING (SELECT 1,'a') AS SOUR(ID,Col2)ON 1 = 0WHEN NOT MATCHED THEN INSERT (ID,Col2) VALUES (SOUR.ID,SOUR.Col2)OUTPUT $action,Deleted.*,Inserted.*;

Merge就有一个独特一点的 $Action 的东东,这个的值会表示它的行动,有 'INSERT','UPDATE','DELETE' 3种动作。对于要捕捉在Merge里面的变化就很好用啦~

Merge还有一点比较好用,可以把没有插入到目标的列,也带到Output里面来~这个就可以清晰的看到每一行数据的对应情况了

 

但是Output虽好,但是还是会有一些限制,有些我遇到过,有些还没测试过。(以下内容出自联机文档)

  • 整个操作是原子的。 INSERT 语句和包含 OUTPUT 子句的嵌套 DML 语句要么都执行,要么整个语句都失败。

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

    • 目标不能为远程表、视图或公用表表达式。 (这个好理解,这个是指insert 的对象,并非指 output into 的对象)

    • 目标不能有 FOREIGN KEY 约束,或者被 FOREIGN KEY 约束所引用。 (就是output into 的目标表不能带外键)

    • 不能对目标定义触发器。

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

  • 对于嵌套的 DML 语句有以下限制:

    • 目标不能为远程表或分区视图。

    • 源本身不能包含 <dml_table_source> 子句。

  • 在包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句。

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

  • @@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 仅返回由嵌套的 DML 语句生成的标识值,而不返回由外层 INSERT 语句生成的标识值。

  • 查询通知将语句作为单个实体进行处理,并且即使重大更改是来自外层 INSERT 语句本身,所创建的任何消息的类型也将是嵌套 DML 的类型。

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

 

其它东西~遇到了在继续补充