存储过程中使用事务

落日映苍穹つ 2022-09-28 15:28 183阅读 0赞

**一、存储过程中使用事务的简单语法**

在存储过程中使用事务时非常重要的，使用数据可以保持数据的关联完整性，在Sql server存储过程中使用事务也很简单，用一个例子来说明它的语法格式：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

Create Procedure MyProcedure ( @Param1 nvarchar(
         
          10
         
          ), @param2 nvarchar(
         
          10
         
          ) ) AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; Begin Tran Delete from table1 
         
          where
         
          name
         
          =
         
          ’abc’; Insert into table2 values(value1,value2,value3); Commit Tran End

说明：1 、使用存储过程执行事物，需要开启XACT\_ABORT参数(默认值为Off)，将该参数设置为On，表示当执行事务时，如果出错,会将transcation设置为uncommittable状态，那么在语句块批处理结束后将回滚所有操作；如果该参数设置为Off，表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。

2、当SET NOCOUNT 为 ON 时，不返回计数（计数表示受 Transact-SQL 语句影响的行数，例如在Sql server查询分析器中执行一个delete操作后，下方窗口会提示（3）Rows Affected）。当   SET NOCOUNT 为 OFF 时，返回计数,我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上 SET NOCOUNT OFF这样的话，以达到优化存储过程的目的。

**二、事务内设置保存点**

用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。如果将事务回滚到保存点，则必须（如果需要，使用更多的 Transact-SQL 语句和 COMMIT TRANSACTION 语句）继续完成事务，或者必须（通过将事务回滚到其起始点）完全取消事务。若要取消整个事务，请使用 ROLLBACK TRANSACTION transaction\_name 格式。这将撤消事务的所有语句和过程。如：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; begin tran ok 
         
          --
         
          开始一个事务OK delete from rxqz 
         
          where
         
          qz
         
          =
         
          
         
          '
         
          rx015 
         
          '
         
          
         
          --
         
          删除数据 save tran bcd 
         
          --
         
          保存一个事务点命名为bcd update sz 
         
          set
         
          name
         
          =
         
          '
         
          李丽s
         
          '
         
          
         
          where
         
          name
         
          =
         
          
         
          '
         
          李丽
         
          '
         
          --
         
          修改数据 
         
          if
         
          @@error
         
          <>
         
          0
         
          
         
          --
         
          判断修改数据有没有出错 begin 
         
          --
         
          如果出错 rollback tran bcd 
         
          --
         
          回滚事务到BCD 的还原点 commit tran ok 
         
          --
         
          提交事务 end 
         
          else
         
          
         
          --
         
          没有出错 commit tran ok 
         
          --
         
          提交事务 End

说明：1、@@error判断是否有错误，为0表示没有错误，但是对那种重大错误无法捕捉，而且@@error只能前一句sql语句生效。

**三、存储过程使用try…catch捕获错误**

在存储过程中可以使用try…catch语句来捕获错误，如下：

![ExpandedBlockStart.gif][] 代码

Create Procedure MyProcedure ( @Param1 nvarchar(
         
          10
         
          ), @param2 nvarchar(
         
          10
         
          ) ) AS Begin Set NOCOUNT ON; Begin 
         
          try
         
          Delete from table1 
         
          where
         
          name
         
          =
         
          ’abc’; Insert into table2 values(value1,value2,value3); End 
         
          try
         
          Begin Catch SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; End Catch End

说明：1、捕获错误的函数有很多，如下：

ERROR\_NUMBER() 返回错误号。

ERROR\_SEVERITY() 返回严重性。

ERROR\_STATE() 返回错误状态号。

ERROR\_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

ERROR\_LINE() 返回导致错误的例程中的行号。

ERROR\_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

2、有些错误，如sql语句中的表名称输入错误，这是数据库引擎无法解析这个表名称时，所发生的错误在当前的try…catch语句中无法捕获，必须由外层调用该存储过程的地方使用　　　　　　　　　　　　               try…catch来进行捕获。

**四、存储过程中事务和try…catch联合使用**

