http://expert.csdn.net/Expert/TopicView1.asp?id=1675986 全接触SQL异常与孤立事务!一、首先从SQLServer中Error讲起,SQL中错误处理有些怪辟 错误级别同是16但结果都不同。 select * from 一个不在的表
if @@error<>0
print '这个没有输出'
go raiserror('',16,3)
if @@error<>0
print '这个输出了'
go exec('select * from 一个不在的表')
if @@error<>0
print '这个输出了'
go exec sp_executesql N'select * from 一个不在的表'
if @@error<>0
print '这个输出了'这样你可以发现通过exec或sp_executesql执行可疑的sql,这样就可以在后面捕捉到被异常终止的错误。
二、引出孤立事务:
1、孤立事务的产生 select @@trancount 当前连接的活动事务数 --当前连接的活动事务数为0 begin tran select * from 一个不在的表
if @@error<>0
begin
print '没有执行到这里来!'
if @@trancount<>0 rollback tran
end commit tran select @@trancount 当前连接的活动事务数 --执行后你看看 当前连接的活动事务数为1,且重复执行会每次累加,这是很耗资源的。 应为rollback根本就没有被回滚。 2、使用现有手段解决孤立事务
print @@trancount print '当前连接的活动事务数' --当前连接的活动事务数为0 if @@trancount<>0 rollback tran --在这里写可以让孤立事务只保持到下次你的过程被调用
begin tran select * from 一个不在的表
if @@error<>0
begin
print '没有执行到这里来!'
if @@trancount<>0 rollback tran
end commit tran ---执行后你看看 当前连接的活动事务数为1,但重复执行不会累加
print @@trancount print '当前连接的活动事务数'三、使用 set xact_abort 来控制部分违反约束的错误的执行过程 create table Table1 (a int check(a>100))
go set xact_abort on
begin tran
insert table1 values(10)
print '这里没有被执行'
commit tran
go
print '' print '==============================================' print ''
set xact_abort off
begin tran
insert table1 values(10)
print '这里被执行'
commit tran go
drop table table1但 set xact_abort 对于编译产生的错误确没有起作用,且同样会产生孤立事务 set xact_abort on
begin tran
insert 一个不在的表 values(10)
print '这里没有被执行'
commit tran
go print '' print '==============================================' print '' set xact_abort off
begin tran
insert 一个不在的表 values(10)
print '这里没有被执行'
commit tran
go select @@trancount 当前连接的活动事务数 ---有两个孤立事务
if @@trancount<>0 rollback tran
对于sql中怪辟的各种错误,和孤立事务在t-sql编程中一定要注意,小心孤立事务的陷阱,尽量避免浪费或孤立资源,Microsoft公开宣布过SQLServe下一版本Yukon将有内置异常处理语法。那时可以通过代码对无法预料的错误有更好的控制。
if @@error<>0
print '这个没有输出'
go raiserror('',16,3)
if @@error<>0
print '这个输出了'
go exec('select * from 一个不在的表')
if @@error<>0
print '这个输出了'
go exec sp_executesql N'select * from 一个不在的表'
if @@error<>0
print '这个输出了'这样你可以发现通过exec或sp_executesql执行可疑的sql,这样就可以在后面捕捉到被异常终止的错误。
二、引出孤立事务:
1、孤立事务的产生 select @@trancount 当前连接的活动事务数 --当前连接的活动事务数为0 begin tran select * from 一个不在的表
if @@error<>0
begin
print '没有执行到这里来!'
if @@trancount<>0 rollback tran
end commit tran select @@trancount 当前连接的活动事务数 --执行后你看看 当前连接的活动事务数为1,且重复执行会每次累加,这是很耗资源的。 应为rollback根本就没有被回滚。 2、使用现有手段解决孤立事务
print @@trancount print '当前连接的活动事务数' --当前连接的活动事务数为0 if @@trancount<>0 rollback tran --在这里写可以让孤立事务只保持到下次你的过程被调用
begin tran select * from 一个不在的表
if @@error<>0
begin
print '没有执行到这里来!'
if @@trancount<>0 rollback tran
end commit tran ---执行后你看看 当前连接的活动事务数为1,但重复执行不会累加
print @@trancount print '当前连接的活动事务数'三、使用 set xact_abort 来控制部分违反约束的错误的执行过程 create table Table1 (a int check(a>100))
go set xact_abort on
begin tran
insert table1 values(10)
print '这里没有被执行'
commit tran
go
print '' print '==============================================' print ''
set xact_abort off
begin tran
insert table1 values(10)
print '这里被执行'
commit tran go
drop table table1但 set xact_abort 对于编译产生的错误确没有起作用,且同样会产生孤立事务 set xact_abort on
begin tran
insert 一个不在的表 values(10)
print '这里没有被执行'
commit tran
go print '' print '==============================================' print '' set xact_abort off
begin tran
insert 一个不在的表 values(10)
print '这里没有被执行'
commit tran
go select @@trancount 当前连接的活动事务数 ---有两个孤立事务
if @@trancount<>0 rollback tran
对于sql中怪辟的各种错误,和孤立事务在t-sql编程中一定要注意,小心孤立事务的陷阱,尽量避免浪费或孤立资源,Microsoft公开宣布过SQLServe下一版本Yukon将有内置异常处理语法。那时可以通过代码对无法预料的错误有更好的控制。
返回受上一语句影响的行数。语法
@@ROWCOUNT返回类型
integer注释
任何不返回行的语句将这一变量设置为 0 ,如 IF 语句。示例
下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'
A.用 @@ERROR 检测一个特定错误
下面的示例用 @@ERROR 在一个 UPDATE 语句中检测限制检查冲突(错误 #547)。USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"IF @@ERROR = 547
print "A check constraint violation occurred"B.用 @@ERROR 有条件地退出一个过程
在此示例中,IF...ELSE 语句在存储过程中的 INSERT 语句后检测 @@ERROR。@@ERROR 变量的值将决定传给调用程序的返回值,以指示此过程的成功与失败。USE pubs
GO-- Create the procedure.
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GOC.用 @@ERROR 检测几条语句的成功
下面的示例取决于 INSERT 和 DELETE 语句的成功操作。局部变量在两条语句后均被设置为 @@ERROR 的值,并且用于此操作的共享错误处理例程中。USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'-- Set a variable to the error value for
-- the DELETE statement.
SELECT @del_error = @@ERROR-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @ins_error = @@ERROR-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement." IF @ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement." ROLLBACK TRAN
END
GOD. 与 @@ROWCOUNT 一同使用 @@ERROR
下面的示例用 @@ERROR 和 @@ROWCOUNT 验证一条 UPDATE 语句的操作。为任何可能出现的错误而检验 @@ERROR 的值,而用 @@ROWCOUNT 保证更新已成功应用于表中的某行。USE pubs
GO
CREATE PROCEDURE change_publisher
@title_id tid,
@new_pub_id char(4)
AS-- Declare variables used in error checking.
DECLARE @error_var int, @rowcount_var int-- Execute the UPDATE statement.
UPDATE titles SET pub_id = @new_pub_id
WHERE title_id = @title_id -- Save the @@ERROR and @@ROWCOUNT values in local
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT-- Check for errors. If an invalid @new_pub_id was specified
-- the UPDATE statement returns a foreign-key violation error #547.
IF @error_var <> 0
BEGIN
IF @error_var = 547
BEGIN
PRINT "ERROR: Invalid ID specified for new publisher"
RETURN(1)
END
ELSE
BEGIN
PRINT "ERROR: Unhandled error occurred"
RETURN(2)
END
END-- Check the rowcount. @rowcount_var is set to 0
-- if an invalid @title_id was specified.
IF @rowcount_var = 0
BEGIN
PRINT "Warning: The title_id specified is not valid"
RETURN(1)
END
ELSE
BEGIN
PRINT "The book has been updated with the new publisher"
RETURN(0)
END
GO