SQL 存储过程入门（事务）（四）
这里做好准备工作，建立一张表，插入一条数据。
--新建表 create table userinfo (ID int identity(1,1) , UserName varchar(20) primary key, UserPwd varchar(20) , RegisterTime datetime ) --初始化插入一条记录 insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate()) select * from userinfo ------------------- ID userName UserPwd RegisterTime admin admin 2013-04-13 10:30:36.387
从表结构看出，UserName是主键，是唯一值，现在要插入两条数据
Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT on; --这句话非常重要 Begin Tran --开始事务 insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate()) insert into userinfo(username,userpwd,RegisterTime) values('jack','jack',getdate()) Commit Tran --提交事务 End
执行
exec MyProcedure /* 消息 2627，级别 14，状态 1，过程 MyProcedure，第 9 行 违反了 PRIMARY KEY 约束 'PK__userinfo__C9F284577F60ED59'。不能在对象 'dbo.userinfo' 中插入重复键。 */ --查看数据库 ------------------------------------------------ admin admin 2013-04-13 10:41:22.457
上面说了 Set XACT_ABORT on; 这句话非常重要 ，为什么呢？我们来设置为off的时候来看效果
Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT off; --这句话非常重要 Begin Tran --开始事务 insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate()) insert into userinfo(username,userpwd,RegisterTime) values('jack','jack',getdate()) Commit Tran --提交事务 End
执行并查看结果
exec MyProcedure /* 消息 2627，级别 14，状态 1，过程 MyProcedure，第 9 行 违反了 PRIMARY KEY 约束 'PK__userinfo__C9F284577F60ED59'。不能在对象 'dbo.userinfo' 中插入重复键。 语句已终止。 */ --查看结果 select * from userinfo ------------------------------------------- admin admin 2013-04-13 10:41:22.457 jack jack 2013-04-13 10:44:05.203
这里我们将XACT_ABORT 设置为off,事务中执行已经出现错误了，但是还是将 “jack”这条记录插入进去了。这就违反了事务的一致性原则了。所以我们要将XACT_ABORT 设置为ON的原因。
看看下面说明：
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这样的话，以达到优化存储过程的目的。
五，存储过程中事务和try…catch联合使用
如果我们在存储过程事务中出现了错误，我们不想显示错误，我们想动态处理这些错误信息，比如出错了，我们回滚，我们设置某个属性的值，这里就会用到try ,catch了
还是从例子出发
Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; --这句话非常重要 begin try Begin Tran --开始事务 insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate()) insert into userinfo(username,userpwd,RegisterTime) values('jack','jack',getdate()) Commit Tran --提交事务 end try begin catch --在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务 --表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交； --1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。 if xact_state()=-1 rollback tran; end catch Set XACT_ABORT OFF; End
当我们执行的时候不会再出现刚才的那种错误了，
exec MyProcedure -------------- 命令已成功完成。 --没有出现那种错误 select * from userinfo -------------------------------------------------- admin admin 2013-04-13 10:55:50.653
可以看到，事务回滚了，没有插入数据了。
如果我们想看到错误信息呢，再来看个例子
Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; --这句话非常重要 begin try Begin Tran --开始事务 insert into userinfo(username,userpwd,RegisterTime) values('admin','admin',getdate()) insert into userinfo(username,userpwd,RegisterTime) values('jack','jack',getdate()) Commit Tran --提交事务 end try begin catch --在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务 --表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交； --1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。 if xact_state()=-1 begin rollback tran; SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; end end catch End
执行
exec MyProcedure -------------------------------- ErrorNumber ErrorMessage 2627 违反了 PRIMARY KEY 约束 'PK__userinfo__C9F284577F60ED59'。不能在对象 'dbo.userinfo' 中插入重复键。
说明：1、捕获错误的函数有很多，如下：
ERROR_NUMBER() 返回错误号。
ERROR_SEVERITY() 返回严重性。
ERROR_STATE() 返回错误状态号。
ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。
ERROR_LINE() 返回导致错误的例程中的行号。
ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。
在存储过程中使用事务时，如果存在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操作。
下面再来看个例子，在实践中不断熟悉。
这个例子就是，如果插入重复的数据给出提示信息并返回
-- 判断要创建的存储过程名是否存在 if Exists(Select name From sysobjects Where name = 'P_InsertUser' And type = 'P') -- 删除存储过程 Drop Procedure dbo.P_InsertUser Go USE [StoreTest] GO create Procedure [dbo].[P_InsertUser] @UserName varchar(100), @UserPwd varchar(100) AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; --这句话非常重要 Begin try if(isnull(@UserName,'')='') begin print 'UserName is empty'; return; end declare @iCount int; set @iCount = 0; select @iCount = Count(1) from userinfo with(nolock) where username=@UserName; if( @iCount > 0 ) begin print 'the current name already exist'; return end Begin Tran --开始事务,事务中不能有return语句 --insert insert into userinfo( username ,userpwd ,RegisterTime ) values( @UserName, @UserPwd, getdate() ) Commit Tran --提交事务 end try begin catch --在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务 --表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交； --1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。 if xact_state()=-1 begin rollback tran; --事务回滚 SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; end end catch Set XACT_ABORT off; End --调用存储过程 exec [P_InsertUser] '','admin' select * from userinfo GO
事务的东西很多，这里希望能起到抛砖引玉的效果。
这里附近一下c#使用事务的语法，概念是一样是，只不过是用c#实现的。
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction(v=vs.110).aspx
using (SqlConnection conn = new SqlConnection(Connstring) { conn .Open(); // Start a local transaction. SqlTransaction sqlTran = conn .BeginTransaction(); // Enlist a command in the current transaction. SqlCommand command = conn .CreateCommand(); //begin transaction command.Transaction = sqlTran; try { // Execute two separate commands. command.CommandText ="xxxxx"; command.ExecuteNonQuery(); command.CommandText ="yyyyyy"； command.ExecuteNonQuery(); // Commit the transaction. sqlTran.Commit(); } catch (Exception ex) { // Handle the exception if the transaction fails to commit. lblMsg.Text = ex.Message; try { // Attempt to roll back the transaction. sqlTran.Rollback(); } catch (Exception exRollback) { // Throws an InvalidOperationException if the connection // is closed or the transaction has already been rolled // back on the server. lblMsg.Text = exRollback.Message; } } }
