SQL Server中使用異常處理調試預存程序)

異常處理被普遍認為是T-SQL指令碼編程中的最弱的方面。幸運的是，這一點在SQL Server 2005中得到了改變，因為SQL Server 2005支援結構化異常處理。本文首先關注新特性“TRY……CATCH”的基本構成，然後在SQL Server 2000和SQL Server 2005中對照著看一些T-SQL的例子，這些例子中使用事務代碼故意製造了一些違反約束限制的情況。將來的文章會繼續探討這一主題。

　　在SQL Server之前的版本中，你需要在執行INSERT，UPDATE，DELETE之後立即檢查全域變數“@@error”來處理異常，如果“@@error”變數不為零的話（表示有錯誤），就接著執行一些糾正動作。開發人員常常重複這種與商務邏輯無關的代碼，這會導致重複代碼塊，而且需要與GOTO語句和RETURN語句結合使用。

　　結構化異常處理為控制具有許多動態運行時特性的複雜程式提供了一種強有力的處理機制。目前，這種機制經實踐證明是良好的，許多流行的程式設計語言（比如：微軟的Visual Basic.Net和Visual C#）都支援這種異常處理機制。接下來你會在例子中看到，採用了這種健壯的方法以後，會使你的代碼可讀性和可維護性更好。TRY塊包含了可能潛在失敗的交易性代碼，而CATCH塊包含了TRY塊中出現錯誤時執行的代碼。如果TRY塊中出現了任何錯誤，執行流程被調轉到CATCH塊，錯誤可以被處理，而出錯函數可以被用來提供詳細的錯誤資訊。TRY……CATCH基本文法如下：

BEGIN TRY
　　RAISERROR （'Houston, we have a problem', 16,1）
　　END TRY
　　BEGIN CATCH
　　SELECT ERROR_NUMBER（） as ERROR_NUMBER,
　　ERROR_SEVERITY（） as ERROR_SEVERITY,
　　ERROR_STATE（） as ERROR_STATE,
　　ERROR_MESSAGE（） as ERROR_MESSAGE
　　END CATCH

注意上面指令碼中函數的用法，我們可以用它們代替局部變數和（或者）全域變數。這些函數只應該被用在CATCH塊中，函數功能說明如下：

　　ERROR_NUMBER（） 返回錯誤數量。

　　ERROR_SEVERITY（） 返回錯誤嚴重等級。

　　ERROR_STATE（） 返回錯誤狀態數量。

　　ERROR_PROCEDURE（） 返回出錯位置預存程序或者觸發器的名稱。

　　ERROR_LINE（） 返回程式中引起錯誤的行號。

　　ERROR_MESSAGE（） 返回錯誤資訊的完整文本。錯誤內容包括可替換參數的值，比如：長度，對象名稱或者時間。

　　我會先用SQL Server 2000示範一個簡單例子，然後示範一個SQL Server 2005異常處理的例子。

　　下面是一個簡單的預存程序樣本，先用SQL Server 2000編寫，然後改用SQL Server 2005實現。兩者都從簡單的表開始，我們在對這些表執行插入操作時會違反約束限制。下面是表結構：

create table dbo.Titles
　　（TitleID int Primary Key identity,
　　TitleName nvarchar（128） NOT NULL,
　　Price money NULL constraint CHK_Price check （Price > 0））
　　create table dbo.Authors
　　（Authors_ID int primary key identity,
　　au_fname nvarchar（32） NULL,
　　au_lname nvarchar（64） NULL,
　　TitleID int constraint FK_TitleID foreign key
　　references Titles（TitleID）,
　　CommissionRating int constraint CHK_ValidateCommissionRating
　　Check （CommissionRating between 0 and 100））
　　create table dbo.Application_Error_Log
　　（tablename sysname,
　　userName sysname,
　　errorNumber int,
　　errorSeverity int,
　　errorState int,
　　errorMessage varchar（4000））

預存程序P_Insert_New_BookTitle_2K的原始碼Code
----------------------------------------------

P_Insert_New_BookTitle_2K

----------------------------------------------

create proc P_Insert_New_BookTitle_2K
(@TitleName nvarchar(128),
 @Price money,
 @au_fname nvarchar(32),
 @au_name nvarchar(64),
 @CommissionRating int)
as

declare     @err int,
        @tablename sysname

begin transaction

insert dbo.Titles (TitleName, Price)
values (@TitleName, @Price)

select @err = @@error
if @err <> 0
begin
    select @tablename = 'titles'
    GOTO ERROR_HANDLER
end

