萬盛學電腦網

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

SQL Server 游標完整指南:概念、語法、範例、效能陷阱與集合式替代方案

什麼是游標?

游標是資料庫引擎為結果集開啟的**伺服器端緩衝區**,提供逐行導航與操作能力。它將關係式集合操作轉化為程式化的迭代處理,橋接「集合思維」與「程序思維」。

游標生命週期

-- 1. 宣告
DECLARE cursor_name CURSOR [LOCAL | GLOBAL] 
    [FORWARD_ONLY | SCROLL] 
    [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] 
    [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] 
FOR select_statement;

-- 2. 開啟
OPEN cursor_name;

-- 3. 擷取第一行
FETCH NEXT FROM cursor_name INTO @var1, @var2, ...;

-- 4. 迴圈處理
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 業務邏輯
    FETCH NEXT FROM cursor_name INTO @var1, @var2, ...;
END

-- 5. 關閉與釋放
CLOSE cursor_name;
DEALLOCATE cursor_name;

游標類型選擇指南

類型特性適用場景開銷
FAST_FORWARD唯讀、僅向前、自動優化 (STATIC/DYNAMIC)單向遍歷、無需更新、效能最佳低
STATIC快照複本、不反映並發變更、可雙向捲動需固定時間點視圖、報表查詢中 (建立 TempDB 複本)
KEYSET鍵值固定、非鍵值變更可見、可捲動需感知並發更新、不需感知新增/刪除中高
DYNAMIC完全反映並發變更、可捲動極少見,極高開銷最高

預設:LOCAL FORWARD_ONLY DYNAMIC。強烈建議 明確指定 LOCAL FAST_FORWARD READ_ONLY (唯讀遍歷) 或 LOCAL FAST_FORWARD (需更新)。

完整範例:跨表資料同步

DECLARE @id INT, @name NVARCHAR(50), @val DECIMAL(18,2);

DECLARE sync_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT Id, Name, Value FROM SourceTable WHERE Status = 1;

OPEN sync_cur;
FETCH NEXT FROM sync_cur INTO @id, @name, @val;

WHILE @@FETCH_STATUS = 0
BEGIN
    MERGE TargetTable AS tgt
    USING (SELECT @id AS Id, @name AS Name, @val AS Value) AS src
    ON tgt.Id = src.Id
    WHEN MATCHED THEN UPDATE SET Name = src.Name, Value = src.Value
    WHEN NOT MATCHED THEN INSERT (Id, Name, Value) VALUES (src.Id, src.Name, src.Value);

    FETCH NEXT FROM sync_cur INTO @id, @name, @val;
END

CLOSE sync_cur;
DEALLOCATE sync_cur;

效能陷阱與替代方案

陷阱症狀集合式替代
逐行 UPDATE/INSERT大量 Round-trip、鎖爭用、記錄檔碎片MERGE / INSERT...SELECT / UPDATE...JOIN
游標內發動動態 SQL編譯風暴、計畫快取汙染參數化單一陳述式、Table-Valued Parameter
巢狀游標指數級複雜度、TempDB 壓力JOIN / APPLY / 窗口函數 / CTE
長時間持有游標阻塞鎖升級、TempDB 版本存放區膨脹縮短交易範圍、READ_COMMITTED_SNAPSHOT

何時必須用游標?

  • 呼叫外部資源 (Web Service、CLR、xp_cmdshell) 需逐行參數。
  • 動態 DDL (依行建立分區、索引、統計)。
  • 維護作業:依名單循環 DBCC CHECKDB、BACKUP、sp_update_job。
  • 遞歸階層處理 (配合 WHILE 迴圈) 在不支援遞歸 CTE 的舊版本。

最佳實踐清單

  1. 永遠加 LOCAL 避免名稱衝突。
  2. 唯讀遍歷加 FAST_FORWARD READ_ONLY。
  3. FETCH NEXT 統一風格,避免 PRIOR/FIRST/LAST 除非 SCROLL。
  4. TRY...CATCH 包裹迴圈體,確保 CLOSE/DEALLOCATE 在 FINALLY 執行。
  5. 大量資料改用 OFFSET/FETCH 分頁或 BULK INSERT。
copyright © 萬盛學電腦網 all rights reserved