T-SQL 與 SSMS：深入剖析 SQL Server 預存程序中的 While 迴圈游標

大家好，我是 Jayson Chan。在處理 T‑SQL 邏輯時，我們偶爾會遇到需要逐行處理資料的情況，這時許多人第一個想到的就是「游標」（Cursor）。雖然 SQL Server 的強項是「集合導向」（Set‑based）操作，但有時候（例如呼叫外部 API、複雜的商業邏輯，或來源是連結伺服器的資料）游標仍是必要的工具。今天我們就透過一個實際的預存程序範例，深入探討如何在 SSMS 中使用 WHILE 迴圈搭配游標，並點出其中的細節與陷阱。

一、範例程式碼一覽

以下是讀者提供的預存程序，功能是從 Oracle 連結伺服器抓取資料，然後逐筆印出並呼叫另一個程序：

CREATE PROCEDURE [dbo].[SP_ABC]
AS
BEGIN
    DECLARE @F_DATE VARCHAR(10)
    DECLARE @T_DATE VARCHAR(10)

    DECLARE Hello_Cursor CURSOR FOR
        SELECT A, B 
        FROM OPENQUERY(Oracle_SF, 'SELECT * FROM ABC')

    OPEN Hello_Cursor
    FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT(@F_DATE + ' | ' + @T_DATE)
        EXECUTE('Call your Procedure')   -- 假設這裡是動態呼叫其他程序
        FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE
    END

    CLOSE Hello_Cursor
    DEALLOCATE Hello_Cursor
END

這段程式碼包含了 T‑SQL 游標的標準流程：宣告、開啟、提取、迴圈、關閉、釋放。我們將一步步拆解它，並討論實務上需要注意的地方。

二、關鍵技術解析

1. 宣告與開啟游標

DECLARE Hello_Cursor CURSOR FOR
    SELECT A, B 
    FROM OPENQUERY(Oracle_SF, 'SELECT * FROM ABC')

DECLARE CURSOR 定義游標的 SELECT 陳述式。
OPENQUERY 是 SQL Server 用來對連結伺服器執行傳遞查詢的函數。Oracle_SF 是已定義的 Oracle 連結伺服器名稱，它會將內層的 'SELECT * FROM ABC' 直接送給 Oracle 執行，結果再傳回 SQL Server。這是跨資料庫平台擷取資料的常見手法。
游標的型別未指定，所以會使用預設值（全域游標、唯讀、唯向前）。若需要更新或往後提取，應明確指定，例如 SCROLL 或 DYNAMIC。

2. @@FETCH_STATUS 與 WHILE 迴圈

FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE
END

@@FETCH_STATUS 是一個全域變數，回傳上一次 FETCH 指令的狀態。0 表示成功；-1 表示失敗或已超出結果集；-2 表示提取的資料列已不存在（例如使用動態游標時，該列已被刪除）。
典型的游標迴圈會先做一次 FETCH 初始化，然後進入 WHILE @@FETCH_STATUS = 0，在迴圈最後再次 FETCH。這樣可以避免重複處理或無窮迴圈。
注意：範例中第二次 FETCH 誤寫為 INTO @F_DATE, T_DATE（少了 @），正確應為 INTO @F_DATE, @T_DATE，否則會引發語法錯誤。

3. 變數與輸出

DECLARE @F_DATE VARCHAR(10)
DECLARE @T_DATE VARCHAR(10)

宣告兩個 VARCHAR(10) 變數來承接游標提取的兩個欄位。這裡假設 Oracle 的 A 與 B 欄位長度不超過 10，實際應用應調整為符合資料的型別與長度，避免截斷或轉換錯誤。

PRINT(@F_DATE + ' | ' + @T_DATE) 將兩個變數串接後輸出到訊息視窗，方便除錯或記錄。

4. 在迴圈內呼叫其他程序

EXECUTE('Call your Procedure')

這是動態 SQL 的寫法，會執行一個字串內容。這裡假設每次迭代都要呼叫某個預存程序（可能帶參數）。實務上多半會改寫成：

EXEC YourProcedure @Param1 = @F_DATE, @Param2 = @T_DATE

使用參數化呼叫不僅更安全，也避免 SQL injection 風險（雖然動態 SQL 在此風險較低，但仍是良好習慣）。

5. 關閉與釋放游標

CLOSE Hello_Cursor
DEALLOCATE Hello_Cursor

CLOSE 會關閉游標，釋放目前的結果集，但游標的結構仍在，可以重新 OPEN。
DEALLOCATE 則完全移除游標的定義，釋放所有相關資源。兩者成對出現是標準的清理動作，避免記憶體或連線資源洩漏。

三、使用游標的潛在問題與最佳實踐

1. 效能考量

游標是逐列處理，若資料量龐大（數萬列以上），效能通常遠低於集合操作。SQL Server 是針對「一次處理一個集合」最佳化的。因此在寫游標之前，應先思考是否能改用 JOIN、UPDATE 搭配 CASE、或使用 CTE 一次完成。

2. 明確指定游標類型

