TRY...CATCH是Sql Server 2005/2008令人印象深刻的新特性.提高了開(kāi)發(fā)人員異常處理能力.沒(méi)有理由不嘗試一下Try.. Catch功能.
* TRY 塊 - 包含可能產(chǎn)生異常的代碼或腳本
* CATCH 塊 - 如果TRY塊出現(xiàn)異常,代碼處理流將被路由到CATCH塊.在這里你可以處理異常,記錄日志等.
Sql Server中的Try Catch和C#,JAVA等語(yǔ)言的處理方式一脈相承.這種一致性才是最大的創(chuàng)新之處.
一、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的一個(gè)存儲(chǔ)過(guò)程,在每個(gè)數(shù)據(jù)庫(kù)操作之后立即必須檢查@@ERROR,進(jìn)行Commit / RollBack該事務(wù).
Sql server 2000中監(jiān)測(cè)錯(cuò)誤,只能通過(guò)監(jiān)測(cè)全局遍歷 @@ERROR.由于@@ERROR會(huì)被下一個(gè)數(shù)據(jù)庫(kù)操作所覆蓋. 所以在每次操作完后必須立即監(jiān)測(cè).
二、SQL SERVER 2005中異常處理
TRY...CATCH是SQL Server 2005提供的更具有可讀性的語(yǔ)法.每個(gè)開(kāi)發(fā)人員都熟悉這種寫(xiě)法.SQL Server 2005仍然支持@@ERROR這種用法.
1.try catch語(yǔ)法:
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.獲得錯(cuò)誤信息的函數(shù)表:
下面系統(tǒng)函數(shù)在CATCH塊有效.可以用來(lái)得到更多的錯(cuò)誤信息:
函數(shù) 描述
ERROR_NUMBER() 返回導(dǎo)致運(yùn)行 CATCH 塊的錯(cuò)誤消息的錯(cuò)誤號(hào)。
ERROR_SEVERITY() 返回導(dǎo)致 CATCH 塊運(yùn)行的錯(cuò)誤消息的嚴(yán)重級(jí)別
ERROR_STATE() 返回導(dǎo)致 CATCH 塊運(yùn)行的錯(cuò)誤消息的狀態(tài)號(hào)
ERROR_PROCEDURE() 返回出現(xiàn)錯(cuò)誤的存儲(chǔ)過(guò)程名稱(chēng)
ERROR_LINE() 返回發(fā)生錯(cuò)誤的行號(hào)
ERROR_MESSAGE() 返回導(dǎo)致 CATCH 塊運(yùn)行的錯(cuò)誤消息的完整文本
簡(jiǎn)單示例:
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回滾/提交事務(wù)的示例
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
三、實(shí)例講解
創(chuàng)建錯(cuò)誤日志表:
CREATE TABLE ErrorLog(errNum INT,ErrSev NVARCHAR(1000),ErrState INT,ErrProc NVARCHAR(1000),ErrLine INT, ErrMsg NVARCHAR(2000))
創(chuàng)建錯(cuò)誤日志記錄存儲(chǔ)過(guò)程:
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
寫(xiě)一個(gè)存儲(chǔ)過(guò)程吧!里面使用一下Try Catch:
USE [Your_Test]
GO
/****** Object: StoredProcedure [dbo].[getTodayBirthday]
Script Date: 05/17/2010 15:38:46
Author:jinho
Desc:獲取當(dāng)天生日的所有人
******/
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 --調(diào)用上面的存儲(chǔ)過(guò)程,保存錯(cuò)誤日志
END CATCH
說(shuō)明:ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE() 這幾個(gè)函數(shù)只能用在Catch里面!
您可能感興趣的文章:- SQL Server出現(xiàn)System.OutOfMemoryException異常的解決方法
- 解決SQL SERVER數(shù)據(jù)庫(kù)備份時(shí)出現(xiàn)“操作系統(tǒng)錯(cuò)誤5（拒絕訪問(wèn)）。BACKUP DATABASE 正在異常終止?！卞e(cuò)誤的解決辦法
- SQL Server自定義異常raiserror使用示例
- SQLServer 連接異常與解決方法小結(jié)
- SQLserver2000 企業(yè)版 出現(xiàn)"進(jìn)程51發(fā)生了嚴(yán)重的異常"錯(cuò)誤的處理方法
- SqlServer異常處理常用步驟
- SQL Server異常代碼處理的深入講解
