SQL Server 儲存過程的運用

pursuer.chen發表於2017-11-20
SQLServer儲存過程

概述  

 最近因為業務的需求寫了一段時間儲存過程，發現之前寫的儲存過程存在一些不嚴謹的地方，特別是TRY...CATCH中巢狀事務的寫法；雖然之前寫的並沒有錯，但是還是埋藏著很大的隱患在裡面。希望這篇文章能給大家一些參考；文章內容有點長還望耐心閱讀。

 

 

1.插入測試資料

----建立表
DROP TABLE score
GO
CREATE TABLE [dbo].[score](
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    score INT NOT NULL CHECK (score>=0),
    months INT NOT NULL,
    createtime DATETIME NOT NULL DEFAULT GETDATE()
)

---根據姓名月份查詢分數
CREATE INDEX IX_score_name ON score(name,months) include(score)
---根據月份查詢最高分數
CREATE INDEX IX_score_months ON score(months) include(name,score)
---建立姓名和月份組合的唯一索引
CREATE UNIQUE INDEX IX_score_months_name ON score(months,name)

------插入測試資料
TRUNCATE TABLE score

INSERT INTO score(name,score,months) 
VALUES('li',50,10),('chen',70,10),('zhang',80,10),('wang',90,10),('li',50,11),('chen',70,11),('zhang',80,11),('wang',90,11)

SELECT * FROM score;

2.THROW

THROW是在2012版本中引入的，在有些場景當中，應用程式端不做一些合法性的驗證，這些驗證會被放在資料庫端來驗證。當資料庫端驗證輸入的資訊不合法時需要主動丟擲異常來中斷程式碼的執行。

THROW既可以接收錯誤資訊拋錯提示，同時也可以手動丟擲錯誤到CATCH中。語法如下：

;THROW

THROW [ { error_number | @local_variable }, 

        { message | @local_variable }, 

        { state | @local_variable } ]  

[ ; ]

引數

error_number
表示異常的常量或變數。 error_number是int並且必須為大於或等於 50000 且小於或等於 2147483647，如果CATCH中使用RAISERROR來接收錯誤資訊那麼指定的error_number必須在sys.messages 中存在；如果使用CATCH來接收則不需要。

訊息
描述異常的字串或變數。 訊息是nvarchar(2048)。

狀態
在 0 到 255 之間的常量或變數，指示與訊息關聯的狀態。 狀態是tinyint。

注意：

1.THROW程式碼前必須要用分號，因為THROW會中斷程式碼的執行，所以如果將THROW放在CATCH中時必須放在ROLLBACK TRAN之後，否則不會回滾事務導致物件一直處於提交狀態被鎖。

2.THROW放CATCH中可以達到RAISERROR一樣的效果，同時還簡便了程式碼。

3. THROW能返回正確的錯誤程式碼行號，而RAISERROR沒辦法

參考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/throw-transact-sql

3.sp_addmessage

自定義錯誤號

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'Manual cast wrong ',
    @lang = 'us_english'; 

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'手動拋錯',  
    @lang = '簡體中文';

注意：自定義錯誤號必須大於50000

呼叫儲存過程

1.查詢儲存過程

----查詢儲存過程
CREATE PROCEDURE  Pro_score
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=''
)
AS
BEGIN

---查詢指定人分數
IF @Option='GetScore'
   BEGIN
        SELECT name,
               score
        FROM score
        WHERE name=@name

   END

----查詢指定月份最高分數
IF @Option='MonthMaxScore'
   BEGIN
        SELECT Top 1 
             name,
             score
        FROM score
        WHERE months=@months
        ORDER BY score

   END



END

呼叫儲存過程：

EXEC Pro_score @Option='GetScore',@name='li'
EXEC Pro_score @Option='MonthMaxScore',@months=11

3.修改儲存過程

CREATE PROCEDURE [dbo].[Pro_Insert_score]
(@Option VARCHAR(50),
@name VARCHAR(50)='',
@months INT=0,
@score INT=0
) 
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
 IF @Option='InsertScore'
     BEGIN
     
        -----使用事務
        BEGIN TRY  
                BEGIN TRAN 
                INSERT INTO score(name,score,months) 
                VALUES(@name,@score,@months)

                ----插入重複值報錯事務回滾
                INSERT INTO score(name,score,months) 
                VALUES(@name,@score,@months)

                COMMIT TRAN  
                ----執行成功
                RETURN 0
            END TRY  
            BEGIN CATCH 
                IF @@TRANCOUNT > 0
                    ROLLBACK TRAN
                SELECT  @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
                RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ;
                ----執行失敗
                RETURN 1
            END CATCH

     END
 
END

呼叫儲存過程

----呼叫儲存過程2
DECLARE @status INT
EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90
SELECT @status

