SQL Server 交易、異常處理與游標完整教學：ACID 特性、TRY-CATCH、游標操作全攻略

一、 交易 (Transaction) 核心概念

ACID 四大特性

三種交易模式

• 顯式交易：BEGIN TRANSACTION / COMMIT / ROLLBACK
• 自動認可：預設模式，單一陳述式自成交易
• 隱式交易：SET IMPLICIT_TRANSACTIONS ON，Commit/Rollback 後自動開啟新交易

完整交易範本 (含錯誤處理)

BEGIN TRANSACTION tran_bank;
DECLARE @tran_error INT = 0;

BEGIN TRY
    UPDATE bank SET totalMoney = totalMoney - 10000 WHERE userName = 'jack';
    SET @tran_error += @@ERROR;
    
    UPDATE bank SET totalMoney = totalMoney + 10000 WHERE userName = 'jason';
    SET @tran_error += @@ERROR;
END TRY
BEGIN CATCH
    PRINT '異常: ' + ERROR_MESSAGE();
    SET @tran_error += 1;
END CATCH

IF @tran_error > 0
BEGIN
    ROLLBACK TRANSACTION tran_bank;
    PRINT '轉帳失敗，交易回滾';
END
ELSE
BEGIN
    COMMIT TRANSACTION tran_bank;
    PRINT '轉帳成功';
END

二、 異常處理 (TRY...CATCH)

錯誤資訊函數

通用錯誤記錄存儲過程

CREATE PROC proc_error_info AS
SELECT 
    ERROR_NUMBER() AS 錯誤編號,
    ERROR_MESSAGE() AS 錯誤訊息,
    ERROR_SEVERITY() AS 嚴重性,
    ERROR_STATE() AS 狀態碼,
    ERROR_LINE() AS 錯誤行號,
    ERROR_PROCEDURE() AS 錯誤物件名稱;

異常處理限制

• 無法捕捉編譯錯誤 (語法錯誤、物件不存在導致的延遲解析錯誤)
• 無法處理導致交易進入 不可認可狀態 (XACT_STATE = -1) 的錯誤後繼續執行

不可認可交易處理範例

BEGIN TRY
    BEGIN TRAN;
    ALTER TABLE temp_tab DROP COLUMN createTime; -- 故意錯誤
    COMMIT TRAN;
END TRY
BEGIN CATCH
    EXEC proc_error_info;
    
    IF XACT_STATE() = -1
        PRINT '交易不可認可，只能完整回滾';
    ELSE IF XACT_STATE() = 1
        PRINT '交易可認可，可選擇 Commit 或 Rollback';
    
    -- 記錄異常日誌
    EXEC proc_add_exception_log;
END CATCH

三、 游標 (Cursor) 操作詳解

游標宣告語法

DECLARE cursor_name CURSOR 
[ LOCAL | GLOBAL ] 
[ FORWARD_ONLY | SCROLL ] 
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
FOR select_statement 
[ FOR UPDATE [ OF column_name ... ] ]

參數說明

• FORWARD_ONLY：僅支援 FETCH NEXT，效能最佳
• SCROLL：支援 FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE
• READ_ONLY：唯讀，不允許透過游標修改
• SCROLL_LOCKS：讀取時鎖定列，確保更新/刪除成功
• OPTIMISTIC：不鎖定，透過時間戳/校驗和檢測衝突

完整游標操作流程

-- 1. 宣告
DECLARE cursor_stu CURSOR SCROLL FOR SELECT id, name, age FROM student;

-- 2. 開啟
OPEN cursor_stu;
-- 此時 @@CURSOR_ROWS 可得知資料列數

-- 3. 宣告接收變數
DECLARE @id INT, @name NVARCHAR(20), @age INT;

-- 4. 讀取第一列
FETCH FIRST FROM cursor_stu INTO @id, @name, @age;

-- 5. 迴圈處理
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '編號:' + CAST(@id AS VARCHAR) + ', 姓名:' + @name + ', 年齡:' + CAST(@age AS VARCHAR);
    FETCH NEXT FROM cursor_stu INTO @id, @name, @age;
END

-- 6. 關閉 (釋放資料列鎖定)
CLOSE cursor_stu;

-- 7. 刪除 (釋放記憶體結構)
DEALLOCATE cursor_stu;

最佳實踐建議

1. 能用集合操作 (JOIN/WHERE/子查詢) 絕不使用游標
2. 必須用游標時：LOCAL FORWARD_ONLY READ_ONLY 效能最佳
3. 交易處理優先使用 TRY...CATCH + XACT_STATE() 判斷
4. 異常日誌記錄獨立存儲過程，統一管理
5. 避免長時間交易持有鎖定，縮小交易範圍