| 類別 | 執行位置 | 適用場景 | API/語法 |
| API 伺服器游標 | 伺服器端 | ODBC/OLE DB/ADO/ADO.NET 應用 | API 函數 (SQLSetStmtAttr 等) |
| Transact-SQL 游標 | 伺服器端 | 預存程序、觸發器、批次腳本 | DECLARE CURSOR / OPEN / FETCH / CLOSE / DEALLOCATE |
| API 用戶端游標 | 用戶端 | 小結果集、離線操作 | ADO Recordset (adUseClient) |
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;
伺服器游標四大類型特性對比
| 特性 | 靜態 | 動態 | 只進 | 鍵集驅動 |
| 結果集建立 | 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 集合操作
| 場景 | 推薦方案 |
| 逐列呼叫外部 API / 發送郵件 / 執行動態 SQL | 游標 / WHILE 迴圈 (無集合替代) |
| 依前一列結果計算本列 (遞迴/累加) | 視窗函數 (LAG/LEAD/SUM OVER) / 遞迴 CTE |
| 複雜多步驟邏輯需事務一致性 | 預存程序 + 表變數/暫存表 + 集合 UPDATE |
| 報表明細逐列格式化輸出 | 應用層處理 / FOR XML/JSON 輸出 |
| 大量資料轉換/遷移 | INSERT SELECT / BULK INSERT / SSIS / SqlBulkCopy |
效能優化鐵律
- 能不用就不用:90% 游標可改寫為集合操作
- 最小化結果集:
SELECT 僅需欄位 FROM 表 WHERE 嚴格條件
- 指定最適特性:
LOCAL FAST_FORWARD READ_ONLY 為預設最佳組合
- 避免巢狀游標:指數級效能惡化
- 及時 CLOSE/DEALLOCATE:釋放 tempdb/memory/鎖資源
- 大量操作分批 COMMIT:避免長交易封鎖
游標是手術刀,非瑞士刀。精準切入「必須逐列」場景,其他場合請拋棄。
copyright ©
萬盛學電腦網 all rights reserved