我要评论output是sql server2005的新特性，可以从数据修改语句中返回输出,可以看作是"返回结果的dml"。
insert、delete、update均支持output子句。
在output子句中,可以引用特殊表inserted和deleted，使用inserted和deleted表与在触发器中使用的非常相似。
在insert,delete,update中output的区别
- 对于insert，可以引用inserted表以查询新行的属性。
- 对于delete，可以引用deleted表以查询旧行的属性。
- 对于update，使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值。
输出方式:
- 输出给调用方(客户端应用程序)
- 输出给表
一、应用:
1、带有output的insert的应用
对于包含自增列的表执行多行insert语句,同时想知道新的标识值时,在insert中使用output子句非常方便。
1、对于单行insert语句，这不成问题:scope_identity函数即可实现。scope_identity函数：返回为当前会话和当前作用域中的任何表最后生成的标识值。
-- generating surrogate keys for customers
use tempdb;
go
if object_id('dbo.customersdim') is not null
drop table dbo.customersdim;
go
create table dbo.customersdim
(
keycol int not null identity primary key,
customerid nchar(5) not null,
companyname nvarchar(40) not null,
);
-- insert new customers and get their surrogate keys
declare @newcusts table
(
customerid nchar(5) not null primary key,
keycol int not null unique
);
insert into dbo.customersdim(customerid, companyname)
output inserted.customerid, inserted.keycol into @newcusts
-- output inserted.customerid, inserted.keycol
select customerid, companyname from northwind.dbo.customers where country = n'uk';
select customerid, keycol from @newcusts;
go注意代码中被注释掉的第二个output子句，后面没有into子句。如果还要输出返回给调用方，取消注释即可。这样insert语句将包含两个output子句。
2、多行insert语句
use adventureworks;
go
create table testtable (id int, textval varchar(100))
declare @tmptable table (id int, textval varchar(100))
insert testtable (id, textval)
output inserted.id, inserted.textval into @tmptable
values (1,'firstval')
insert testtable (id, textval)
output inserted.id, inserted.textval into @tmptable
values (2,'secondval')
select * from @tmptable
select * from testtable
drop table testtable
go2、带有output的delete的应用.
如果要删除数据的同时，还需要记录日志，或者归档数据，在delete中使用output子句在适合不过了。
use adventureworks; go create table testtable (id int, textval varchar(100)) declare @tmptable table (id int, textval varchar(100)) insert testtable (id, textval) values (1,'firstval') insert testtable (id, textval) values (2,'secondval') delete from testtable output deleted.id, deleted.textval into @tmptable where id in (1,2) select * from @tmptable select * from testtable drop table testtable go
3、带有output的update的应用
use adventureworks; go create table testtable (id int, textval varchar(100)) declare @tmptable table (id_new int, textval_new varchar(100),id_old int, textval_old varchar(100)) insert testtable (id, textval) values (1,'firstval') insert testtable (id, textval) values (2,'secondval') update testtable set textval = 'newvalue' output inserted.id, inserted.textval, deleted.id, deleted.textval into @tmptable where id in (1,2) select * from @tmptable select * from testtable drop table testtable go
4、在 update 语句中使用包含 from_table_name 的 output into
以下示例使用指定的 productid 和 scrapreasonid，针对 workorder 表中的所有工作顺序更新 scrapreasonid 列。
output into 子句返回所更新表 (workorder) 中的值以及 product 表中的值。 在 product 子句中使用 from 表来指定要更新的行。
由于 workorder 表上定义了 after update 触发器，因此需要 into 关键字。
use adventureworks2012;
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;
go4、merge语句
下面的示例捕获从 output 语句的 merge 子句返回的数据，并将这些数据插入另一个表。
merge 语句每天根据在 quantity 表中处理的订单更新 productinventory 表的 salesorderdetail 列。 如果产品的库存降至 0 或更低，它还会删除与这些产品对应的行。
本示例捕获已删除的行并将这些行插入另一个表 zeroinventory 中，该表跟踪没有库存的产品。
use adventureworks2012;
go
if object_id(n'production.zeroinventory', n'u') is not null
drop table production.zeroinventory;
go
--create zeroinventory table.
create table production.zeroinventory (deletedproductid int, removedondate datetime);
go
insert into production.zeroinventory (deletedproductid, removedondate)
select productid, getdate()
from
( merge production.productinventory as pi
using (select productid, sum(orderqty) from sales.salesorderdetail as sod
join sales.salesorderheader as soh
on sod.salesorderid = soh.salesorderid
and soh.orderdate = '20070401'
group by productid) as src (productid, orderqty)
on (pi.productid = src.productid)
when matched and pi.quantity - src.orderqty <= 0
then delete
when matched
then update set pi.quantity = pi.quantity - src.orderqty
output $action, deleted.productid)
as changes (action, productid)
where action = 'delete';
if @@rowcount = 0
print 'warning: no rows were inserted';
go
select deletedproductid, removedondate from production.zeroinventory;二、使用output子句的注意事项：
以下语句中不支持 output 子句：
- 引用本地分区视图、分布式分区视图或远程表的 dml 语句。
- 包含 execute 语句的 insert 语句。
- 不能将 output into 子句插入视图或行集函数。
- 参数或变量作为 update 语句的一部分进行了修改，则 output 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值
三、c#中使用cmd.executescalar（单列）、cmdexecutereader（多行或多列）
返回单列：
using(sqlcommand cmd=new sqlcommand("insert into mem_basic(mem_na,mem_occ) output inserted.id values(@na,@occ)",con))
{
cmd.parameters.addwithvalue("@na", mem_na);
cmd.parameters.addwithvalue("@occ", mem_occ);
con.open();
int modified =(int)cmd.executescalar();
if (con.state == system.data.connectionstate.open)
con.close();
return modified;
}返回多行或者多列：
create table suspension (pkey int not null identity(1, 1),
pallet_position int,
processing_pallet_pkey int,
datetime_created datetime,
datetime_updated datetime,
[this.created_by] int,
[this.updated_by] int);
using (var conn = new sqlconnection(connectionstring))
{
conn.open();
const string insertquery = @"
insert into dbo.suspension
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
[this.created_by], [this.updated_by])
output inserted.pkey values
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2),
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2);";
// 通过数据库
datatable dt = new datatable();
using (sqlcommand cmd = new sqlcommand(insertquery, conn))
using (var insertedoutput = cmd.executereader())
{
dt.load(insertedoutput);
}
console.writeline(dt.rows.count); // 4
// 通过手工读取
var list = new list<int>();
using (sqlcommand cmd = new sqlcommand(insertquery, conn))
using (var insertedoutput = cmd.executereader())
{
while(insertedoutput.read())
{
list.add(insertedoutput.getint32(0));
}
}
console.writeline(list.count); // 4
// 通过dapper
var ids = conn.query<int>(insertquery).tolist();
console.writeline(ids.count); // 4
}四、参考：
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持代码网。
发表评论