DECLARE CURSOR (Transact-SQL)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

定義 Transact-SQL 伺服器資料指標的屬性，例如立資料指標運作時的捲動行為以及用以建立結果集的查詢。 DECLARE CURSOR 可接受採用 ISO 標準以及使用 Transact-SQL 延伸模組的語法。

Transact-SQL 語法慣例

Syntax

ISO 語法：

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Transact-SQL 擴充語法：

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_name [ , ...n ] ] ]
[ ; ]

引數

cursor_name

定義的 Transact-SQL 伺服器數據指標名稱。 cursor_name 必須符合識別碼的規則。

INSENSITIVE

定義一個資料指標，它會建立資料暫存複本供資料指標本身使用。 對數據指標的所有要求都會從 中的 tempdb這個臨時表回答。 因此，對基表所做的修改不會反映在對這個數據指標所擷取所傳回的數據中，而且此數據指標不允許修改。 使用 ISO 語法時若省略了 INSENSITIVE，則任何使用者對基礎資料表所做的已認可刪除及更新動作，都會反映在後續的擷取中。

SCROLL

指定提供所有擷取選項 (FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)。 如果未 SCROLL 在 ISO DECLARE CURSOR中指定 ， NEXT 則是唯一支援的擷取選項。 SCROLL 如果 FAST_FORWARD 也指定，則無法指定 。 如果未 SCROLL 指定，則只有提取選項 NEXT 可用，而資料指標會 FORWARD_ONLY變成 。

select_statement

SELECT定義數據指標結果集的標準語句。 在資料指標宣告的select_statement內不允許 關鍵字FOR BROWSE和 INTO 。

如果 select_statement 中的子句與要求的資料指標類型功能相衝突，SQL Server 會隱含地將資料指標轉換為其他類型。

READ ONLY

防止利用這個資料指標進行更新。 數據指標無法在 或 DELETE 語句的 UPDATE 子句中WHERE CURRENT OF參考。 這個選項會覆寫要更新之資料指標的預設功能。

UPDATE [ OF column_name [ ,...n ] ]

在資料指標內定義可更新的資料行。 如果 OF <column_name> [, <... n> ] 指定 ，則只有列出的數據行允許修改。 如果指定 UPDATE 時沒有同時指定資料行清單，則可更新所有的資料行。

cursor_name

定義的 Transact-SQL 伺服器數據指標名稱。 cursor_name 必須符合識別碼的規則。

LOCAL

指定已建立資料指標的批次、預存程序或觸發程序，其資料指標的範圍為本機範圍。 資料指標名稱只在這個範圍內有效。 批次、預存程序或觸發程序內的區域資料指標變數或是預存程序 OUTPUT 參數可以參考資料指標。 OUTPUT 參數是用來將本機資料指標傳回呼叫的批次、預存程序或觸發程序，它們可將參數指派到資料指標變數，以在預存程序中止後參考資料指標。 當批次、預存程序或觸發程序結束時，除非在 OUTPUT 參數中傳回資料指標，否則，會隱含地將資料指標取消配置。 如果它傳回參數，當參考它的最後一個 OUTPUT 變數解除分配或超出範圍時，就會取消配置數據指標。

GLOBAL

指定連接的資料指標範圍為全域。 連接所執行的任何預存程序或批次內都可以參考資料指標名稱。 只有在中斷連接時，才會隱含地取消配置資料指標。

FORWARD_ONLY

指定資料指標只能向前移動，以及從第一個資料列捲動到最後一個資料列。 FETCH NEXT 是唯一支援的擷取選項。 在擷取數據列時，目前使用者所做的所有插入、更新和刪除語句，都會在影響結果集中的數據列時顯示。 不過，由於數據指標無法向後捲動，因此在擷取數據列之後對資料庫中的數據列所做的變更無法透過數據指標顯示。 順向資料指標預設是動態的，這表示當處理目前的資料列時會偵測到所有變更。 這會加速資料指標的開啟，並讓結果集顯示對基礎資料表所做的更新。 雖然正向數據指標不支援向後捲動，但應用程式可以關閉並重新開啟數據指標，回到結果集的開頭。

如果指定 FORWARD_ONLY 但沒有包含 STATIC、KEYSET 或 DYNAMIC 關鍵字，則資料指標會以動態資料指標運作。 若 FORWARD_ONLY 未指定 或 SCROLL ， FORWARD_ONLY 則為預設值，除非指定 關鍵字 STATIC、 KEYSET或 DYNAMIC 。 STATIC、KEYSET 和 DYNAMIC 資料指標預設為 SCROLL。 不同於 ODBC 和 ADO 等資料庫 API，STATIC、KEYSET 和 DYNAMIC Transact-SQL 資料指標皆支援 FORWARD_ONLY。

STATIC

