在SQL Server 2005數(shù)據(jù)庫(kù)中進(jìn)行錯(cuò)誤捕捉

在SQL Server數(shù)據(jù)庫(kù)中，如果執(zhí)行Transact-SQL時(shí)出現(xiàn)了錯(cuò)誤，我們可以使用兩種捕捉錯(cuò)誤的方法解決此問(wèn)題，一種是在客戶端代碼(如c#、 delphi等)中使用類(lèi)似try...catch的語(yǔ)句進(jìn)行捕捉;另外一種就是在Transact-SQL中利用Transact-SQL本身提供的錯(cuò)誤捕捉機(jī)制進(jìn)行捕捉。如果是因?yàn)門(mén)ransact-SQL語(yǔ)句的執(zhí)行而產(chǎn)生的錯(cuò)誤，如鍵值沖突，使用第一種和第二種方法都可以捕捉，但是如果是邏輯錯(cuò)誤，使用客戶端代碼進(jìn)行捕捉就不太方便了。因此，本文針對(duì)如何使用Transact-SQL進(jìn)行錯(cuò)誤捕捉進(jìn)行了深入的探討。

　　一、非致命錯(cuò)誤(non-fatal error)的捕捉

　　通過(guò)執(zhí)行Transact-SQL而產(chǎn)生的錯(cuò)誤可分為兩種：致命錯(cuò)誤(fatal error)和非致命錯(cuò)誤(non-fatal error)。在Transact-SQL中只可以捕捉非致命錯(cuò)誤(如鍵值沖突)，而無(wú)法捕捉致命錯(cuò)誤(如語(yǔ)法錯(cuò)誤)。在Transact-SQL中可以通過(guò)系統(tǒng)變量@@ERROR判斷最近執(zhí)行的一條語(yǔ)句是否成功執(zhí)行。如果發(fā)生了錯(cuò)誤，@@Error的值大于0，否則值為0。下面舉一個(gè)例子說(shuō)明 @@ERROR的使用。

　　假設(shè)有一個(gè)表table1，在這個(gè)表中有兩個(gè)字段f1，f2。其中f1是主鍵。

　　INSERT INTO table1 VALUES(1, 'aa')

　　INSERT INTO table1 VALUES(1, 'bb') --這條語(yǔ)句將產(chǎn)生一個(gè)錯(cuò)誤

　　IF @@ERROR > 0

　　PRINT '鍵值沖突'

　　當(dāng)執(zhí)行第二條語(yǔ)句時(shí)發(fā)生鍵值沖突錯(cuò)誤，@@ERROR被賦為錯(cuò)誤號(hào)2627，因此輸出結(jié)果顯示'鍵值沖突'。使用@@ERROR系統(tǒng)變量時(shí)需要注意，@@ERROR只記錄最近一次執(zhí)行的Transact-SQL語(yǔ)句所發(fā)生的錯(cuò)誤，如果最近一次執(zhí)行的Transact-SQL沒(méi)有發(fā)生錯(cuò)誤，@@ERROR的值為0。因此，只能在被捕捉的那條Transact-SQL語(yǔ)句后使用@@ERROR。

　　在SQL Server中，不僅可以捕捉系統(tǒng)提供的錯(cuò)誤，還?以自定義錯(cuò)誤。有兩種方法可以定義錯(cuò)誤信息。

　　1. 使用sp_addmessage系統(tǒng)存儲(chǔ)過(guò)程添加錯(cuò)誤信息，然后使用RAISERROR拋出錯(cuò)誤

　　sp_addmessage將錯(cuò)誤號(hào)，錯(cuò)誤級(jí)別、錯(cuò)誤描述等信息添加到系統(tǒng)表中，然后使用RAISERROR根據(jù)相應(yīng)的錯(cuò)誤號(hào)拋出錯(cuò)誤信息。用戶自定義的信息應(yīng)該從50001開(kāi)始。

　　EXEC sp_addmessage @msgnum = 50001, @severity = 16,

　　@msgtext = 'sql encounter an error(%s).',

　　@lang = 'us_english'

　　EXEC sp_addmessage @msgnum = 50001, @severity = 16,

　　@msgtext = 'sql遇到了一個(gè)錯(cuò)誤(%1!).'

　　如果使用的SQL Server版本是非英語(yǔ)版本，在添加本地錯(cuò)誤信息時(shí)必須首先添加英文的錯(cuò)誤信息。錯(cuò)誤描述可以象c語(yǔ)言中的printf的格式字符串一樣使用參數(shù)，如 %s、%d。但要注意的是在英文版的錯(cuò)誤信息中要使用%s、%d等形式，而在本地化的錯(cuò)誤信息中要使用%1!、%2!等形式，在每個(gè)%?(1 <= ? <= n)后需要加一個(gè)!，而且%?的數(shù)目必須和英文版的錯(cuò)誤信息的參數(shù)一致。

　　在未插入本地化錯(cuò)誤信息時(shí)，RAISERROR將使用英文版的錯(cuò)誤信息。當(dāng)插入本地化錯(cuò)誤信息時(shí)，RAISERROR使用本地化的錯(cuò)誤信息。

　　RAISERROR(50001, 16, 1, '測(cè)試')

　　輸出的結(jié)果：

　　服務(wù)器: 消息 50001，級(jí)別 16，狀態(tài) 1，行 1

　　sql遇到了一個(gè)錯(cuò)誤(測(cè)試).

　　其中'測(cè)試'字符串通過(guò)%1傳入本地化的錯(cuò)誤描述字符串中。

　　2.直接使用RAISERROR將錯(cuò)誤拋出

　　使用第一種方法雖然使Transact-SQL語(yǔ)句看上去更整潔(這種方法類(lèi)似于在編程語(yǔ)言中使用常量定義錯(cuò)誤信息，然后在不同的地方通過(guò)錯(cuò)誤編號(hào)引用這些錯(cuò)誤信息。)，但是這樣做卻使錯(cuò)誤信息和數(shù)據(jù)庫(kù)的耦合度增加，因?yàn)槿绻麑⑦@些帶有RAISERROR的Transact-SQL放到別的SQL Server數(shù)據(jù)庫(kù)上執(zhí)行，由于在其它的數(shù)據(jù)庫(kù)中還未添加錯(cuò)誤信息，因此會(huì)產(chǎn)生RAISERROR調(diào)用錯(cuò)誤，除非使用sp_addmessage將所需的錯(cuò)誤信息再加入到其它的數(shù)據(jù)庫(kù)中。

