SQL Server 游標 完整指南：語法、類型、巢狀用法與效能警示

在 T-SQL 預存程序或批次中需逐列處理結果集時，游標 是標準解法。本文系統整理游標的宣告、開啟、讀取、關閉、釋放全流程，涵蓋區域/全域、FORWARD_ONLY/SCROLL 類型、巢狀游標、游標變數，並強調效能代價與替代方案。

一、游標基本語法

DECLARE cursor_name CURSOR [LOCAL | GLOBAL]
    [FORWARD_ONLY | SCROLL]
    FOR select_statement;

• LOCAL（預設）：僅在當前批次/程序可見
• GLOBAL：跨批次可見，需显式 DEALLOCATE 釋放
• FORWARD_ONLY（預設）：僅能 FETCH NEXT 向後讀取
• SCROLL：支援 NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n 隨機定位

二、標準使用流程

-- 1. 宣告
DECLARE c1 CURSOR FOR SELECT xingming FROM yiren;
DECLARE @xingming VARCHAR(20);

-- 2. 開啟
OPEN c1;

-- 3. 讀取首列
FETCH NEXT FROM c1 INTO @xingming;

-- 4. 迴圈處理
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '姓名：' + @xingming;
    FETCH NEXT FROM c1 INTO @xingming;
END

-- 5. 關閉（釋放結果集鎖定）
CLOSE c1;

-- 6. 釋放資源（必做）
DEALLOCATE c1;

@@FETCH_STATUS：0=成功、-1=超出結果集、-2=該列已刪除/不存在。

三、SCROLL 全域游標跨批次範例

-- 批次 1
IF CURSOR_STATUS('global', 'CURSOR_2') != -3 DEALLOCATE CURSOR_2;
DECLARE CURSOR_2 CURSOR GLOBAL SCROLL FOR SELECT xingming, nicheng, xingbie FROM yiren;

OPEN CURSOR_2;
DECLARE @seq INT = 4, @xingming VARCHAR(20), @nicheng VARCHAR(50), @xingbie NCHAR(1);
FETCH ABSOLUTE @seq FROM CURSOR_2 INTO @xingming, @nicheng, @xingbie;
IF @@FETCH_STATUS = 0
    PRINT '第 ' + CAST(@seq AS VARCHAR) + ' 位藝人：' + @xingming;
CLOSE CURSOR_2;
GO

-- 批次 2（同一連線可見全域游標）
OPEN CURSOR_2;
SET @seq = 5;
FETCH ABSOLUTE @seq FROM CURSOR_2 INTO @xingming, @nicheng, @xingbie;
-- ... 處理邏輯
CLOSE CURSOR_2;
GO

-- 批次 3 釋放
DEALLOCATE CURSOR_2;

四、巢狀游標（慎用）

DECLARE c3 CURSOR FOR SELECT yanchuid FROM yanchu;
OPEN c3;
FETCH NEXT FROM c3 INTO @ycid;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '第 ' + CAST(@ycid AS VARCHAR) + ' 場演出參與藝人：';
    DECLARE c4 CURSOR FOR SELECT xingming FROM yiren WHERE yirenid IN (SELECT yirenid FROM yanchuyiren WHERE yanchuid = @ycid);
    OPEN c4;
    FETCH NEXT FROM c4 INTO @xingming;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @xingming;
        FETCH NEXT FROM c4 INTO @xingming;
    END
    CLOSE c4; DEALLOCATE c4;
    FETCH NEXT FROM c3 INTO @ycid;
END
CLOSE c3; DEALLOCATE c3;

警告：巢狀游標極大增加伺服器負載，能用 JOIN、APPLY、集合運算替代時絕不使用游標。

五、游標變數

DECLARE @c1 CURSOR;
SET @c1 = CURSOR FOR SELECT xingming FROM yiren;
OPEN @c1;
FETCH NEXT FROM @c1 INTO @xingming;
PRINT @xingming;
CLOSE @c1; DEALLOCATE @c1;

六、效能警示與替代方案

• 游標逐列處理屬 RBAR (Row-By-Agonizing-Row)，CPU/鎖/記憶體開銷遠大於集合操作
• 優先考慮：CASE 條件邏輯、MERGE、窗口函數、CTE 遞迴、TABLE-VALUED PARAMETERS
• 必須用游標時：LOCAL FORWARD_ONLY READ_ONLY 開銷最小

七、分頁預存程序改寫範例（游標版）

IF @currentpage > 1
BEGIN
    DECLARE @start INT, @count INT = 0;
    SET @start = @currentpage * @pagesize + 1;
    DECLARE @sql NVARCHAR(MAX) = 'DECLARE cursor_1 CURSOR SCROLL FOR SELECT * FROM ' + @tablename + ' ORDER BY ' + @idname;
    EXEC(@sql);
    OPEN cursor_1;
    FETCH RELATIVE @start FROM cursor_1;  -- 定位起始行
    WHILE @@FETCH_STATUS = 0 AND @count < @pagesize
    BEGIN
        SET @count += 1;
        FETCH NEXT FROM cursor_1;
    END
    CLOSE cursor_1; DEALLOCATE cursor_1;
END

掌握游標語法是基本功，但「能不用就不用」才是資深開發者的標誌。集合思維，事半功倍。