指定資料指標一律會以第一次開啟資料指標時的原狀顯示結果集，並建立資料的暫存複本以供資料指標使用。 對數據指標的所有要求都會從 中的 tempdb這個臨時表回答。 因此，對基表所做的插入、更新和刪除不會反映在對這個數據指標進行擷取所傳回的數據中，而且此數據指標不會偵測對數據指標開啟之後對結果集成員資格、順序或值所做的變更。 靜態數據指標可能會偵測自己的更新、刪除和插入，但不需要這麼做。

例如，假設靜態資料指標擷取一個資料列，而其他應用程式接著更新該資料列。 如果應用程式從靜態資料指標重新擷取該資料列，它所看到的值會保持不變，即使其他應用程式已進行變更亦然。 支援所有捲動類型。

KEYSET

指定在開啟資料指標時，修正資料指標中之資料列的成員資格和順序。 唯一識別數據列的索引鍵集合會內建在稱為索引鍵集的數據表中tempdb。 這個資料指標會提供在靜態與動態資料指標之間偵測變更的功能。 如同靜態數據指標，它不一定會偵測結果集成員資格和順序的變更。 如同動態資料指標，它會偵測結果集中的資料列值變更。

索引鍵集驅動資料指標是由稱為索引鍵集的一組唯一識別碼 (索引鍵) 所控制。 索引鍵是從結果集中唯一識別資料列的一組資料行建立的。 索引鍵集是一組取自由查詢陳述式傳回之所有資料列的索引鍵值。 使用索引鍵集驅動資料指標，會在資料指標中為每個資料列建置和保留一個索引鍵，並儲存在用戶端工作站或伺服器上。 當您存取每個資料列時，會使用此預存金鑰從資料來源擷取目前的資料值。 在索引鍵集驅動資料指標中，當完整擴展索引鍵集時，結果集的成員資格會遭到凍結。 之後，影響成員資格的新增或更新，在重新開啟之前，不會是結果集的一部分。

當使用者捲動結果集時，可看見資料值的變更 (索引鍵集擁有者或其他處理序所做的變更)：

• 如果刪除數據列，則嘗試擷取數據列會傳回 ，@@FETCH_STATUS-2因為已刪除的數據列會顯示為結果集中的間距。 索引鍵集中存在資料列的索引鍵，但結果集中不再有此資料列。

• 只有在關閉並重新開啟資料指標之後，才能看見其他處理序在資料指標外部執行的插入。 在結果集結尾可看見從資料指標內部執行的插入。

• 從資料指標之外更新索引鍵值，類似於先刪除舊資料列，再插入新資料列。 看不到具有新值的數據列，而且嘗試擷取具有舊值的數據列會傳回 @@FETCH_STATUS 的 -2。 如果更新是藉由指定 WHERE CURRENT OF 子句透過資料指標完成，新值的值便為可見。

DYNAMIC

定義資料指標，在您捲動資料指標並擷取新的記錄時，反映結果集中資料列的所有資料變更，不論這些變更發生於資料指標內部，還是其他使用者在資料指標外部所做的變更。 因此，您可以透過資料指標看到所有使用者執行的全部 UPDATE、INSERT 和 DELETE 陳述式作業。 每次提取時，資料列的資料值、順序和成員資格都有可能改變。 動態數據指標不支援擷 ABSOLUTE 取選項。 在認可數據指標之前，更新 在數據指標外部建立的 （除非數據指標交易隔離等級設定為 UNCOMMITTED）。

例如，假設動態數據指標擷取兩個數據列，而另一個應用程式接著會更新其中一個數據列，並刪除另一個數據列。 如果動態數據指標接著擷取那些數據列，它就不會找到已刪除的數據列，但它會顯示更新數據列的新值。

FAST_FORWARD

指定 FORWARD_ONLY、READ_ONLY 資料指標，且啟用效能最佳化。 FAST_FORWARD 如果 SCROLL 或 FOR_UPDATE 也指定，則無法指定 。 這種類型的數據指標不允許從數據指標內部修改數據。

READ_ONLY

防止利用這個資料指標進行更新。 數據指標無法在 或 DELETE 語句的 UPDATE 子句中WHERE CURRENT OF參考。 這個選項會覆寫要更新之資料指標的預設功能。

SCROLL_LOCKS

指定藉由資料指標進行的定位更新或刪除一定會成功。 當資料列讀入資料指標時，SQL Server 會鎖定這些資料列，以確保之後可對其加以修改。 SCROLL_LOCKS 如果 FAST_FORWARD 或 STATIC 也指定，則無法指定 。

OPTIMISTIC

