萬盛學電腦網

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

SQL Server 游標完整指南:分類、語法、四大類型特性與最佳實務

游標提供結果集逐列處理能力,但濫用會嚴重拖垮效能。本文系統梳理游標分類、T-SQL 語法、四大伺服器游標特性、替代方案與使用準則。

游標分類

T-SQL 游標完整語法流程

-- 1. 宣告變數接收欄位值
DECLARE @Id INT, @Name NVARCHAR(50), @Amount DECIMAL(18,2);

-- 2. 宣告游標 (可指定特性)
DECLARE Cur CURSOR 
    [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    FOR
    SELECT Id, Name, Amount FROM Sales WHERE Status = 'Pending';

-- 3. 開啟游標 (執行 SELECT、建立結果集)
OPEN Cur;

-- 4. 取得第一列
FETCH NEXT FROM Cur INTO @Id, @Name, @Amount;

-- 5. 迴圈處理
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 業務邏輯: 更新、插入、呼叫其他 SP
    UPDATE Accounts SET Balance += @Amount WHERE Id = @Id;
    
    FETCH NEXT FROM Cur INTO @Id, @Name, @Amount;
END

-- 6. 關閉游標 (釋放結果集鎖定,保留定義)
CLOSE Cur;

-- 7. 釋放游標資源 (必須執行)
DEALLOCATE Cur;

伺服器游標四大類型特性對比

類別執行位置適用場景API/語法
API 伺服器游標伺服器端ODBC/OLE DB/ADO/ADO.NET 應用API 函數 (SQLSetStmtAttr 等)
Transact-SQL 游標伺服器端預存程序、觸發器、批次腳本DECLARE CURSOR / OPEN / FETCH / CLOSE / DEALLOCATE
API 用戶端游標用戶端小結果集、離線操作ADO Recordset (adUseClient)
特性靜態動態只進鍵集驅動
結果集建立OPEN 時複製至 tempdb動態反映基礎表變更OPEN 時建立OPEN 時建立鍵集至 tempdb
可捲動✅ (FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE)✅❌ (僅 NEXT)✅
反映他人 INSERT❌✅✅❌ (需重開)
反映他人 UPDATE (鍵欄)❌✅✅✅ (鍵值不變)
反映他人 UPDATE (非鍵欄)❌✅✅✅
反映他人 DELETE✅ (顯示已刪除列)✅✅✅ (鍵值留存)
並發/鎖定低 (快照隔離)高 (持續鎖定/版本存儲)中中
效能開銷中 (tempdb 複製)高低中
預設類型----

游標特性選擇指南

  • FAST_FORWARD = FORWARD_ONLY + READ_ONLY + 優化 (小結果集首選)
  • STATIC:需反覆瀏覽、不需即時資料、結果集 < 1萬列
  • DYNAMIC:極少用,極高開銷,除非必須即時看見所有變更
  • KEYSET:需捲動、需看見非鍵欄更新、可接受新增列不可見
  • READ_ONLY:明確不更新 → 優化器可選擇更佳計畫
  • LOCAL (預設):批次/程序結束自動清理;GLOBAL:跨批次存活,需手動 DEALLOCATE

何時使用游標 vs 集合操作

效能優化鐵律

  1. 能不用就不用:90% 游標可改寫為集合操作
  2. 最小化結果集:SELECT 僅需欄位 FROM 表 WHERE 嚴格條件
  3. 指定最適特性:LOCAL FAST_FORWARD READ_ONLY 為預設最佳組合
  4. 避免巢狀游標:指數級效能惡化
  5. 及時 CLOSE/DEALLOCATE:釋放 tempdb/memory/鎖資源
  6. 大量操作分批 COMMIT:避免長交易封鎖

游標是手術刀,非瑞士刀。精準切入「必須逐列」場景,其他場合請拋棄。

copyright © 萬盛學電腦網 all rights reserved
場景推薦方案
逐列呼叫外部 API / 發送郵件 / 執行動態 SQL游標 / WHILE 迴圈 (無集合替代)
依前一列結果計算本列 (遞迴/累加)視窗函數 (LAG/LEAD/SUM OVER) / 遞迴 CTE
複雜多步驟邏輯需事務一致性預存程序 + 表變數/暫存表 + 集合 UPDATE
報表明細逐列格式化輸出應用層處理 / FOR XML/JSON 輸出
大量資料轉換/遷移INSERT SELECT / BULK INSERT / SSIS / SqlBulkCopy