• 一个简单的存储过程使用事务的例子


      在存储过程中使用事务时非常重要,使用数据库可以保持数据的关联完整性,在SQL Server存储过程中使用事务也很简单,举个例子:

    复制代码

    Create  Procedure MyProcedure
    (
       @Param1 nvarchar(10),
       @Param2 nvarchar(10)
    )
    AS
        Begin  
                  Set NOCUNT 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 Nocunt为ON时,不返回计数(计数表示受Transact-SQL语句影响的行数,例如在SQL Server查询分析器中执行一个Delete操作后,下方窗口提示(3) Rows Affeted)。当Set  NOCOUNT 为OFF时,返回计数,我们应该在存储过程的头部加上Set  NOCOUNT ON这样的话,在推出存储过程的时候加上Set Nocunt Off这样的话,以达到优化存储过程的目的。

    二、事务内设置保存点

          用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分,事务可以返回的位置。如果将事务回滚到保存点,则必须(如果需要,使用更多的Transact-SQL语句和Commite  Transaction语句)继续完成事务,或者必须(通过事务回滚到起始点)完全取消事务。若要取消整个事务,请使用Rollback Tansaction_name格式。这将撤销事务的所有语句和过程。如:

    复制代码
    Create  Procedure  MyProcedure
    AS
         Begin
                 Set  NOCUNT  ON;
                 Set   XACT_ABORT  ON;
                 begin  tran  ok --开始一个事务OK 
                        delete  from  rxqz  where qz=rx015'  --删除数据
             save  tran  bcd  --保存一个事务点命名为bcd
                    update  sz  set name='ln'  where name='l'  ----修改数据
             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语句来捕获错误,如下:

    复制代码
     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 

    复制代码
    复制代码
    代码
    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 

    复制代码
    复制代码
    代码
    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。

  • 相关阅读:
    不会写研发部门OKR?来这里看看吧
    HR 必须了解的绩效考核
    成功的OKR复盘会需要注意什么
    effective解读-第一条 静态工厂创建对象代替构造器
    灾难恢复:邮箱数据库操作总结:整理 查询邮箱数据库大小和空白数据大小(重要文档)
    Exchange传输队列queue数据库mail.que文件越来越大(重要文档)
    Exchange2016邮件流(重要文档)
    Exchange2016服务器使用到的9个端口
    VMware EXSI 启用vMotion
    Exchange2016DAG的配置
  • 原文地址:https://www.cnblogs.com/momjs/p/5776155.html
Copyright © 2020-2023  润新知