存储过程中如何使用事务

存储过程中如何使用事务

一、sql事务

1、什么是事务

事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是做为最小的控制单元来使用的。他包含的所有数据库操作命令作为一个整体一起向系提交或撤消，这一组数据库操作命令要么都执行，要么都不执行。

2、事务的语句

开始事物：BEGIN TRANSACTION
提交事物：COMMIT TRANSACTION
回滚事务：ROLLBACK TRANSACTION

二、存储过程的优势

1、能实现模块化程序设计。存储过程是根据实际功能的需要创建的一个程序模块，并被存储在数据库中。以后用户要完成该功能，只要在程序中直接调用该存储过程即可，而无需再编写重复的程序代码。存储过程可由数据库编程方面的专门人员创建，并可独立于程序源代码而进行修改和扩展。

2、使用存储过程可以提高执行效率。当客户程序需要访问服务器上的数据时，一般要经过5个步骤：

 ● 查询语句被发送到服务器；
 ● 服务器编译T-SQL语句；
 ● 优化产生查询执行计划；
 ● 数据库引擎执行查询；
 ● 执行结果发回客户程序。

如果执行存储在客户端本地的T-SQL程序，那么每次执行该程序时，对于程序中的每一条语句都要经过以上5个步骤。而存储过程在创建时就被编译和优化，当存储过程第一次被执行时，SQL Server为其产生查询计划并将其保存在内存中，这样以后在调用该存储过程时就不必再进行编译，即以上5个步骤中的第2步和第3步就被省略了，这能大大改善系统的性能。

3、减少网络流量。一个需要数百行T-SQL代码的操作，如果将其创建成存储过程，那么使用一条调用存储过程的语句就可完成该操作。这样就可避免在网络上发送数百行代码，从而减少了网络负荷。

三、存储过程中使用事务的方法

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

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

    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这样的话，以达到优化存储过程的目的。

2、事务内设置保存点

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

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

		

说明：

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

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

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

 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 来进行捕获。

4、存储过程中事务和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操作。

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

（1）、使用Set xact_abort       on

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

		

（2）、使用Set xact_abort off

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。