SqlServer 2000 游標用法小例 翻彈押尾桑Cannon(卡儂) » --------------------------------------------------------------------------------

DECLARE CURSOR (T-SQL)創建游標 September 14th, 2006 by OoperMan; (1 votes, average: 5 out of 5) Loading ...SQL Server 2005 聯機叢書 DECLARE CURSOR (Transact-SQL)更新日期： 2005 年 12 月 5 日

定義 Transact-SQL 服務器游標的屬性，例如游標的滾動行為和用于生成游標所操作的結果集的查詢。DECLARE CURSOR 接受基于 SQL-92 標準的語法和使用一組 Transact-SQL 擴展插件的語法。

Transact-SQL 語法約定

語法

SQL 92 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 ] ] ] [;]

參數

cursor_name所定義的 Transact-SQL 服務器游標的名稱。cursor_name 必須符合標識符規則。有關標識符規則的詳細信息，請參閱使用標識符作為對象名稱。

INSENSITIVE定義一個游標，以創建將由該游標使用的數據的臨時復本。對游標的所有請求都從 tempdb 中的這一臨時表中得到應答；因此，在對該游標進行提取操作時返回的數據中不反映對基表所做的修改，并且該游標不允許修改。使用 SQL-92 語法時，如果省略 INSENSITIVE，則已提交的（任何用戶）對基礎表的刪除和更新都反映在后面的提取中。

SCROLL指定所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在 SQL-92 DECLARE CURSOR 中指定 SCROLL，則 NEXT 是唯一支持的提取選項。如果也指定了 FAST_FORWARD，則不能指定 SCROLL。

select_statement定義游標結果集的標準 SELECT 語句。在游標聲明的 select_statement 內不允許使用關鍵字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

Microsoft 如果 select_statement 中的子句與所請求的游標類型的功能有沖突，則 SQL Server 會將游標隱式轉換為其他類型。有關詳細信息，請參閱使用隱式游標轉換。

READ ONLY禁止通過該游標進行更新。在 UPDATE 或 DELETE 語句的 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指定該游標的作用域對來說連接是全局的。在由連接執行的任何存儲過程或批處理中，都可以引用該游標名稱。該游標僅在斷開連接時隱式釋放。

注意：如果 GLOBAL 和 LOCAL 參數都未指定，則默認值由 default to local cursor 數據庫選項的設置控制。在 SQL Server 7.0 版中，該選項默認為 FALSE，以便與 SQL Server 的早期版本相匹配，在早期版本中，所有游標都是全局的。該選項的默認值在以后的 SQL Server 版本中可能會更改。有關詳細信息，請參閱“設置數據庫選項”。

FORWARD_ONLY指定游標只能從第一行滾動到最后一行。FETCH NEXT 是唯一支持的提取選項。如果在指定 FORWARD_ONLY 時不指定 STATIC、KEYSET 和 DYNAMIC 關鍵字，則游標作為 DYNAMIC 游標進行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，則除非指定 STATIC、KEYSET 或 DYNAMIC 關鍵字，否則默認為 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游標默認為 SCROLL。與 ODBC 和 ADO 這類數據庫 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游標支持 FORWARD_ONLY。

STATIC定義一個游標，以創建將由該游標使用的數據的臨時復本。對游標的所有請求都從 tempdb 中的這一臨時表中得到應答；因此，在對該游標進行提取操作時返回的數據中不反映對基表所做的修改，并且該游標不允許修改。

KEYSET指定當游標打開時，游標中行的成員身份和順序已經固定。對行進行唯一標識的鍵集內置在 tempdb 內一個稱為 keyset 的表中。

注意：如果查詢引用了至少一個無唯一索引的表，則鍵集游標將轉換為靜態游標。

對基表中的非鍵值所做的更改（由游標所有者更改或由其他用戶提交）可以在用戶滾動游標時看到。其他用戶執行的插入是不可見的（不能通過 Transact-SQL 服務器游標執行插入）。如果刪除行，則在嘗試提取行時返回值為 -2 的 @@FETCH_STATUS。從游標以外更新鍵值類似于刪除舊行然后再插入新行。具有新值的行是不可見的，并在嘗試提取具有舊值的行時，將返回值為 -2 的 @@FETCH_STATUS。如果通過指定 WHERE CURRENT OF 子句利用游標來完成更新，則新值是可見的。

DYNAMIC定義一個游標，以反映在滾動游標時對結果集內的各行所做的所有數據更改。行的數據值、順序和成員身份在每次提取時都會更改。動態游標不支持 ABSOLUTE 提取選項。

FAST_FORWARD指定啟用了性能優化的 FORWARD_ONLY、READ_ONLY 游標。如果指定了 SCROLL 或 FOR_UPDATE，則不能也指定 FAST_FORWARD。

注意：在 SQL Server 2000 中，FAST_FORWARD 和 FORWARD_ONLY 游標選項是互相排斥的。如果指定了二者，則會引發錯誤。在 SQL Server 2005 中，這兩個關鍵字可以用在同一個 DECLARE CURSOR 語句中。

READ_ONLY禁止通過該游標進行更新。在 UPDATE 或 DELETE 語句的 WHERE CURRENT OF 子句中不能引用游標。該選項優于要更新的游標的默認功能。

SCROLL_LOCKS指定通過游標進行的定位更新或刪除保證會成功。將行讀取到游標中以確保它們對隨后的修改可用時，Microsoft SQL Server 將鎖定這些行。如果還指定了 FAST_FORWARD 或 STATIC，則不能指定 SCROLL_LOCKS。

OPTIMISTIC指定如果行自從被讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不會成功。當將行讀入游標時 SQL Server 不會鎖定行。相反，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 語句的第一種格式使用 SQL-92 語法聲明游標行為。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 中可以使用變量。游標變量值在聲明游標后不發生更改。在 SQL Server 版本 6.5 以及早期版本中，每次重新打開游標時都會重新刷新變量值。

權限

默認情況下，將 DECLARE CURSOR 權限授予對游標中所使用的視圖、表和列具有 SELECT 權限的任何用戶。

示例

A. 使用簡單游標和語法

在打開該游標時所生成的結果集包括表中的所有行和所有列。可以更新該游標，對該游標所做的所有更新和刪除均在提取中表現出來。因為未指定 SCROLL 選項，所以 FETCH NEXT 是唯一可用的提取選項。

DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursorB. 使用嵌套游標生成報表輸出

以下示例顯示如何嵌套游標以生成復雜的報表。為每個供應商聲明內部游標。

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