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里面!
您可能感兴趣的文章:
- 在SQL Server的try…catch语句中获取错误消息代码的的语句
- SQL SERVER 里的错误处理（try catch)
- 在SQL Server中使用 Try Catch 处理异常的示例详解
