您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 游標完整實戰指南：宣告、開啟、擷取、關閉、釋放與效能替代方案

編輯：關於SqlServer

前言

游標提供逐列處理結果集的能力，將集合導向的 SQL 行為轉為逐列程式邏輯。但游標**效能極差、鎖定久發、記憶體壓力大**，應視為**最後手段**。本文完整涵蓋語法、範例、參數選項、常見陷阱與集合導向替代方案。

游標生命週期

DECLARE CURSOR 定義
OPEN 執行查詢、建立結果集
FETCH NEXT INTO @vars 逐列讀取
WHILE @@FETCH_STATUS = 0 迴圈處理
CLOSE 釋放結果集鎖定
DEALLOCATE 釋放游標結構記憶體

完整語法與參數選項


DECLARE cursor_name CURSOR
    [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_list ] ];

關鍵選項對照表

選項	說明	效能/行為
LOCAL	僅目前 Batch/Proc 可見 (預設)	避免名稱衝突
GLOBAL	整個連線可見	慎用
FORWARD_ONLY	僅支援 FETCH NEXT (預設)	最輕量、唯讀最佳
SCROLL	支援 FIRST/LAST/PRIOR/NEXT/ABSOLUTE/RELATIVE	需臨時表支援、較重
STATIC	開啟時複製結果集到 tempdb，後續異動不可見	一致性快照、允許 SCROLL
KEYSET	固定鍵值集合，非鍵值更新可見，插入不可見	折衷方案
DYNAMIC	即時反映所有異動	最重、不支援 ABSOLUTE FETCH
FAST_FORWARD	FORWARD_ONLY + READ_ONLY + 內部最佳化 (預設優先)	唯讀單向遍歷首選
READ_ONLY	不可透過游標更新/刪除	允許 STATIC/FAST_FORWARD 最佳化

標準範例：將 TableA 資料複製到 TableB


DECLARE @ColA NVARCHAR(50), @ColB NVARCHAR(50), @ColC NVARCHAR(50);

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT ColA, ColB, ColC FROM TableA;

OPEN Cur;
FETCH NEXT FROM Cur INTO @ColA, @ColB, @ColC;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO TableB (ColA, ColB, ColC) VALUES (@ColA, @ColB, @ColC);
    FETCH NEXT FROM Cur INTO @ColA, @ColB, @ColC;
END

CLOSE Cur;
DEALLOCATE Cur;

進階：游標更新/刪除 (WHERE CURRENT OF)


DECLARE Cur CURSOR LOCAL FORWARD_ONLY FOR
    SELECT Id, Status FROM Orders WHERE Status = 'Pending' FOR UPDATE OF Status;

OPEN Cur;
FETCH NEXT FROM Cur INTO @Id, @Status;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 業務邏輯判斷...
    IF @SomeCondition = 1
        UPDATE Orders SET Status = 'Processing' WHERE CURRENT OF Cur;
    ELSE
        DELETE FROM Orders WHERE CURRENT OF Cur;
    
    FETCH NEXT FROM Cur INTO @Id, @Status;
END
CLOSE Cur; DEALLOCATE Cur;

需宣告 FOR UPDATE；鎖定該列至交易結束。
WHERE CURRENT OF 直接定位當前游標列，無需再次比對鍵值。

游標變數 (Cursor Variable) - 參數傳遞


-- 程序回傳游標
CREATE PROC GetOrders @CustId INT, @Cur CURSOR VARYING OUTPUT
AS
    SET @Cur = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM Orders WHERE CustId = @CustId;
    OPEN @Cur;
GO

-- 呼叫端
DECLARE @MyCur CURSOR, @Id INT, @Amt MONEY;
EXEC GetOrders 123, @Cur = @MyCur OUTPUT;
FETCH NEXT FROM @MyCur INTO @Id, @Amt;
WHILE @@FETCH_STATUS = 0 BEGIN ... FETCH NEXT ... END
CLOSE @MyCur; DEALLOCATE @MyCur;

效能陷阱與替代方案 (必讀)

場景	游標寫法	集合導向替代 (快 10-1000 倍)
逐列計算/呼叫外部 API	WHILE FETCH ... EXEC sp_ext_api	CLR 整合 / Service Broker / 應用層批次處理
逐列條件更新	WHILE FETCH ... UPDATE ... WHERE CURRENT OF	單一 UPDATE 配合 CASE/WHERE： `UPDATE T SET Col = CASE WHEN ... THEN ... ELSE ... END WHERE ...`
逐列插入	WHILE FETCH ... INSERT ...	INSERT ... SELECT ... 或 `MERGE`
逐列刪除	WHILE FETCH ... DELETE WHERE CURRENT OF	DELETE FROM T WHERE ...
跨表同步	雙層游標巢狀	MERGE / INSERT ... SELECT / UPDATE ... JOIN