可以發現使用RAISERROR拋錯出來的行號和訊息號都是錯誤的，50000這個訊息號其實是不存在的，它是保留的一個統一的訊息號。

可以通過查詢sys.message查詢對應的訊息號

SELECT * FROM score WHERE name='chen'
SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052

 4.手動拋錯中斷

手動拋錯也是這篇文章主要要講的一個知識點，在有一些業務場景當中有一些驗證操作需要在資料庫中進行，甚至必須在更新之後進行但是又是正常的提交操作，在這種情況下就需要手動進行驗證是否需要執行下面的程式碼。，見過很多程式設計師寫儲存過程喜歡在每一個判斷的地方加上RETURN操作，目的是為了不執行後面的程式碼，同時又在RETURN前加上ROLLBACK操作。這雖然是一個辦法，但是在事務中運用RETURN是一個很危險的操作，弄不好會導致事務一直處於開啟操作導致表一直被鎖住，在生成環境是很危險的操作。

建議使用THROW來手動進行拋錯，THROW拋錯會相當於觸發一個11-19級別的錯誤，這樣會跳到CATCH中做ROLLBACK操作。

注意：THROW前必須以分號開頭，如果THROW前有程式碼以分號結尾也可以。

CREATE PROCEDURE [dbo].[Pro_score_throw]
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=0,
 @score INT=0
) 
AS
BEGIN
DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
IF @Option='UpdateScore'
    BEGIN
    
             -----使用事務
                  BEGIN TRY  
                        BEGIN TRAN 
                        UPDATE score
                        SET score=score+@score
                        WHERE name=@name AND months=@months

                        ----在有些業務場景有些判斷必須等操作完了才能去做判斷是否能繼續執行下去
                        IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
                        BEGIN
                             
                             ;THROW 60000,'分數不能大於100',111 

                        END
                        COMMIT TRAN  
        
                    END TRY  
                    BEGIN CATCH 
             
                     ROLLBACK TRAN
                        ;THROW
                    END CATCH

        ----執行成功
        RETURN 0
    END

END

呼叫儲存過程

DECLARE @status INT
EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40
SELECT @status

5.儲存過程呼叫儲存過程

CREATE TABLE Tbtran
(Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(10)
)

INSERT INTO Tbtran(Id,Name) VALUES(1,'a'),(2,'b')

建立儲存過程

CREATE PROC SpChangeTranCall
(@Option VARCHAR(50),
@Id INT =NULL,
@Name VARCHAR(50)=NULL OUTPUT
)
AS
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ReturnNum  INT ---Return返回值
DECLARE @OutputName VARCHAR(50)
BEGIN
SET XACT_ABORT ON   
IF @OPTION='a'
   BEGIN
           SELECT 
                @Name=Name
           FROM Tbtran
           WHERE Id=@Id

           IF @Name='a'
           BEGIN
                RETURN 2
           END
           IF @Name='b'
           BEGIN
                RETURN 3
           END

          INSERT INTO Tbtran(Id) VALUES(1)
    END
IF @OPTION='b'
   BEGIN
        BEGIN TRY  
            BEGIN TRAN  

            INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

            ----呼叫儲存過程,內部儲存過程不使用事務
            EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT
            ---接收內部儲存過程OUTPUT返回值
            SELECT @OutputName
            ---判斷呼叫儲存過程是否執行成功
            IF @ReturnNum>0
            BEGIN
                 ROLLBACK TRAN
                 RETURN  @ReturnNum
            END

            COMMIT TRAN 
            ---成功標誌,放在COMMIT後
            RETURN 0 
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); 
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) 
            ---失敗標誌,放在最後
            RETURN 1
        END CATCH 
    END 
END

呼叫儲存過程

DECLARE @a INT
SET @a=-2
EXEC @a=SpChangeTranCall @OPTION='b',@Id=1
SELECT @a AS ReturnNum

儲存過程呼叫儲存過程事務的三種處理方法:

1.內部儲存過程不要包含事務，因為內部ROLLBACK會直接回滾到外部的BEGIN TRAN導致外部的ROLLBACK沒有對應的COMMIT;

2.還有一種方法是在呼叫內部儲存過程之前使用儲存點“SAVE TRAN TRAN1”,同時內部儲存過程的ROLLBACK TRAN必須指定事務儲存點，例如“ROLLBACK TRAN TRAN1”,這樣內部儲存過程回滾就只會回滾到保持點.

3.在外部儲存過程的CATCH塊的ROLLBACK前加上IF @@TRANCOUNT > 0判斷條件

 

如果不需要使用RETURN 1來標誌執行失敗，可以使用THROW來替代RAISERROR來接受返回的錯誤值

