儲存過程——異常捕獲&列印異常資訊

shanzm發表於2020-05-13
儲存過程
shanzm-2020年5月13日

0. 背景說明

之前極其的抱怨使用儲存過程，覺得儲存過程不應該出現在現在的新專案中，

但是最近研究儲存過程，發現儲存過程的優點也是及其的耀眼！

之前只盯著儲存過程的缺點，有點一葉障目了。

前一週自己摸索著寫的儲存過程，寫的太幼稚了，不規範。

之前在《儲存過程——C#中呼叫儲存過程的簡單示例》中，

我們在儲存過程中的事務中定義了一個臨時變數@sum,在事務的每一句sql語句後都@sum+@@error,最後根據@sum是否為0來判斷是否有異常，

如果沒有異常則@@error為0，有異常則@@error值為錯誤程式碼，即一定不為0

所以，可以通過最終的@sum判斷是否有異常，

但是有一點要說明的是，@@error對那種重大錯誤無法捕捉，而且@@error只對其前一句sql語句生效

所以，建議還是使用TRY……CATCH

這裡定義一個捕獲異常的儲存過程，實現將儲存過程中出現的異常記錄在資料庫的異常資訊表中。

本示例中，全部的儲存過程都是在一個新建的測試資料庫ShanTest資料庫中進行的

1. 建立異常資訊表ErrorLog

USE [ShanTest]
GO
/****** Object:  Table [dbo].[ErrorLog]    Script Date: 2020-05-11 14:49:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ErrorLog](
	[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,--異常表ID
	[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime]  DEFAULT (getdate()),--異常時間，提供預設值就是當前時間
	[UserName] [sysname] NOT NULL,          --異常使用者名稱，這裡就是dbo,dbo是每個資料庫的預設使用者，具有所有者許可權，全稱：datebaseOwner
	[ErrorNumber] [int] NOT NULL,           --異常程式碼
	[ErrorSeverity] [int] NULL,             --異常嚴重性
	[ErrorState] [int] NULL,                --異常狀態碼
	[ErrorProcedure] [nvarchar](126) NULL,  --拋異常的儲存過程
	[ErrorLine] [int] NULL,                 --錯誤行數
	[ErrorMessage] [nvarchar](4000) NOT NULL,--完整的異常資訊
 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
(
	[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

異常資訊表中欄位如下：
異常表欄位

2. 建立儲存異常資訊的儲存過程

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[pro_ErrorLog]    Script Date: 2020-05-11 14:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author:		shanzm
-- Create date: 2020年5月11日 
-- Description:	儲存儲存過程中捕獲的異常到ErrorLog表
-- =================================================
CREATE PROCEDURE [dbo].[pro_ErrorLog]
@ErrorLogID [int] = 0 OUTPUT 
AS                               
BEGIN
    SET NOCOUNT ON;

        INSERT [dbo].[ErrorLog]
            (
            [UserName],
            [ErrorNumber],
            [ErrorSeverity],
            [ErrorState],
            [ErrorProcedure],
            [ErrorLine],
            [ErrorMessage]
            )
        VALUES
            (
            CONVERT(sysname, CURRENT_USER),--current_user ,這裡值是dbo,dbo是每個資料庫的預設使用者，具有所有者許可權
										   --sysname型別 用於表列、變數以及用於儲存物件名的儲存過程引數，等價與nvachart(120)
            ERROR_NUMBER(),                 --錯誤代號，有很多錯誤代號，可以自行百度
            ERROR_SEVERITY(),               --錯誤的嚴重性
            ERROR_STATE(),                  --錯誤的狀態碼
            ERROR_PROCEDURE(),              --錯誤的儲存過程
            ERROR_LINE(),                   --錯誤行號
            ERROR_MESSAGE()                 --錯誤資訊
            );
        SET @ErrorLogID = @@IDENTITY;--@@IDENTITY 是插入記錄時自動產生的ID
		execute dbo.pro_PrintError;--改儲存過程會將ERROR_MESSAGE()在sql server資訊視窗列印出來
END;

3. 建立在SQL Server中列印異常資訊的儲存過程

在儲存過程 pro_ErrorLog 中儲存異常資訊後，在呼叫這個儲存過程

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[pro_PrintError]    Script Date: 2020-05-11 14:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		shanzm
-- Create date: 2020年5月11日 
-- Description:	在訊息框中列印異常資訊
-- =============================================
CREATE PROCEDURE [dbo].[pro_PrintError]
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information.
    PRINT 'ErrorNumber ： ' +CONVERT(varchar(50), ERROR_NUMBER()) 
    PRINT 'ErrorSeverity ： ' + CONVERT(varchar(5), ERROR_SEVERITY()) 
    PRINT 'ErrorState ：' + CONVERT(varchar(5), ERROR_STATE()) 
    PRINT 'ErrorProcedure ：' + ISNULL(ERROR_PROCEDURE(), '-') 
    PRINT 'ErrorLine ：' + CONVERT(varchar(5), ERROR_LINE());
    PRINT 'ErrorMessage ：' + ERROR_MESSAGE();
END;

4. 建立一個用於測試的儲存過程丟擲異常進行測試

切記我們在業務中需要使用儲存過程的時候，一旦使用了事務，則我們必須在BEGIN CATCH語句中判斷是否有異常丟擲，一旦有異常丟擲，則儲存過程中的事務一定要進行ROLLBACK

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[TestErrorLog]    Script Date: 2020-05-11 15:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		shanzm
-- Create date: 2020年5月11日
-- Description:	用於丟擲異常測試ErrorLog是否可用
-- =============================================
ALTER PROCEDURE [dbo].[TestErrorLog]   
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY---------開始捕捉異常
        BEGIN TRANSACTION------------------開始事務
		update  ShanTest.dbo.Product set Name=NULL where Id=1--這裡隨便建一個資料庫，建一個表，給該表中不允許為空的列插入一個NUll
		--select 1/0;
        COMMIT TRAN -----------------------提交事務
    END TRY-----------結束捕捉異常

    BEGIN CATCH------有異常被捕獲
        IF @@TRANCOUNT > 0---------------------判斷有沒有事務
        BEGIN
            ROLLBACK TRAN----------------------回滾事務
        END
        EXEC pro_ErrorLog----------------------執行儲存過程將錯誤資訊記錄在表當中
    END CATCH--------結束異常處理
END

執行改儲存過程，進行測試：

USE [ShanTest]
GO
EXEC [dbo].[TestErrorLog]
GO

測試結果：
SQL Serve訊息框中現實訊息：

ErrorNumber ： 515
ErrorSeverity ： 16
ErrorState ：2
ErrorProcedure ：TestErrorLog
ErrorLine ：13
ErrorMessage ：不能將值 NULL 插入列 'Name'，表 'ShanTest.dbo.Product'；列不允許有 Null 值。UPDATE 失敗。

該訊息是由pro_PrintError儲存過程列印的
執行測試

同時ErrorLog表中新增了一條記錄：
異常表中資訊

5. 參考資訊

部落格園：SQLServer異常捕獲
部落格園：sqlserver 儲存過程 try catch TRANSACTION

相關文章