SQL Server 動態游標定義與使用範例

動態游標特性

動態游標 (DYNAMIC CURSOR) 反映基礎資料的所有變更 (INSERT/UPDATE/DELETE)，游標開啟後的資料異動即時可見，適合需即時同步的場景，但開銷較大。

方法一：動態建立游標變數 (使用 NEWID 命名)

DECLARE @cursor_name VARCHAR(36), @str NVARCHAR(4000);
DECLARE @ckbm VARCHAR(20);

-- 1. 產生唯一游標名稱
SELECT @cursor_name = NEWID();

-- 2. 動態建立游標 (注意雙引號跳脫)
EXEC('DECLARE [' + @cursor_name + '] CURSOR DYNAMIC FOR SELECT f_ckbm FROM tbda_ck');

-- 3. 開啟游標
EXEC('OPEN [' + @cursor_name + ']');

-- 4. 設定 FETCH 動態 SQL (使用 sp_executesql 繫結輸出參數)
SET @str = N'FETCH NEXT FROM [' + @cursor_name + '] INTO @ckbm';

-- 5. 首次擷取
EXEC sp_executesql @str, N'@ckbm VARCHAR(20) OUTPUT', @ckbm OUTPUT;

-- 6. 迴圈處理
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ckbm;  -- 實際業務邏輯替換 PRINT
    
    -- 後續擷取
    EXEC sp_executesql @str, N'@ckbm VARCHAR(20) OUTPUT', @ckbm OUTPUT;
END

-- 7. 關閉並釋放游標
EXEC('CLOSE [' + @cursor_name + ']');
EXEC('DEALLOCATE [' + @cursor_name + ']');

關鍵技術點

1. 游標名稱動態化：使用 NEWID() 避免同一連線多個動態游標名稱衝突。
2. sp_executesql 參數化：FETCH ... INTO @變數 需透過 sp_executesql 繫結 OUTPUT 參數，普通 EXEC 無法取回值。
3. @@FETCH_STATUS：全域變數反映**最後一次 FETCH** 狀態 (0=成功, -1=失敗, -2=資料列不存在)；迴圈條件需緊跟 FETCH 之後檢查。
4. 資源釋放：務必 CLOSE 釋放鎖定 → DEALLOCATE 釋放記憶體結構，否則洩漏資源。

方法二：預存程序傳回游標 (OUTPUT 參數)

CREATE PROCEDURE usp_GetDynamicCursor
    @Cursor CURSOR VARYING OUTPUT
AS
BEGIN
    SET @Cursor = CURSOR DYNAMIC FOR
    SELECT f_ckbm FROM tbda_ck;
    OPEN @Cursor;
END;
GO

-- 呼叫端
DECLARE @MyCursor CURSOR;
EXEC usp_GetDynamicCursor @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor INTO @ckbm;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ckbm;
    FETCH NEXT FROM @MyCursor INTO @ckbm;
END
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

此法較簡潔，適用於固定查詢邏輯封裝於 SP 內。

效能與替代方案建議

• 動態游標鎖定資源久、併發差，大量資料建議改用 **集合式操作** (單一 UPDATE/DELETE/INSERT) 或 **WHILE 迴圈 + 主鍵範圍** 處理。
• 若需逐列處理且資料量可控 (< 1 萬列)，可考慮 FAST_FORWARD (唯讀、順向、最佳化) 或靜態游標 (STATIC) 複製快照。
• SQL 2005+ 推薦使用 WHILE EXISTS (SELECT 1 FROM ...) 配合 TOP (1) 模式替代游標。