ALTER PROC SpChangeTranCall
(@Option VARCHAR(50),
@Id INT =NULL,
@Name VARCHAR(50)=NULL OUTPUT
)
AS
DECLARE @ReturnNum  INT ---Return返回值
DECLARE @OutputName VARCHAR(50)
BEGIN
SET XACT_ABORT ON   
IF @OPTION='a'
   BEGIN
           SELECT 
                @Name=Name
           FROM Tbtran
           WHERE Id=@Id

           IF @Name='a'
           BEGIN
                RETURN 2
           END
           IF @Name='b'
           BEGIN
                RETURN 3
           END

          INSERT INTO Tbtran(Id) VALUES(1)
    END
IF @OPTION='b'
   BEGIN
        BEGIN TRY  
            BEGIN TRAN  

            INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

            ----呼叫儲存過程,內部儲存過程不使用事務
            EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT
            ---接收內部儲存過程OUTPUT返回值
            SELECT @OutputName
            ---判斷呼叫儲存過程是否執行成功
            IF @ReturnNum>0
            BEGIN
                 ROLLBACK TRAN
                 RETURN  @ReturnNum
            END

            COMMIT TRAN 
            ---成功標誌,放在COMMIT後
            RETURN 0 
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN
            ;THROW
        END CATCH 
    END 
END

呼叫儲存過程

DECLARE @a INT
SET @a=-2
EXEC @a=SpChangeTranCall @OPTION='b',@Id=3
SELECT @a AS ReturnNum

注意：THROW接收的返回錯誤行是準確的錯誤行，而RAISERROR返回的錯誤行是不正確的。

6.事務巢狀事務的理解

---事務1
BEGIN TRAN
        ---事務2
        BEGIN TRAN

        COMMIT TRAN /ROLLBACK TRAN 

COMMIT TRAN /ROLLBACK TRAN

對於事務巢狀事務，事務2的ROLLBACK操作會直接回滾到事務1的BEGIN TRAN，會導致事務1的ROLLBACK沒有對應的BEGIN TRAN。處理方法可以在呼叫事務2之前定義一個事務儲存點或者在事務1的ROLLBACK前加上IF @@TRANCOUNT > 0判斷條件是否存在事務需要回滾。

7.SET XACT_ABORT ON

並不是所有的錯誤都能被CATCH所接收。對於嚴重級別為0-10（資訊性訊息）和20-25（致命的訊息）是不能被CATCH所接收的，這時如果在事務中遇到了這類的報錯那麼通用會導致事務處理開啟狀態，這時就需要開啟XACT_ABORT。當開啟XACT_ABORT後只要程式碼中存在報錯就會執行回滾操作，而不管錯誤的級別。例如：

CREATE TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事務1
        BEGIN TRY  
            BEGIN TRAN  
                 INSERT INTO AA SELECT 2  
                 SELECT * FROM AA;
                 INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

由於臨時表#BB不存在，導致插入報錯，但是嚴重級別又小於11導致CATCH接收不到錯誤，這時檢視發現事務處於開啟狀態，而且表AA也被鎖住。

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

加上事務前加上 SET XACT_ABORT ON  

ALTER TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
   SET XACT_ABORT ON  
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事務1
        BEGIN TRY  
            BEGIN TRAN  
                 INSERT INTO AA SELECT 2  
                 SELECT * FROM AA;
                 INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

再次執行

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

沒有處於開啟的事務而且事務也執行了回滾操作。

總結

1.THROW後面的語句不會被執行；如果不需要Return 0和Return 1來作為成功失敗的標誌那麼可以使用THROW來替代RAISERROR，否則使用RAISERROR。不要使用THROW拋錯又使用RAISERROR來接收錯誤，在事務巢狀事務的寫法中如果內部事務使用RAISERROR來接收THROW返回的報錯不會執行後面的ROLLBACK。

2.建議在ROLLBACK前統一加上IF @@TRANCOUNT > 0判斷條件，這樣可以避免因為內部的ROLLBACK回滾或者RETURN操作導致ROLLBACK沒有對應的COMMIT。

3.CATCH只是用來處理TRY報錯之後的邏輯，不要認為程式碼執行到了CATCH的ROLLBACK就會結束處理，除非是在ROLLBACK後加入了RETURN或者THROW之類的中斷程式碼執行的命令，否則程式碼還將繼續執行ROLLBACK之後的程式碼甚至END CATCH之後的程式碼（如果存在）。

4.如果儲存過程呼叫儲存過程，內部的儲存過程不使用事務也不需要使用TRY來接收錯誤，可以參考上面的“5.儲存過程呼叫儲存過程”。

5.雖然OUTPUT也能用來接收儲存過程的執行情況，但是RETURN的作用可以跳出儲存過程不執行後面的程式碼，所以避免即使用RETURN作為執行成功失敗的標誌又使用OUTPUT來標誌執行成功和失敗的標記，OUTPUT更多用來返回內部儲存過程執行結果的值而不是用來做成功失敗的標誌。

 

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

 

相關文章