insert dbo.Authors  (au_fname, au_lname, TitleID, CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY, @CommissionRating)

if @err <> 0
begin
    select @tablename = 'authhors'
    GOTO ERROR_HANDLER
end

GOTO EXIT_Proc


ERROR_HANDLER:

ROLLBACK TRANSACTION

-- Log the error 
insert dbo.Application_Error_Log (tableName, UserName, errorNumber, errorSeverity, errorState)
values (@tableName, suser_sname(), @err, 0, 0)

EXIT_Proc:

commit tran


----------------------------------------------

你可以看到，這個預存程序包含了非結構化的錯誤處理代碼，這是我們在SQL Server 2005之前使用的方式。

　　我們已經先看到了預存程序P_Insert_New_BookTitle_2K中使用的代碼。你頂多能說：“至少我們有異常處理。”下面的語句執行這個SQL Server 2000下的預存程序。

Code
exec P_Insert_New_BookTitle_2K 'Red Storm Rising',16.99,
　　'Tom','Clancy', 200

在用指定的參數執行預存程序時，對Authors表的插入失敗了，因為傭金費率值無效。我們的約束檢查發現了該無效值，我們可以看到如下錯誤資訊：Code
Msg 547, Level 16, State 0, Procedure P_Insert_New_BookTitle, Line 23 The INSERT statement conflicted with the CHECK constraint "CHK_ValidateCommissionRating". The conflict occurred in database "Adventureworks2005", table "dbo.Authors", column 'CommissionRating'. The statement has been terminated.

這裡的問題是我們不能阻止這些訊息被送到用戶端。所以判斷哪裡出錯的重擔就放到了用戶端的頭上。令人遺憾的是，在有些情況下，這樣的結果對於一些不使用約束限制的應用程式可能足夠了。

　　我們再來試一次，這次我們使用TRY……CATCH代碼塊。

預存程序P_Insert_New_BookTitle_2K5的原始碼Code
----------------------------------------------

----------------------------------------------P_Insert_New_BookTitle_2K5 ----------------------------------------------create proc P_Insert_New_BookTitle_2K5(@TitleName nvarchar(128), @Price money, @au_fname nvarchar(32), @au_name nvarchar(64), @CommissionRating int)asdeclare @err int,@tablename sysname,@errormessage nvarchar(2000)BEGIN TRYbegin transactionselect @errormessage = 'insert into Titles table failed', @tablename = 'Titles'insert dbo.Titles (TitleName, Price)values (@TitleName, @Price)select @errormessage = 'insert into Authors table failed', @tablename = 'Authors'insert dbo.Authors  (au_fname, au_lname, TitleID,                      CommissionRating)values (@au_fname, @au_fname, @@IDENTITY,         @CommissionRating)commit transactionEND TRYBEGIN CATCHROLLBACK TRANSACTION-- Log the error insert dbo.Application_Error_Log (UserName, tableName,                 errorNumber, errorSeverity, errorState, errorMessage)values (suser_sname(), @tableName, ERROR_NUMBER(),              ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())  RAISERROR (@errormessage, 16,1)END CATCH----------------------------------------------

在這段新改進的預存程序中，我們看到使用了TRY……CATCH代碼塊的結構化錯誤處理：

　　要注意SQL Server 2005異常處理代碼是經過簡化的，因此具有更好的可讀性和可維護性。不需要剪下和粘貼異常處理代碼，也不需要使用GOTO語句。執行該預存程序時，你可以看到如下結果：

exec P_Insert_New_BookTitle_2K5 'Red Storm Rising',16.99,
　　'Tom','Clancy', 200
我們用指定的參數執行預存程序，同樣因為傭金費率值無效，對Authors表的插入失敗了。錯誤發生時，程式執行流程跳轉到了CATCH代碼塊，在CATCH代碼塊中我們復原了事務，然後用SQL Server 2005內建的函數給Application_Error_Log表插入一行日誌。

　　新的TRY……CATCH代碼塊無疑使編寫處理錯誤碼更容易，它還可以在任何時候阻止錯誤資訊發送到用戶端。當然這可能需要T-SQL程式員的編程思維有一個轉變，這是一個絕對有必要使用的特性。要記住遷移SQL Server 2000代碼到SQL Server 2005時，如果程式的錯誤處理機制已經設計為舊的發送錯誤到用戶端的方式，那你可能不得不修改應用程式了。從長遠來看，我相信為這種潛在的問題付出努力重新設計是值得的。

 備忘：
http://www.searchdatabase.com.cn/ShowContent_23265.htm