SQL Server在存储过程中编写事务处理代码的三种方法

数据库 SQL Server
本文我们主要介绍了SQL Server数据库中在存储过程中编写正确的事务处理代码的方法,希望能够对您有所帮助。

SQL Server中数据库事务处理是相当有用的,鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞,本文我们介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。

在编写SQL Server 事务相关的存储过程代码时,经常看到下面这样的写法:

 

  1. begin tran   
  2. update statement 1 ...   
  3. update statement 2 ...   
  4. delete statement 3 ...   
  5. commit tran 

 

这样编写的SQL存在很大隐患。请看下面的例子:

 

  1. create table demo(id int not null)   
  2. go   
  3. begin tran   
  4. insert into demo values (null)  
  5. insert into demo values (2)   
  6. commit tran   
  7. go 

 

执行时会出现一个违反not null 约束的错误信息,但随后又提示(1 row(s) affected)。 我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。 这是什么原因呢? 原来 SQL Server在发生runtime 错误时,默认会rollback引起错误的语句,而继续执行后续语句。

如何避免这样的问题呢?有三种方法:

1. 在事务语句最前面加上set xact_abort on

 

  1. set xact_abort on   
  2. begin tran   
  3. update statement 1 ...   
  4. update statement 2 ...   
  5. delete statement 3 ...   
  6. commit tran   
  7. go 

 

当xact_abort 选项为on 时,SQL Server在遇到错误时会终止执行并rollback 整个事务。

2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。

 

  1. begin tran   
  2. update statement 1 ...  
  3. if @@error <> 0   
  4. begin rollback tran   
  5. goto labend   
  6. end   
  7. delete statement 2 ...   
  8. if @@error <> 0  
  9. begin rollback tran   
  10. goto labend   
  11. end   
  12. commit tran   
  13. labend:   
  14. go 

 

3. 在SQL Server 2005中,可利用 try...catch 异常处理机制。

 

  1. begin tran   
  2. begin try   
  3. update statement 1 ...   
  4. delete statement 2 ...   
  5. endtry   
  6. begin catch  
  7. if @@trancount > 0   
  8. rollback tran   
  9. end catch  
  10. if @@trancount > 0   
  11. commit tran  
  12. go 

 

下面是个简单的存储过程,演示事务处理过程。

 

  1. create procedure dbo.pr_tran_inproc as begin set nocount on   
  2. begin tran  
  3. update statement 1 ...   
  4. if @@error <> 0   
  5. begin rollback tran   
  6. return -1 end   
  7. delete statement 2 ...   
  8. if @@error <> 0   
  9. begin rollback tran   
  10. return -1   
  11. end commit tran   
  12. return 0   
  13. end   
  14. go  

 

关于SQL Server数据库中在存储过程中编写正确的事务处理代码的方法就介绍到这里了,希望本次的介绍能够对您有所帮助。

原文出处:http://www.sqlstudy.com/sql_article.php?id=2008060701。

【编辑推荐】

  1. MySQL数据库完整备份与增量备份的原理简介
  2. SQL Server链接服务器访问DB2设置步骤图解
  3. 详解xtrabackup对MySQL进行备份和恢复的全过程
  4. SQL Server中通过设置SET NOCOUNT来优化存储过程
  5. VC程序访问Oracle数据库的存储过程时的问题解决方案
责任编辑:赵鹏 来源: www.sqlstudy.com
相关推荐

2011-03-15 10:22:42

SQL Server 联机事务处理

2009-04-15 17:03:15

OracleSQL Server事务处理

2011-04-27 15:55:16

2011-04-27 16:09:48

SQL ServerSSIS

2011-08-15 15:56:31

SQL Server

2010-10-20 13:52:07

SQL Server数

2009-07-08 12:56:32

编写Servlet

2009-07-15 17:41:55

iBATIS事务处理

2010-01-04 13:06:50

ADO.NET事务

2010-11-10 13:28:06

SQL Server删

2009-10-09 16:47:44

Oracle数据库事务

2009-09-14 19:55:03

LINQ事务处理

2009-11-04 15:05:45

ADO.NET事务

2015-03-18 09:33:41

大数据分布式系统事务处理

2021-10-13 09:02:05

Python中文乱码Python基础

2010-04-13 15:44:00

Oracle与SqlS

2014-01-22 13:37:53

2010-11-12 09:18:13

SQL Server存

2010-01-13 14:13:24

VB.NET事务处理

2009-07-09 18:15:42

JDBC事务处理
点赞
收藏

51CTO技术栈公众号