您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

游標、交易與錯誤處理：SQL Server 程式設計三大核心機制整合實戰

編輯：關於SqlServer

一、游標：逐列處理的雙面刃

基本結構

DECLARE @BookId INT, @BName VARCHAR(50), @BIndex INT;

DECLARE Book_Cursor CURSOR FOR
SELECT BookId, BookName, BookIndex FROM Bas_BookList;

OPEN Book_Cursor;
FETCH NEXT FROM Book_Cursor INTO @BookId, @BName, @BIndex;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 處理當前列：UPDATE/DELETE/INSERT 等
    FETCH NEXT FROM Book_Cursor INTO @BookId, @BName, @BIndex;
END

CLOSE Book_Cursor;
DEALLOCATE Book_Cursor;

@@FETCH_STATUS：0=成功、-1=失敗、-2=找不到列 (游標結尾)。

客戶端 vs 伺服器端游標

伺服器端游標 (預設)：在 SQL Server 記憶體中運作，支援所有游標類型 (前向/靜態/鍵集/動態)，效能較好。
客戶端游標：由 ODBC 驅動在用戶端快取結果集，僅支援前向/靜態，適用於伺服器不支援的複雜批次/TSQL。一般不建議常規使用。

二、交易：ACID 保證的基石

TSQL 交易控制


BEGIN TRANSACTION Tran1;
BEGIN TRY
    -- 業務邏輯
    COMMIT TRANSACTION Tran1;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION Tran1;
    -- 錯誤記錄/拋送
END CATCH

@@TRANCOUNT：巢狀交易計數器，BEGIN TRAN 遞增，COMMIT 遞減至 0 才真正提交，ROLLBACK 直接歸零回滾至最外層起點。

C# ADO.NET 交易模式


using (var conn = new SqlConnection(connStr)) {
    conn.Open();
    using (var tran = conn.BeginTransaction()) {
        try {
            using (var cmd = new SqlCommand(sql, conn, tran)) {
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        } catch {
            tran.Rollback();
            throw;
        }
    }
}

三、TRY...CATCH：結構化錯誤處理


BEGIN TRY
    -- 可能出錯的 TSQL 陳述式
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrNo,
        ERROR_MESSAGE() AS ErrMsg,
        ERROR_SEVERITY() AS Severity,
        ERROR_STATE() AS State,
        ERROR_LINE() AS ErrLine,
        ERROR_PROCEDURE() AS ProcName;
    -- 可在此記錄錯誤日誌、發送警示
END CATCH

捕捉運行時錯誤 (嚴重度 11-19)，編譯錯誤 (語法錯誤) 不會進入 CATCH。搭配 THROW (SQL 2012+) 重新拋送錯誤保留堆疊。

四、實戰整合：游標在交易中批次處理 + 錯誤回滾

場景：需逐筆處理大量暫存表資料，每筆邏輯複雜，要求全部成功或全部失敗，且需精確定位失敗列。


SET NOCOUNT ON;

BEGIN TRY
    BEGIN TRANSACTION BatchTran;

    DECLARE @ArrearageId BIGINT, @ComId BIGINT, @AgentId BIGINT;
    
    DECLARE A_Cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT Id, ProviceName, CityName FROM Ass_ArrearageTemporary;
    
    OPEN A_Cursor;
    FETCH NEXT FROM A_Cursor INTO @ArrearageId, @ComId, @AgentId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 複雜業務邏輯：驗證、計算、寫入多張表
        -- 例如：INSERT INTO FormalTable ...; UPDATE Summary SET ...;
        
        FETCH NEXT FROM A_Cursor INTO @ArrearageId, @ComId, @AgentId;
    END

    COMMIT TRANSACTION BatchTran;
    
    CLOSE A_Cursor;
    DEALLOCATE A_Cursor;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        -- 確保游標資源釋放再回滾
        IF CURSOR_STATUS('global', 'A_Cursor') >= 0
        BEGIN
            CLOSE A_Cursor;
            DEALLOCATE A_Cursor;
        END
        ROLLBACK TRANSACTION BatchTran;
    END
    -- 記錄錯誤資訊
    INSERT INTO ErrorLog (ErrTime, ErrProc, ErrMsg, ErrLine)
    VALUES (GETDATE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), ERROR_LINE());
    RETURN; -- 結束預存程序
END CATCH

效能優化關鍵點

游標選項：LOCAL FAST_FORWARD (唯讀、僅前進) 效能最佳，避免預設動態游標開銷。
集合式思維優先：能用 INSERT...SELECT、MERGE、UPDATE...JOIN 解決的，堅決不用游標。游標僅處理「必須逐列、邏輯無法集合化」的極少數場景。
批次大小：若資料量極大 (百萬列+)，考慮分批提交 (每 5000 列 Commit 一次) 避免記錄檔暴漲、長鎖定，但需設計可重入/補償邏輯處理部分失敗。
變數作用域：預存程序層級變數在整個執行週期有效，方便游標迴圈跨迭代共享狀態。