在存储过程中使用事务时，如果没有try…catch语句，那么当set xact\_abort on时，如果有错误发生，在批处理语句结束后，系统会自动回滚所有的sql操作。当set xact\_abort off时，如果有错误发生，在批处理语句结束后，系统会执行所有没有发生错误的语句，发生错误的语句将不会被执行。

在存储过程中使用事务时，如果存在try…catch语句块，那么当捕获到错误时，需要在catch语句块中手动进行Rollback操作，否则系统会给客户端传递一条错误信息。如果在存储过程开始处将set xact\_abort on，那么当有错误发生时，系统会将当前事务置为不可提交状态，即会将xact\_state()置为-1，此时只可以对事务进行Rollback操作，不可进行提交(commit)操作，那么我们在catch语句块中就可以根据xact\_state()的值来判断是否有事务处于不可提交状态，如果有则可以进行rollback操作了。如果在存储过程开始处将set xact\_abort off，那么当有错误发生时，系统不会讲xact\_state()置为-1，那么我们在catch块中就不可以根据该函数值来判断是否需要进行rollback了，但是我们可以根据@@Trancount全局变量来判断，如果在catch块中判断出@@Trancount数值大于0，代表还有未提交的事务，既然进入catch语句块了，那么还存在未提交的事务，该事务应该是需要rollback的，但是这种方法在某些情况下可能判断的不准确。推荐的方法还是将set xact\_abort on，然后在catch中判断xact\_state()的值来判断是否需要Rollback操作。

下面我们来看看两个例子：

**一．使用Set xact\_abort       on**

![ExpandedBlockStart.gif][] 代码

Create proc myProcedure As begin 
         
          set
         
          xact_abort on; begin 
         
          try
         
          begin tran insert into TestStu values(
         
          '
         
          Terry
         
          '
         
          ,
         
          '
         
          boy
         
          '
         
          ,
         
          23
         
          ); insert into TestStu values(
         
          '
         
          Mary
         
          '
         
          ,
         
          '
         
          girl
         
          '
         
          ,
         
          21
         
          ); commit tran end 
         
          try
         
          begin 
         
          catch
         
          
         
          --
         
          在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务 
         
          --
         
          表示在事务内部发生错误了。Xact_state()有三种值：
         
          -
         
          1
         
          .事务不可提交； 
         
          --
         
          1
         
          .事务可提交；
         
          0
         
          .表示没有事务，此时commit或者rollback会报错。 
         
          if
         
          xact_state()
         
          =-
         
          1
         
          rollback tran; end 
         
          catch
         
          end

**二.使用Set xact\_abort off**

![ExpandedBlockStart.gif][] 代码

Create proc myProcedure As begin 
         
          set
         
          xact_abort off; begin 
         
          try
         
          begin tran insert into TestStu values(
         
          '
         
          Terry
         
          '
         
          ,
         
          '
         
          boy
         
          '
         
          ,
         
          23
         
          ); insert into TestStu values(
         
          '
         
          Mary
         
          '
         
          ,
         
          '
         
          girl
         
          '
         
          ,
         
          21
         
          ); commit tran end 
         
          try
         
          begin 
         
          catch
         
          
         
          --
         
          在此不可以使用xact_state来判断是否有不可提交的事务 
         
          --
         
          只可以使用@@Trancount来判断是否有还未提交的事务，未提交的事务未必 
         
          --
         
          就是不可提交的事务，所以使用@@TranCount
         
          >
         
          0后就RollBack是不准确的 
         
          if
         
          @@TranCount
         
          >
         
          0
         
          rollback tran; end 
         
          catch
         
          end

另外，对于@@Trancount需要说明的是，begin  tran 语句将 @@Trancount加 1。Rollback  tran将 @@Trancount递减到 0，但 Rollback tran savepoint\_name 除外，它不影响 @@Trancount。Commit  tran 或 Commit  work 将 @@Trancount 递减 1。

[ContractedBlock.gif]: http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20220708/c6f3bead39f840718b11e7762811932c.png