未指定類型時，SQL Server 會使用預設的 FORWARD_ONLY READ_ONLY 靜態游標，但若查詢來源複雜（如 OPENQUERY），最好明確標註：

DECLARE Hello_Cursor CURSOR FAST_FORWARD FOR ...

FAST_FORWARD 會啟用最有效率的唯向前、唯讀游標，特別適合簡單的逐列讀取。

3. 錯誤處理與交易

範例中沒有 TRY...CATCH，若執行過程中發生錯誤，游標可能沒有正常關閉。建議包覆例外處理：

BEGIN TRY
    OPEN ...
    ...
END TRY
BEGIN CATCH
    IF CURSOR_STATUS('local','Hello_Cursor') > -1
    BEGIN
        CLOSE Hello_Cursor;
        DEALLOCATE Hello_Cursor;
    END
    THROW;  -- 重新拋出錯誤
END CATCH

4. 動態 SQL 的替代方案

範例中的 EXECUTE('Call your Procedure') 很可能只是想呼叫預存程序。如果該程序需要每次傳入不同的參數，應該用參數化呼叫，且最好將參數從變數傳入，例如：

EXEC dbo.YourStoredProcedure @F_DATE, @T_DATE;

5. 連結伺服器的注意事項

OPENQUERY 是靜態的，它會在編譯時固定查詢字串。若需要動態改變 Oracle 的查詢條件，需改用 EXEC ... AT 或建立字串後用 OPENQUERY 搭配變數，但後者須使用動態 SQL（小心 injection）。另外，OPENQUERY 傳回的資料會先完整傳到 SQL Server 再進行游標處理，若 Oracle 資料量極大，網路傳輸會是瓶頸。可考慮在 Oracle 端先篩選或使用其他方式。

四、修改後的完整範例（含錯誤處理與參數化呼叫）

CREATE PROCEDURE [dbo].[SP_ABC_Enhanced]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @F_DATE VARCHAR(10),
            @T_DATE VARCHAR(10),
            @FetchStatus INT;

    DECLARE Hello_Cursor CURSOR FAST_FORWARD FOR
        SELECT A, B 
        FROM OPENQUERY(Oracle_SF, 'SELECT A, B FROM ABC WHERE ROWNUM <= 1000');  -- 範例加上限制

    BEGIN TRY
        OPEN Hello_Cursor;

        FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE;
        SET @FetchStatus = @@FETCH_STATUS;

        WHILE @FetchStatus = 0
        BEGIN
            PRINT @F_DATE + ' | ' + @T_DATE;

            -- 假設有一個預存程序需要這兩個參數
            EXEC dbo.YourTargetProcedure @Param1 = @F_DATE, @Param2 = @T_DATE;

            FETCH NEXT FROM Hello_Cursor INTO @F_DATE, @T_DATE;
            SET @FetchStatus = @@FETCH_STATUS;
        END

    END TRY
    BEGIN CATCH
        PRINT '錯誤發生：' + ERROR_MESSAGE();
        -- 若游標仍開啟，則關閉釋放
        IF CURSOR_STATUS('local', 'Hello_Cursor') >= 0
        BEGIN
            CLOSE Hello_Cursor;
            DEALLOCATE Hello_Cursor;
        END
        THROW;  -- 重新拋出讓應用程式知悉
    END CATCH

    CLOSE Hello_Cursor;
    DEALLOCATE Hello_Cursor;
END

五、什麼時候「該」用游標？

需要對每一列執行不同的預存程序、傳送郵件、呼叫外部 API。
需要複雜的運算，無法用單一 UPDATE 或 SELECT 達成。
處理來自連結伺服器的資料，且無法在遠端完成所有操作。
除錯或分析資料時，逐列追蹤邏輯。

在這些情況之外，強烈建議優先使用「集合式」操作，不僅效能好，程式碼也更簡潔。

六、替代方案：使用暫存表與 While 迴圈

有時候可以用暫存表搭配一個識別欄位，再用 WHILE 迴圈加上 TOP 1 逐列處理，但這本質上仍是游標，而且效率更差。若真的無法用集合，還是回歸游標並加上 FAST_FORWARD 最直接。

七、結語

今天我們深入探討了 SQL Server 預存程序中的游標與 WHILE 迴圈，從語法、@@FETCH_STATUS、OPENQUERY 到錯誤處理。雖然游標常被貼上「效能殺手」的標籤，但在合適的場景下，它仍是 T‑SQL 工具箱裡不可或缺的一員。只要謹慎使用、記得清理資源，並盡可能限制處理的資料量，游標也能穩健地完成任務。

希望這篇文章能幫助你在開發預存程序時更得心應手！

參考資料：Microsoft Docs – DECLARE CURSOR, @@FETCH_STATUS, OPENQUERY

下一篇文章預告：探討 SQL Server 中的動態 SQL 與其安全性議題，敬請期待！

本文由 Jayson Chan 原創，轉載請註明出處：https://freecodeinfosite.blogspot.com/

搜尋此網誌

freeCodeInfo