我要评论try...catch是sql server 2005/2008令人印象深刻的新特性.提高了开发人员异常处理能力.没有理由不尝试一下try.. catch功能.
* try 块 - 包含可能产生异常的代码或脚本
* catch 块 - 如果try块出现异常,代码处理流将被路由到catch块.在这里你可以处理异常,记录日志等.
sql server中的try catch和c#,java等语言的处理方式一脉相承.这种一致性才是最大的创新之处.
一、sql server 2000中异常处理
create proc usp_accounttransaction
@accountnum int,
@amount decimal
as
begin
begin transaction --beginning a transaction..
update mychecking set amount = amount - @amount
where accountnum = @accountnum
if @@error != 0 --check @@error variable after each dml statements..
begin
rollback transaction --rollback transaction if error..
return
end
else
begin
update mysavings set amount = amount + @amount
where accountnum = @accountnum
if @@error != 0 --check @@error variable after each dml statements..
begin
rollback transaction --rollback transaction if error..
return
end
else
begin
commit transaction --finally, commit the transaction if success..
return
end
end
end
go
上面是sql server 2000的一个存储过程,在每个数据库操作之后立即必须检查@@error,进行commit / rollback该事务.
sql server 2000中监测错误,只能通过监测全局遍历 @@error.由于@@error会被下一个数据库操作所覆盖. 所以在每次操作完后必须立即监测.
二、sql server 2005中异常处理
try...catch是sql server 2005提供的更具有可读性的语法.每个开发人员都熟悉这种写法.sql server 2005仍然支持@@error这种用法.
1.try catch语法:
begin try try statement 1 try statement 2 ... try statement m end try begin catch catch statement 1 catch statement 2 ... catch statement n end catch
2.获得错误信息的函数表:
下面系统函数在catch块有效.可以用来得到更多的错误信息:
函数 描述
error_number() 返回导致运行 catch 块的错误消息的错误号。
error_severity() 返回导致 catch 块运行的错误消息的严重级别
error_state() 返回导致 catch 块运行的错误消息的状态号
error_procedure() 返回出现错误的存储过程名称
error_line() 返回发生错误的行号
error_message() 返回导致 catch 块运行的错误消息的完整文本
简单示例:
begin try select getdate() select 1/0--evergreen divide by zero example! end try begin catch select 'there was an error! ' + error_message() return end catch;
3.try catch回滚/提交事务的示例
alter proc usp_accounttransaction
@accountnum int,
@amount decimal
as
begin
begin try --start the try block..
begin transaction -- start the transaction..
update mychecking set amount = amount - @amount
where accountnum = @accountnum
update mysavings set amount = amount + @amount
where accountnum = @accountnum
commit tran -- transaction success!
end try
begin catch
if @@trancount > 0
rollback tran --rollback in case of error
-- you can raise error with raiseerror() statement including the details of the exception
raiserror(error_message(), error_severity(), 1)
end catch
end
go
三、实例讲解
创建错误日志表:
create table errorlog(errnum int,errsev nvarchar(1000),errstate int,errproc nvarchar(1000),errline int, errmsg nvarchar(2000))
创建错误日志记录存储过程:
create procedure errorlog as select error_number() as errnum,error_severity()as errsev,error_state() as errstate,error_procedure() as errproc,error_line()as errline,error_message()as errmsg insert into errorlog values(error_number(),error_severity(),error_state(),error_procedure(),error_line(),error_message()) go
写一个存储过程吧!里面使用一下try catch:
use [your_test]
go
/****** object: storedprocedure [dbo].[gettodaybirthday]
script date: 05/17/2010 15:38:46
author:jinho
desc:获取当天生日的所有人
******/
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure [dbo].[gettodaybirthday]
as
begin try
declare @today datetime;
set @today = getdate();--获取今天的日期
declare @day varchar(2);
set @day =replace(day(@today),0,'');
declare @month varchar(2) ;
set @month = replace(month(@today),0,'');
declare @year varchar(4);
set @year = year(@today);
select * from dbo.userinfo where replace(day(convert(datetime,birthday )),0,'') =@day and replace(month(convert(datetime,brithday)),0,'')=@month and birthday is not null
end try
begin catch
errorlog --调用上面的存储过程,保存错误日志
end catch
说明:error_number(),error_severity(),error_state(),error_procedure(),error_line(),error_message() 这几个函数只能用在catch里面!
发表评论