存储过程中使用事务

存储过程中使用的事务

GPS平台，网站建设，软件开发，系统运维，找森达网络科技！

https://cnsendnet.taobao.com

来自森达科技官方博客

http://www.cnsendblog.com/index.php/p=2094

存储过程中使用事务

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

在存储过程中使用事务非常重要。使用数据可以保持数据的关系完整性。在Sql服务器存储过程中使用事务也非常简单。下面是一个例子来说明它的语法格式：

密码

创建过程我的过程

（

@Param1 nvarchar(10)，

@param2 nvarchar(10)

)

如同

开始

设置NOCOUNT

开；

将XACT _中止设置为开；

开始传输

从表1中删除，其中名称=' abc

插入表2中的值(值1、值2、值3)；

提交交易

结束

注：1。使用存储过程执行事情时，需要打开XACT_ABORT参数(默认值为Off)，将此参数设置为On，这意味着在执行事务时，如果出现问题，transactions将被设置为不可提交状态，然后在语句块批处理完成后，所有操作都会回滚；如果此参数设置为Off，则表示执行事务时如果出现问题，将不会执行错误的语句，而继续执行其他正确的操作。

2.当SET NOCOUNT为ON时，不返回计数(计数表示受Transact-SQL语句影响的行数，例如，在Sql server Query Analyzer中执行删除操作后，下部窗口将提示(3)受影响的行)。当设置无计数关闭时，将返回计数。我们应该在存储过程的头部添加“设置无计数开”和“设置无计数关”来优化存储过程。

二、事务内设置保存点

用户可以在事务中设置保存点或标记。保存点定义了如果部分事务被有条件取消，事务可以返回的位置。如果将事务回滚到保存点，则必须继续完成事务(如有必要，使用更多的Transact-SQL语句和COMMIT TRANSACTION语句)或完全取消事务(通过将事务回滚到其起始点)。要取消整个事务，请使用格式回滚事务事务名称。这将撤消事务的所有语句和过程。例如：

密码

创造

程序我的程序

如同

开始

设置NOCOUNT

开；

将XACT _中止设置为开；

begin tran

确定-开始交易确定

从.删除

Rxqz，其中qz='rx015' -删除数据

保存交易

Bcd -保存名为Bcd的交易点

更新sz集

名称='丽丽'其中名称='丽丽'-修改数据

如果

@ @ @错误0 -确定修改数据时是否有任何错误。

开始-如果出了问题

回滚事务

Bcd -将事务回滚到Bcd的还原点

提交交易

确定-提交交易。

结束

否则-没有错。

提交事务ok -提交事务。

结束

描述：1。@@@ error判断是否有错误。值为0表示没有错误，但无法捕捉到主要错误，并且@@@错误只能从以前的sql语句中生效。

三、存储过程使用

ong>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来判断是否有还未提交的事务，未提交的事务未必

--就是不可提交的事务，所以使用@@TranCount0后就RollBack是不准确的

if @@TranCount0

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。

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/p=2094