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 ErrorLogAS 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[getTodayBirthday]ASBEGIN 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里面!
新聞熱點
疑難解答
