萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 存儲過程實戰:事務控制、異常捕獲回滾、數據遷移不重複導入模式

場景

舊系統數據遷移至新表,要求冪等(重複執行不產生重複數據)、原子(全成功或全失敗)、可追蹤(返回狀態碼)。

完整存儲過程

USE [New_WZJW]
GO
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

CREATE PROC [dbo].[成績管理_過程化成績錄入_數據遷移]
    @kcId VARCHAR(50)  -- 課程 ID 參數
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN

        -- 核心:NOT EXISTS 保證冪等,避免重複導入
        INSERT INTO [web_inputCj_xsxkb] 
            ([xh], [xm], [xxxzdm], [xxxzmc], [cjxzdm], [kcid], [lrsj], [manuAdd])
        SELECT 
            xh, xm, xxxzdm, xxxzmc, cjxzdm, kcid, lrsj, manualAdd
        FROM web_inputCj_cjb AS a
        WHERE a.kcId = @kcId
          AND NOT EXISTS (
              SELECT 1 FROM web_inputCj_xsxkb AS b
              WHERE a.xh = b.xh AND a.kcId = b.kcid
          );

        COMMIT TRAN
        RETURN 0  -- 成功
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN
        -- 可選:記錄錯誤日誌
        -- INSERT INTO ErrLog (...) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), @kcId, GETDATE())
        RETURN 1  -- 失敗
    END CATCH
END

關鍵點解析

  1. TRY...CATCH + 顯式事務:SQL Server 2005+ 標準錯誤處理模式,捕獲運行時錯誤(非編譯錯誤)。
  2. @@TRANCOUNT 守護:CATCH 中判斷事務計數再回滾,防止嵌套調用時誤回滾外層事務。
  3. 冪等寫法:NOT EXISTS (SELECT 1 FROM Target WHERE PK = Source.PK) 確保同一源數據只導入一次,支援重跑。
  4. 返回值約定:0=成功,非0=失敗,應用層據此判斷並記錄日誌/告警。
  5. SET NOCOUNT ON:抑制「(n 行受影響)」消息,減少網絡往返,提升性能。

調用示例

DECLARE @Ret INT
EXEC @Ret = [dbo].[成績管理_過程化成績錄入_數據遷移] @kcId = 'CS101'
IF @Ret = 0
    PRINT '遷移成功'
ELSE
    PRINT '遷移失敗,請檢查錯誤日誌'

擴展:分批遷移大表(避免長事務鎖表)

WHILE 1=1
BEGIN
    BEGIN TRAN
    INSERT TOP (5000) INTO Target ...
    SELECT ... FROM Source WHERE ... AND NOT EXISTS (...)
    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRAN
        BREAK
    END
    COMMIT TRAN
    WAITFOR DELAY '00:00:00.05' -- 釋放鎖間隔
END
copyright © 萬盛學電腦網 all rights reserved