摘要:本文主要向大家介绍了在SQLServer数据库应用中使用OUTPUT子句,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了在SQLServer数据库应用中使用OUTPUT子句,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。
现在大多数描述SQL Server 2005新特性的文章都关注于华而不实的特性,如SQL CLR或XML数据类型,而对许多老很好的老的T-SQL语言的改进没有得到应有的关注。我曾经从许多DBA口中听到令他们更兴奋的是看到T-SQL语言的改进,而不是那些新出现和发布的功能。对于SQL语言的一个很有用的实际改进是OUTPUT子句,它允许查询一个数据修改命令所影响的记录行。
本文将讨论OUTPUT子句在SQL Server中的具体应用。我会向你介绍如何很容易地使用OUTPUT子句实现审计和数据修改存档等业务需求,以及其它相关的内容。
OUTPUT子句的基本原理是很简单的——它返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT可以在客户端应用程序中返回这些记录行,然后将它们插入到一个持久的或临时的表中,也可以将记录插入到一个表变量中。它的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。
OUTPUT子句中可以引用INSERTED或DELETED虚拟表,这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据。这跟使用触发器去修改数据的操作是很相似的。
注意:不能在一个INSERT语句中引用DELETED,也不能在一个DELETED语句中引用INSERTED,因为这些虚拟表在这两种情况下逻辑上是没有意义的,所以SQL Server不会去创建。
既然我们已经了解了OUTPUT子句在SQL Server中的基本用法,下面让我们看一些它的例子和SQL Server中的实际应用。我将从创建一个简单的Employee表开始:
[sql] view plain copy
1. create table dbo.Employees
2. (
3. EmployeeID int not null identity(1 ,1)
4. ,FirstName varchar(50) not null
5. ,LastName varchar(50) not null
6. ,status varchar(20) default 'Single'
7. )
下一步,我们要插入一行数据并加上OUTPUT来返回执行插入操作时所插入到应用中的记录:
[sql] view plain copy
1.
INSERT INTO dbo.Employees ( FirstName, LastName )
OUTPUT INSERTED.*
--into T2
SELECT 'Susan', 'Kelley'
EmployeeID
FirstName
LastName
Status
1
Susan
Kelley
Single
我们可以看到,SQL Server返回INSERT语句所插入的记录。这个技术对于查找服务器生成的值并返回给应用程序是很有用的,如标记字段或字段默认值。
下一步,我们将来自INSERT语句的输出实时地插入到一个表中。例如,Susan结婚后变更了她的姓。这时,我们需要更新她的员工信息。公司政策规定我们必须保留所有员工的历史数据,因此我们需要存档旧的员工数据。这样我们就创建了一个表Employee_Archive及一些附加域:
[sql] view plain copy
1. create table dbo.Employee_Archive
2. (
3. EmployeeID int not null
4. ,FirstName varchar(50) not null
5. ,LastName varchar(50) not null
6. ,[Status] varchar(20) default 'Single'
7. ,ChangedBy varchar(300) not null
8. ,ChangedDatetime datetime not null
9. )
现在我们可以更新Susan的信息记录了,同时使用OUTPUT子句和DELETED虚拟表将旧的记录行插入到Employee_Archive表中:
[sql] view plain copy
1. UPDATE Employees SET LastName = 'Jones',Status = 'Married'
2. OUTPUT DELETED.*, system_user, getdate()
3. INTO Employee_Archive
4. WHERE EmployeeID = 1
在执行这个查询后,我们会在Employee表中得到这些数据:
EmployeeID
FirstName
LastName
Status
1
Susan
Jones
Married
Employee_Archive表包含了一个有旧数据值、用户名和数据修改时间的记录行。这是我的计算机上的输出,你电脑上的输出可能有些不一样:
EmployeeID
FirstName
LastName
Status
ChangedBY
ChangedDatetime
1
Susan
Kelley
Single
rrehak
2008-04-21 02:04:18.310
另一个OUTPUT子句的实践用法是保存一系列受影响的记录以备后续处理。这在更新一组记录的时候是很有用的,在做一些额外的处理后再重新更新相同的记录集——我在使用一组INSERT/UPDATE/DELETE语句同步2个不同数据库时经常这样做。因为我们有了一列ID,我们就不必再次执行第一次的查询。相反,我们可以在WHERE子句中使用这些ID,从而避免再做一次复杂和大开销的查询去取回相同的数据集。下面的例子创建了一个临时表并将修改的员工记录的ID保存起来:
[sql] view plain copy
1. create table #EmployeeIDs
2. (
3. <span style="white-space:pre;"> </span>EmployeeID int not null
4. )
5. go
6. update dbo.Employees
7. set LastName = LastName
8. output inserted.EmployeeID
9. into #EmployeeI
在UPDATE语句执行后,临时表包含了ID和所有修改的记录。
如果需要从一个表清除大量的数据并转存到一个存档表中,OUTPUT子句是能够有效地节省处理时间的。大多数有经验的DBA都会将删除操作拆分成一组更小的删除操作,可能是100,000个或更多。所以真正的删除数据的代码可能会是这样的:
[sql] view plain copy
1. WHILE 1 = 1
2. BEGIN
3. BEGIN TRANSACTION
4. INSERT INTO ArchiveTable
5. SELECT *
6. FROM MainTable
7. WHERE ID BETWEEN @MinID AND @MaxID
8. DELETE FROM MainTable
9. WHERE ID BETWEEN @MinID AND @MaxID
10. COMMIT TRANSACTION
11. END
如果使用OUTPUT子句,我们就能够在一条语句中完成这个操作,并且可以节省很多处理时间,因为配置记录行只需要进行一次:
[sql] view plain copy
1. WHILE 1 = 1
2. BEGIN
3. DELETE FROM MainTable
4. OUTPUT DELETED.*
5. INTO ArchiveTable
6. WHERE ID BETWEEN @MinID AND @MaxID
7. END
我们可以看到,使用OUTPUT子句来简化代码和替代触发器有很多种可能的用法。在本文中,我介绍了如何使用OUTPUT语句去审计和存档数据、获取一组被修改的记录行和简化数据删除程序。
[sql] view plain copy
1. -------------------------------------------------
2. -- 演示表变量
3. DECLARE @t TABLE(id int)
4.
5. -- 1. 在INSERT语句中使用OUTPUT
6. -- OUTPUT结果直接返回给调用者
7. INSERT @t
8. OUTPUT INSERTED.*
9. SELECT object_id FROM sys.objects O
10.
11.
12. -- 2. 在UPDATE语句中使用OUTPUT
13. -- OUTPUT输出FROM子句中指定的表中的列, 这个列在被UPDATE的表中并不存在
14. UPDATE A SET
15. id = O.object_id % 100
16. OUTPUT
17. O.name,
18. DELETED.id AS id_BeforeUpdate,
19. INSERTED.id AS id_AfterUpdate
20. FROM @t A, sys.objects O
21. WHERE A.id = O.object_id
22.
23.
24. -- 3. 在DELETE语句中使用OUTPUT
25. -- 输出的结果返回给指定的表变量
26.
27. -- a. 用于保存输出结果的表变量
28. DECLARE @re TABLE(
29. id int, name sysname)
30.
31. -- b. 删除
32. DELETE A
33. OUTPUT
34. DELETED.id, O.name
35. INTO @re
36. FROM @t A, sys.objects O
37. WHERE A.id = O.object_id
38.
39. -- c. 显示结果
40. SELECT * FROM @re
本文由职坐标整理并发布,了解更多内容,请关注职坐标SQL Server数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号