　　基于上述原因，RAISERROR不僅可以根據(jù)錯(cuò)誤代碼拋出錯(cuò)誤信息，也可以直接通過(guò)錯(cuò)誤描述格式字符串拋出錯(cuò)誤信息。

　　RAISERROR('sql遇到了一個(gè)錯(cuò)誤(%s)', 16, 1, '測(cè)試')

　　二、邏輯錯(cuò)誤的捕捉

　　在實(shí)際應(yīng)用中，更多的是由于某些業(yè)務(wù)要求而產(chǎn)生的邏輯錯(cuò)誤。這些錯(cuò)誤無(wú)法通過(guò)@@ERROR進(jìn)行捕捉。如果使用客戶端代碼進(jìn)行捕捉，那么 Transact-SQL必須一條一條地執(zhí)行。如果使用存儲(chǔ)過(guò)程，那么發(fā)生在存儲(chǔ)過(guò)程內(nèi)部的邏輯錯(cuò)誤就很難在客戶端代碼中進(jìn)行捕捉，因此，下面將討論如何使用Transact-SQL捕捉邏輯錯(cuò)誤。

　　所謂邏輯錯(cuò)誤，就是在執(zhí)行完Transact-SQL后，執(zhí)行結(jié)果與業(yè)務(wù)要求的結(jié)果不符而產(chǎn)生的。為了說(shuō)明如何處理邏輯?誤，我們?cè)俳⒁粋€(gè)表 table2，這個(gè)表的結(jié)構(gòu)和table1完全一樣，只是f1字段不再是主鍵了。然后建立一個(gè)存儲(chǔ)過(guò)程，它的功能是在table1和table2中同時(shí)插入一條記錄，但是這條記錄必須滿足兩個(gè)條件。

　　1.f1值不能大于100

　　2.要插入的記錄在table1中不存在，如果存在，在table1和table2中都不插入這條記錄

