DECLARE CURSOR (Transact-SQL)
適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體
定義 Transact-SQL 伺服器資料指標的屬性,例如立資料指標運作時的捲動行為以及用以建立結果集的查詢。 DECLARE CURSOR
可接受採用 ISO 標準以及使用 Transact-SQL 延伸模組的語法。
Syntax
ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]
注意
若要檢視 SQL Server 2014 (12.x) 和舊版的 Transact-SQL 語法,請參閱 舊版檔。
引數
cursor_name
是所定義之 Transact-SQL 資料指標的名稱。 cursor_name 必須符合識別碼的規則。
INSENSITIVE
定義一個資料指標,它會建立資料暫存複本供資料指標本身使用。 對於資料指標的所有要求都會從 tempdb 中的這個暫存資料表來回答;因此,對基底資料表所做的修改並不會反映在對這個資料指標所做之擷取傳回的資料中,而這個資料指標不允許修改。 使用 ISO 語法時若省略了 INSENSITIVE
,則任何使用者對基礎資料表所做的已認可刪除及更新動作,都會反映在後續的擷取中。
SCROLL
指定提供所有擷取選項 (FIRST
、LAST
、PRIOR
、NEXT
、RELATIVE
、ABSOLUTE
)。 如果 ISO DECLARE CURSOR
中沒有指定 SCROLL
,則 NEXT
是唯一支援的擷取選項。 如果也指定了 FAST_FORWARD
,就無法指定 SCROLL
。 如果未指定 SCROLL
,則只能使用擷取選項 NEXT
,且資料指標會變成 FORWARD_ONLY
。
select_statement
這是定義資料指標結果集的標準 SELECT
陳述式。 資料指標宣告的 select_statement 中不允許關鍵字 FOR BROWSE
和 INTO
。
如果 select_statement 中的子句與要求的資料指標類型功能相衝突,SQL Server 會隱含地將資料指標轉換為其他類型。
READ ONLY
防止利用這個資料指標進行更新。 無法在 WHERE CURRENT OF
子句中、在 UPDATE
或 DELETE
陳述式中參考該資料指標。 這個選項會覆寫要更新之資料指標的預設功能。
UPDATE [OF column_name [,...n]]
在資料指標內定義可更新的資料行。 如果指定了 OF <column_name> [, <... n>],則只允許修改列出的資料行。 如果指定 UPDATE
時沒有同時指定資料行清單,則可更新所有的資料行。
cursor_name
是所定義之 Transact-SQL 資料指標的名稱。 cursor_name 必須符合識別碼的規則。
LOCAL
指定已建立資料指標的批次、預存程序或觸發程序,其資料指標的範圍為本機範圍。 資料指標名稱只在這個範圍內有效。 批次、預存程序或觸發程序內的區域資料指標變數或是預存程序 OUTPUT
參數可以參考資料指標。 OUTPUT
參數是用來將本機資料指標傳回呼叫的批次、預存程序或觸發程序,它們可將參數指派到資料指標變數,以在預存程序中止後參考資料指標。 當批次、預存程序或觸發程序結束時,除非在 OUTPUT
參數中傳回資料指標,否則,會隱含地將資料指標取消配置。 如果在 OUTPUT
參數中傳回資料指標,當最後一個參考資料指標的變數取消配置或離開範圍時,系統便會將資料指標取消配置。
GLOBAL
指定連接的資料指標範圍為全域。 連接所執行的任何預存程序或批次內都可以參考資料指標名稱。 只有在中斷連接時,才會隱含地取消配置資料指標。
注意
若未指定 GLOBAL
或 LOCAL
,預設值是由 default to local cursor 資料庫選項的設定所控制。
FORWARD_ONLY
指定資料指標只能向前移動,以及從第一個資料列捲動到最後一個資料列。 FETCH NEXT
是唯一支援的擷取選項。 擷取資料列時,可看見目前使用者執行 (或其他使用者認可) 的所有 INSERT、UPDATE 和 DELETE 陳述式如何影響結果集中的資料列。 不過,由於無法反向捲動資料指標,因此擷取資料列之後對資料庫中資料列所進行的變更,都無法經由資料指標看見。 順向資料指標預設是動態的,這表示當處理目前的資料列時會偵測到所有變更。 這會加速資料指標的開啟,並讓結果集顯示對基礎資料表所做的更新。 雖然順向資料指標不支援反向捲動,但應用程式可以藉由關閉並重新開啟資料指標,來返回結果集的開頭。
如果指定 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 中名為 keyset 的資料表內。 這個資料指標會提供在靜態與動態資料指標之間偵測變更的功能。 如同靜態資料指標,它不一定會偵測結果集的成員資格和順序變更。 如同動態資料指標,它會偵測結果集中的資料列值變更。
索引鍵集驅動資料指標是由稱為索引鍵集的一組唯一識別碼 (索引鍵) 所控制。 索引鍵是從結果集中唯一識別資料列的一組資料行建立的。 索引鍵集是一組取自由查詢陳述式傳回之所有資料列的索引鍵值。 使用索引鍵集驅動資料指標,會在資料指標中為每個資料列建置和保留一個索引鍵,並儲存在用戶端工作站或伺服器上。 當您存取每個資料列時,會使用此預存金鑰從資料來源擷取目前的資料值。 在索引鍵集驅動資料指標中,當完整擴展索引鍵集時,結果集的成員資格會遭到凍結。 之後,在重新開啟之前,都不會在結果集中新增或更新受影響的成員資格。
當使用者捲動結果集時,可看見資料值的變更 (索引鍵集擁有者或其他處理序所做的變更):
- 如果刪除某個資料列,嘗試擷取該資料列的動作會傳回值為 -2 的
@@FETCH_STATUS
,因為已刪除的資料列會在結果集中顯示為間距。 索引鍵集中存在資料列的索引鍵,但結果集中不再有此資料列。 - 只有在關閉並重新開啟資料指標之後,才能看見其他處理序在資料指標外部執行的插入。 在結果集結尾可看見從資料指標內部執行的插入。
- 從資料指標之外更新索引鍵值,類似於先刪除舊資料列,再插入新資料列。 含有新值的資料列為不可見,且嘗試擷取含舊值的資料列傳回值為 -2 的
@@FETCH_STATUS
。 如果更新是藉由指定WHERE CURRENT OF
子句透過資料指標完成,新值的值便為可見。
注意
如果查詢所參考的資料表中至少有一個沒有唯一的索引鍵,則索引鍵集資料指標會轉換為靜態資料指標。
DYNAMIC
定義資料指標,在您捲動資料指標並擷取新的記錄時,反映結果集中資料列的所有資料變更,不論這些變更發生於資料指標內部,還是其他使用者在資料指標外部所做的變更。 因此,您可以透過資料指標看到所有使用者執行的全部 UPDATE、INSERT 和 DELETE 陳述式作業。 每次提取時,資料列的資料值、順序和成員資格都有可能改變。 動態資料指標不支援 ABSOLUTE
擷取選項。 至於資料指標外的更新必須經過認可後才看得見 (除非資料指標交易隔離等級設定為 UNCOMMITTED
)。
例如,假設動態資料指標擷取兩個資料列,而其他應用程式接著更新其中一個資料列並刪除另一個資料列。 如果動態資料指標之後擷取這些資料列,它會找不到已刪除的資料列,但會顯示已更新資料列的新值。
FAST_FORWARD
指定 FORWARD_ONLY
、READ_ONLY
資料指標,且啟用效能最佳化。 如果也指定了 SCROLL
或 FOR_UPDATE
,就無法指定 FAST_FORWARD
。 這種資料指標類型不允許從資料指標內部修改資料。
注意
FAST_FORWARD
和 FORWARD_ONLY
都可用於相同的 DECLARE CURSOR
陳述式。
READ_ONLY
防止利用這個資料指標進行更新。 無法在 WHERE CURRENT OF
子句中、在 UPDATE
或 DELETE
陳述式中參考該資料指標。 這個選項會覆寫要更新之資料指標的預設功能。
SCROLL_LOCKS
指定藉由資料指標進行的定位更新或刪除一定會成功。 當資料列讀入資料指標時,SQL Server 會鎖定這些資料列,以確保之後可對其加以修改。 如果也指定了 FAST_FORWARD
或 STATIC
,就無法指定 SCROLL_LOCKS
。
OPTIMISTIC
指定如果將資料列讀入資料指標之後,又更新了這些資料列,則透過資料指標來進行的定位更新或刪除不會成功。 當資料列讀入資料指標時,SQL Server 不會鎖定這些資料列。 它會改用 timestamp 資料行值的比較,或者,如果資料表沒有 timestamp 資料行則使用總和檢查碼值,來判斷在將資料列讀入資料指標之後,該資料列是否已被修改。 如果修改了資料列,試圖執行的定位更新或刪除便會失敗。 如果也指定了 FAST_FORWARD
,就無法指定 OPTIMISTIC
。
TYPE_WARNING
指定當資料指標從要求的類型隱含地轉換成另一個類型時,便傳送一則警告訊息給用戶端。
select_statement
這是定義資料指標結果集的標準 SELECT 陳述式。 資料指標宣告的 select_statement 中不允許關鍵字 COMPUTE
、COMPUTE BY
、FOR BROWSE
和 INTO
。
注意
您可以在資料指標宣告中使用查詢提示,但如果您也使用 FOR UPDATE OF
子句,請在 FOR UPDATE OF
之後指定 OPTION (<query_hint>)
。
如果 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
關鍵字,就不能在 CURSOR
和 FOR <select_statement>
關鍵字之間使用任何關鍵字。 如果您在 CURSOR
和 FOR <select_statement>
關鍵字之間指定任何關鍵字,就不能在 CURSOR
關鍵字之前指定 SCROLL
或 INSENSITIVE
。
如果使用 Transact-SQL 語法的 DECLARE CURSOR
並未指定 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 陳述式可擷取資料列。
在宣告資料指標後,下列系統預存程序即可用來判斷資料指標的特性。
系統預存程序 | 描述 |
---|---|
sp_cursor_list | 傳回目前連接可見的資料指標清單及其屬性。 |
sp_describe_cursor | 描述某個資料指標的屬性,例如,它是一個順向資料指標或捲動資料指標。 |
sp_describe_cursor_columns | 描述資料指標結果集中的資料行屬性。 |
sp_describe_cursor_tables | 描述資料指標所存取的基底資料表。 |
您可以在宣告資料指標的 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)
意見反應
https://aka.ms/ContentUserFeedback。
即將推出:在 2024 年,我們將隨著內容的意見反應機制逐步淘汰 GitHub 問題,並以新的意見反應系統來取代。 如需詳細資訊,請參閱提交並檢視相關的意見反應