指定透過數據指標進行的位置更新或刪除不會成功，如果數據列在數據指標中讀取後已更新。 SQL Server 不會在數據指標中讀取數據列時鎖定數據列。 它會改用 timestamp 資料行值的比較，或者，如果資料表沒有 timestamp 資料行則使用總和檢查碼值，來判斷在將資料列讀入資料指標之後，該資料列是否已被修改。 如果修改了資料列，試圖執行的定位更新或刪除便會失敗。 OPTIMISTIC 如果 FAST_FORWARD 也指定，則無法指定 。

TYPE_WARNING

指定當資料指標從要求的類型隱含地轉換成另一個類型時，便傳送一則警告訊息給用戶端。

select_statement

SELECT定義數據指標結果集的標準語句。 數據指標宣告select_statement不允許關鍵詞COMPUTE、FOR BROWSECOMPUTE BY 、 和 。INTO

如果 select_statement 中的子句與要求的資料指標類型功能相衝突，SQL Server 會隱含地將資料指標轉換為其他類型。

FOR UPDATE [ OF column_name [ ,...n ] ]

在資料指標內定義可更新的資料行。 如果提供了 OF <column_name> [, <... n>]，便只允許修改列出的資料行。 若指定 UPDATE 時未加上資料行清單，除非指定 READ_ONLY 這個並行選項，否則所有資料行皆可更新。

備註

DECLARE CURSOR 會定義 Transact-SQL 伺服器資料指標的屬性，例如立資料指標運作時的捲動行為以及用以建立結果集的查詢。 OPEN 陳述式可擴展結果集，而 FETCH 會從結果集中傳回一個資料列。 CLOSE 陳述式會釋放與資料指標相關聯的目前結果集。 DEALLOCATE 陳述式則會釋放資料指標所使用的資源。

DECLARE CURSOR 陳述式的第一種格式是使用 ISO 語法來宣告資料指標的行為。 DECLARE CURSOR 的第二種形式是使用 Transact-SQL 延伸模組，可讓您透過與 ODBC 或 ADO 資料庫 API 資料指標函數中相同的資料指標類型來定義資料指標。

您無法混合這兩種形式。 如果您在 關鍵詞之前CURSOR指定 SCROLL 或 INSENSITIVE 關鍵詞，就無法在 和 FOR <select_statement> 關鍵詞之間CURSOR使用任何關鍵詞。 如果您在和 關鍵詞之間CURSOR指定任何關鍵詞，則不能在 關鍵詞之前CURSOR指定 SCROLL 或 INSENSITIVE 。FOR <select_statement>

DECLARE CURSOR如果使用 Transact-SQL 語法未指定READ_ONLY、 OPTIMISTIC或 SCROLL_LOCKS， 則預設值如下：

• SELECT如果語句不支援更新（許可權不足、存取不支援更新的遠端資料表等等），則資料指標為 READ_ONLY。

• STATIC 和 FAST_FORWARD 資料指標預設為 READ_ONLY。

• DYNAMIC 和 KEYSET 資料指標預設為 OPTIMISTIC。

數據指標名稱只能由其他 Transact-SQL 語句參考。 資料庫 API 函式無法參考它們。 例如，在宣告數據指標之後，無法從 OLE DB、ODBC 或 ADO 函式或方法參考數據指標名稱。 數據指標數據列無法使用 API 的擷取函式或方法來擷取;數據列只能由 Transact-SQL FETCH 語句擷取。

宣告數據指標之後，可以使用這些系統預存程式來判斷數據指標的特性。

變數可用來做為宣告數據指標之select_statement的一部分。 宣告數據指標之後，數據指標變數值不會變更。

權限

DECLARE CURSOR 的權限預設會授與在資料指標所使用的檢視、資料表和資料行上有 SELECT 權限的任何使用者。

限制

您無法在具有叢集資料行存放區索引的資料表上，使用資料指標或觸發程序。 此限制不適用於非叢集資料行存放區索引。 您可以在具有非叢集資料行存放區索引的資料表上，使用資料指標和觸發程序。

範例

A. 使用基本數據指標和語法

在開啟這個資料指標時所產生的結果集內，包含了資料表的所有資料列及所有資料行。 可以更新這個資料指標，而且所有更新和刪除都會在針對這個資料指標所做的提取中表示。 FETCH NEXT 是唯一可用的擷取， SCROLL 因為未指定 選項。

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. 使用巢狀數據指標來產生報表輸出

下列範例顯示如何讓資料指標形成巢狀結構，以產生複雜報告。 內部資料指標宣告給每個供應商。

SET NOCOUNT ON;

DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
    @message VARCHAR(80), @product NVARCHAR(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

相關內容

• @@FETCH_STATUS (Transact-SQL)
• CLOSE (Transact-SQL)
• 資料指標 (Transact-SQL)
• DEALLOCATE (Transact-SQL)
• FETCH (Transact-SQL)
• SELECT (Transact-SQL)
• sp_configure (Transact-SQL)