　　CREATE PROCEDURE p1(@Num int)

　　AS

　　DECLARE @Error int, @RowCount int

　　BEGIN TRANSACTION

　　INSERT INTO table2 VALUES(@Num, 'p')

　　IF @Num > 100

　　BEGIN

　　RAISERROR('%s的值不能大于100。', 16, 1, '@Num')

　　ROLLBACK TRANSACTION

　　RETURN 1

　　END

　　ELSE

　　BEGIN

　　select f1 from table1 where f1 = @Num

　　IF @@ROWCOUNT > 0

　　BEGIN

　　RAISERROR('table1中已經(jīng)存在%d了。', 16, 1, @Num)

　　ROLLBACK TRANSACTION

　　RETURN 2

　　END

　　ELSE

　　BEGIN

　　INSERT INTO table1 VALUES(@Num, 'p')

　　COMMIT TRANSACTION

　　RETURN 0

　　END

　　END

　　在這個(gè)存儲(chǔ)過(guò)程中一開(kāi)始使用BEGIN TRANSACTION顯示地開(kāi)始一個(gè)事務(wù)，然后當(dāng)上述兩種錯(cuò)誤發(fā)生時(shí)使用ROLLBACK TRANSACTION恢復(fù)到初始狀態(tài)，如果成功插入，使用COMMIT TRANSACTION提交改變?？梢酝ㄟ^(guò)如下語(yǔ)句進(jìn)行調(diào)用。

　　DECLARE @ErrNum int

　　EXEC @ErrNUm = p1 2

　　PRINT @ErrNum

　　可以通過(guò)@ErrNum得到p1返回的錯(cuò)誤代碼，如果返回0，表示執(zhí)行成功。

　　SQL Server 2005數(shù)據(jù)庫(kù)中錯(cuò)誤捕捉的新功能

　　雖然在以前的SQL Server版本中可以通過(guò)一些技巧實(shí)現(xiàn)錯(cuò)誤捕捉，但有時(shí)需要增加一些額外的開(kāi)銷(xiāo)，如在p1中使用了SELECT語(yǔ)句。慶幸的是在SQL Server2005中提供了和大多數(shù)編程語(yǔ)言類(lèi)似的try...catch錯(cuò)誤捕捉功能，從而使Transact-SQL第一次可以真正地進(jìn)行錯(cuò)誤捕捉。使用try...catch可以將p1的下半部分改寫(xiě)為如下形式。

　　ELSE

　　BEGIN

　　BEGIN TRY

　　INSERT INTO table1 VALUES(@Num, 'p')

　　COMMIT TRANSACTION

　　RETURN 0

　　END TRY

　　BEGIN CATCH

　　RAISERROR('table1中已經(jīng)存在%d了。', 16, 1, @Num)

　　ROLLBACK TRANSACTION

　　RETURN 2

　　END CATCH

　　END

　　我們可以發(fā)現(xiàn)，這個(gè)改寫(xiě)的部分未使用SELECT查詢table1中是否已經(jīng)有了某條記錄，而是通過(guò)數(shù)據(jù)庫(kù)的約束來(lái)進(jìn)行判斷的。如果鍵值沖突，就產(chǎn)生了錯(cuò)誤，這樣SQL語(yǔ)句就直接跳到BEGIN CATCH中執(zhí)行錯(cuò)誤處理代碼。這樣做效率要比上一個(gè)版本高得多，而且如果將RAISERROR去掉，p1就不會(huì)拋出任何錯(cuò)誤，只是返回了一個(gè)錯(cuò)誤碼，這種做法的好處是有利于客戶端代碼進(jìn)行處理。

　　如果使用SQL Server 2005數(shù)據(jù)庫(kù)，在Transact-SQL中進(jìn)行錯(cuò)誤捕捉，建議盡量使用try...catch，因?yàn)樗鼤?huì)捕捉到未預(yù)料到的錯(cuò)誤，并且會(huì)使 Transact-SQL更易于維護(hù)。當(dāng)然，這樣做就無(wú)法將Transact-SQL移植到SQL Server2000或更低的版本上運(yùn)行，如果想寫(xiě)通用的Transact-SQL，讀者可以使用傳統(tǒng)的方法來(lái)捕捉錯(cuò)誤。