存储过程中使用事务

转载 2011年01月07日 16:58:00

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

      在存储过程中使用事务时非常重要的,使用数据可以保持数据的关联完整性,在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这样的话,以达到优化存储过程的目的。

 

二、事务内设置保存点

  用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分,事务可以返回的位置。如果将事务回滚到保存点,则必须(如果需要,使用更多的 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

 

 

  说明: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。

相关文章推荐

在MySql存储过程中使用事务

在MySql存储过程中使用事务现有如下的存储过程,执行插入操作。其中bill表中id为primary key,其它为字段为普通字段,没加任何索引与约束,多余的省略,不予展示。DROP PROCEDUR...

存储过程中使用事务

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

存储过程中使用事务

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

存储过程中使用事务

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

存储过程中使用事务---转载

写的挺好,正是目前所需,也分享给大家~     文章出处:http://www.cnblogs.com/RascallySnake/archive/2010/05/17/1737298.html ...

存储过程中使用事务

存储过程中使用事务 一、存储过程中使用事务的简单语法       在存储过程中使用事务时非常重要的,使用数据可以保持数据的关联完整性,在Sql server存储过程中使用事务也很简单,用一个例...

存储过程中使用事务

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

mysql存储过程中使用事务

mysql存储过程中使用事务 1 DROP PROCEDURE IF EXISTS test_sp1 2 CREATE PROCEDURE test_sp1( ) 3...

SQLServer 存储过程中使用事务

存储过程SQL: create proc ***** @GoodsId int, @Number int, @StockPrice money, @SupplierId int, @EmpId in...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)