游標是資料庫引擎為結果集開啟的**伺服器端緩衝區**,提供逐行導航與操作能力。它將關係式集合操作轉化為程式化的迭代處理,橋接「集合思維」與「程序思維」。
-- 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 |
DBCC CHECKDB、BACKUP、sp_update_job。LOCAL 避免名稱衝突。FAST_FORWARD READ_ONLY。FETCH NEXT 統一風格,避免 PRIOR/FIRST/LAST 除非 SCROLL。TRY...CATCH 包裹迴圈體,確保 CLOSE/DEALLOCATE 在 FINALLY 執行。OFFSET/FETCH 分頁或 BULK INSERT。