[学习SQL SERVER 2005系列]关于INSERT、UPDATE 或 DELETE的OUTPUT及OUTPUT...INTO...用法

  1. [学习SQLSERVER2005系列]关于INSERT、UPDATE或DELETE的OUTPUT及OUTPUT...INTO...用法
  2.      作者:Flystone,转载注明出处
  3. 在INSERT、UPDATE、DELETE语句中使用OUTPUT得到语句影响的每行信息,今天我们来学习这个语法。
  4. 1、OUTPUT_CLAUSE定义(语法参Transact-SQL语法约定):
  5. <OUTPUT_CLAUSE>::=
  6. {
  7. [OUTPUT<dml_select_list>INTO{@table_variable|output_table}[(column_list)]]
  8. [OUTPUT<dml_select_list>]
  9. }
  10. <dml_select_list>::=
  11. {<column_name>|scalar_expression}[[AS]column_alias_identifier]
  12. [,...n]
  13. <column_name>::=
  14. {DELETED|INSERTED|from_table_name}.{*|column_name}
  15. 2、OUTPUT_CLAUSE说明:
  16. 返回受INSERT、UPDATE或DELETE语句影响的每行的信息,或者返回基于上述每行的表达式。这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。此外,也可以将结果插入表或表变量。
  17. 3、典型应用:
  18. 1、根据当前表的数据有条件的生成历史或新的初始化数据;
  19. 2、把INSERT、UPDATE或DELETE语句影响的每行的信息暂存处理或反馈给应用程序完成业务或逻辑的完整性;
  20. 3、OUTPUT子句对于在INSERT或UPDATE操作之后检索标识列或计算列的值可能非常有用;
  21. 4、示列:
  22. 1、根据当前表的数据有条件的生成历史数据;
  23. 记得我以前做零售及水厂应用系统时都会有一个月未数据的处理功能,无非根据当前的数据自动生成下一个月数据的初始值。下面我以一个简化了例子来说明,例子是记录员工每个月工分变化中,我们以12月的数据生成下个月的月初数据。
  24. ------------------------------------
  25. --Author:happyflsytone
  26. --Date:2008-10-0216:39:39
  27. --Description:根据当前数据生成下个月的月初数据,并删除历史数据
  28. ------------------------------------
  29. DECLARE@sTABLE([年]INT,[月]INT,[工号]INT,[上月工分值]INT,[本月工分值]INT);
  30. INSERT@sSELECT2008,12,1,10,11;
  31. INSERT@sSELECT2008,12,2,11,12;
  32. INSERT@sSELECT2008,12,3,11,13;
  33. INSERT@sSELECT2008,12,4,3,5;
  34. INSERT@sSELECT2008,12,5,1,7;
  35. INSERT@sSELECT2008,12,6,2,11;
  36. DELETEFROM@s
  37. OUTPUTCASEWHENDELETED.[月]=12THENDELETED.[年]+1ELSEDELETED.[年]END,
  38. CASEWHENDELETED.[月]=12THEN1ELSEDELETED.[月]+1END,
  39. DELETED.[工号],DELETED.[本月工分值],NULLas[上月工分值]
  40. INTO@s;
  41. SELECT*
  42. FROM@s
  43. ORDERBY1,2,3
  44. /*
  45. 年月工号上月工分值本月工分值
  46. -------------------------------------------------------
  47. 20091111NULL
  48. 20091212NULL
  49. 20091313NULL
  50. 2009145NULL
  51. 2009157NULL
  52. 20091611NULL
  53. (6行受影响)
  54. */
  55. 2、根据业务规则的需要保证数据完整性。
  56. 在这个例程里我假设在更新员工的最后登录时间同时增加一条日志信息。先看测试数据:
  57. ------------------------------------
  58. --Author:happyflsytone
  59. --Date:2008-10-0216:39:39
  60. --Description:员工登录时更新员工表的最后登录时间,同时在日志表增加一条登录信息
  61. ------------------------------------
  62. --操作员信息表(本例只关心最后登录时间,所以员工的信息不深入表述)
  63. DECLARE@PTABLE([工号]INT,[姓名]varchar(16),[最后登录时间]datetime);
  64. INSERT@PSELECT1,'test1',getdate()-1;
  65. INSERT@PSELECT2,'test2',getdate()-1;
  66. INSERT@PSELECT3,'test3',getdate()-1;
  67. INSERT@PSELECT4,'test4',getdate()-1;
  68. INSERT@PSELECT5,'test5',getdate()-1;
  69. INSERT@PSELECT6,'test6',getdate()-1;
  70. --操作员操作日志(象征性列举一些字段)
  71. DECLARE@LOGTABLE([工号]INT,[操作时间]DATETIME,[操作类型]CHAR(6),[操作说明]VARCHAR(200));
  72. --模拟工号为3的操作员登录,并记录相应日志
  73. UPDATE@p
  74. SET[最后登录时间]=GETDATE()
  75. OUTPUTDELETED.[工号],DELETED.[最后登录时间],'出舱','成功出舱行走,身体状况良好,仪器工作正常,请主席放心!'
  76. INTO@log
  77. WHERE[工号]=3;
  78. --查看日志
  79. SELECT*
  80. FROM@LOG;
  81. /*
  82. 工号操作时间操作类型操作说明
  83. ----------------------------------------------------------------------------
  84. 32008-10-0117:06:58.790出舱成功出舱行走,身体状况良好,仪器工作正常,请主席放心!
  85. (1行受影响)
  86. */
  87. 注:其实我们可以通过这个OUTPUT_CLAUSE向应用程序提供数据操作的历史信息,或是把数据缓存在表变量中以备程序再次调用,关于这方面的例子就不多说明,因为这是最基本的OUTPUT_CLAUSE应用。
  88. 3、标识列或计算列方面的应用
  89. 对于标识列我们可能通过@@IDENTITY、SCOPE_IDENTITY和IDENT_CURRENT几个相似的函数获得,他们都返回插入到表的IDENTITY列的最后一个值(本身这几个函数还是有差异的,主要是它们的作用域,请查联机帮助)。我们注意到它们只是返回最后一个值,对于批量时就无能无力了。对于实时并发多的系统时我们可以利用OUTPUT_CLAUSE语句把标识列的值提取出来。
  90. ------------------------------------
  91. --Author:happyflsytone
  92. --Date:2008-10-0216:39:39
  93. ------------------------------------
  94. CREATETABLEScrapReason(scrapreasonidINTIDENTITY,[name]VARCHAR(50),modifieddateDATETIME)
  95. ;
  96. --接受标识列值的表变量
  97. DECLARE@MyTableVarTABLE(ScrapReasonIDSMALLINT,
  98. NameVARCHAR(50),
  99. ModifiedDateDATETIME);
  100. --模拟插入数据
  101. INSERTScrapReason
  102. OUTPUTINSERTED.ScrapReasonID,INSERTED.Name,INSERTED.ModifiedDate
  103. INTO@MyTableVar
  104. SELECTN'OperatorIDENTITY',GETDATE()
  105. FROMsys.objects;
  106. --查看记录的标识列数据
  107. SELECTScrapReasonID,Name,ModifiedDateFROM@MyTableVar;
  108. GO
  109. droptableScrapReason;
  110. /*
  111. ScrapReasonIDNameModifiedDate
  112. --------------------------------------------------------------------------------------
  113. 1OperatorIDENTITY2008-10-0217:42:19.000
  114. 2OperatorIDENTITY2008-10-0217:42:19.000
  115. 3OperatorIDENTITY2008-10-0217:42:19.000
  116. 4OperatorIDENTITY2008-10-0217:42:19.000
  117. 5OperatorIDENTITY2008-10-0217:42:19.000
  118. 6OperatorIDENTITY2008-10-0217:42:19.000
  119. .....
  120. .....
  121. 62OperatorIDENTITY2008-10-0217:42:19.000
  122. 63OperatorIDENTITY2008-10-0217:42:19.000
  123. (63行受影响)
  124. */
  125. 下面我们再来看看触发器使用OUTPUT_CLAUSE的情况,
  126. ------------------------------------
  127. --Author:happyflsytone
  128. --Date:2008-10-0216:39:39
  129. ------------------------------------
  130. CREATETABLETA(
  131. scrapreasonidINTIDENTITYPRIMARYKEY,
  132. [name]VARCHAR(50),
  133. modifieddateDATETIME
  134. )
  135. ;
  136. CREATETABLETB(
  137. IDINTREFERENCESTA(SCRAPREASONID),
  138. [name]VARCHAR(50),
  139. MODIFIEDDATEDATETIME
  140. );
  141. GO
  142. CREATETRIGGERTR_INSERT
  143. ONTA
  144. INSTEADOFINSERT
  145. AS
  146. BEGIN
  147. --接受标识列值的表变量
  148. DECLARE@MyTableVarTABLE(IDINT,
  149. [NAME]VARCHAR(10),
  150. ModifiedDateDATETIME);
  151. INSERTTA
  152. OUTPUTINSERTED.scrapreasonid,INSERTED.[NAME],INSERTED.ModifiedDate
  153. INTO@MyTableVar
  154. SELECT[name],modifieddateFROMINSERTED
  155. INSERTINTOTBSELECT*FROM@MyTableVar
  156. END
  157. GO
  158. --模拟插入数据
  159. INSERTTASELECT'TEST',GETDATE();
  160. INSERTTASELECT'TEST2',GETDATE();
  161. --查看记录的标识列数据
  162. SELECT*FROMTB;
  163. /*
  164. IDnameMODIFIEDDATE
  165. ------------------------------------------------------------------------------------
  166. 1TEST2008-10-0217:53:46.780
  167. 2TEST22008-10-0217:53:46.870
  168. (2行受影响)
  169. */
  170. DROPTABLETB,TA;
  171. 最后说明一下使用OUTPUT子句的注意事项:
  172. 以下语句中不支持OUTPUT子句:
  173. 1、引用本地分区视图、分布式分区视图或远程表的DML语句。
  174. 2、包含EXECUTE语句的INSERT语句。
  175. 3、不能将OUTPUTINTO子句插入视图或行集函数。
  176. 4、参数或变量作为UPDATE语句的一部分进行了修改,则OUTPUT子句将始终返回语句执行之前的参数或变量的值而不是已修改的值
  177. Flystone于常州
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值