游標(biāo)概念

由select語句返回的結(jié)果集包括滿足該語句的where子句中條件的所有行。但是有時(shí)候應(yīng)用程序并不總能將整個(gè)結(jié)果集作為一個(gè)單元來處理，這些應(yīng)用程序需要一種機(jī)制以便每次處理結(jié)果集中的一行活一部分行。游標(biāo)就是提供這種機(jī)制的結(jié)果集擴(kuò)展。
??游標(biāo)主要用在存儲(chǔ)過程、觸發(fā)器和T-SQL腳本中。游標(biāo)包括一下兩個(gè)部分。

• 游標(biāo)結(jié)果集(Cursor Result Set)由定義該游標(biāo)的select語句返回的行的集合
• 游標(biāo)位置(Cursor Position)指向這個(gè)集合中某一行的指針

游標(biāo)具有以下優(yōu)點(diǎn)：

• 允許定位在結(jié)果集中的特定行
• 從結(jié)果集的當(dāng)前位置檢索一行或多行
• 支持對(duì)結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改
• 為由其他用戶對(duì)顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級(jí)別的可見性支持

使用游標(biāo)

聲明游標(biāo)

游標(biāo)在使用之前，必須先聲明。其語法格式如下：

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 ] ] ]

• LOCAL | GLOBAL (游標(biāo)生存周期)
  ??LOCAL指定該游標(biāo)的范圍對(duì)在其中創(chuàng)建它的批處理、存儲(chǔ)過程或觸發(fā)器是局部的。
  ??GLOBAL指定該游標(biāo)范圍對(duì)連接是全局的。 在由此連接執(zhí)行的任何存儲(chǔ)過程或批處理中，都可以引用該游標(biāo)名稱。 該游標(biāo)僅在斷開連接時(shí)隱式釋放。
  
• FORWARD_ONLY | SCROLL (讀取方向)
  ??FORWARD_ONLY指定游標(biāo)只能從第一行滾動(dòng)到最后一行。 FETCH NEXT 是唯一支持的提取選項(xiàng)。
  ??SCROLL支持游標(biāo)在定義的數(shù)據(jù)集中向任何方向，或任何位置移動(dòng)（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。
  
• STATIC | KEYSET | DYNAMIC | FAST_FORWARD(表內(nèi)數(shù)據(jù)與游標(biāo)讀取出的數(shù)據(jù)的關(guān)系)
• STATIC：靜態(tài)游標(biāo)。當(dāng)游標(biāo)被建立時(shí)，將會(huì)創(chuàng)建SELECT語句所包含數(shù)據(jù)集的副本存入TempDB數(shù)據(jù)庫中。你在操作游標(biāo)的時(shí)候，不論如何操作數(shù)據(jù)庫，游標(biāo)中的數(shù)據(jù)集都不會(huì)變，并且此游標(biāo)不允許修改。
• DYNAMIC：動(dòng)態(tài)游標(biāo)。和STATIC完全相反，滾動(dòng)游標(biāo)時(shí)，動(dòng)態(tài)游標(biāo)反應(yīng)結(jié)果集中的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員在每次提取時(shí)都會(huì)變化。所有用戶做的增刪改語句通過游標(biāo)均可見。
• FAST_FORWARD：**只進(jìn)游標(biāo) **。只進(jìn)游標(biāo)不支持滾動(dòng)，只支持從頭到尾順序提取數(shù)據(jù)，數(shù)據(jù)庫執(zhí)行增刪改，在提取時(shí)是可見的，但由于該游標(biāo)只能進(jìn)不能向后滾動(dòng)，所以在行提取后對(duì)行做增刪改是不可見的。指定啟用了性能優(yōu)化的 FORWARD_ONLY、READ_ONLY 游標(biāo)。
• KEYSET：鍵集游標(biāo)。將游標(biāo)所在結(jié)果集的唯一能確定每一行的主鍵存入TempDB，結(jié)果集這些行數(shù)據(jù)被一組唯一標(biāo)識(shí)符標(biāo)識(shí)。對(duì)基表中的非鍵值所做的更改（由游標(biāo)所有者更改或由其他用戶提交）可以在用戶滾動(dòng)游標(biāo)時(shí)看到。 其他用戶執(zhí)行的插入是不可見的，因?yàn)樵撔袥]有被標(biāo)識(shí)，除非重新打開游標(biāo)才可見。如果查詢引用了至少一個(gè)無唯一索引的表，則鍵集游標(biāo)將轉(zhuǎn)換為靜態(tài)游標(biāo)。
• READ_ONLY | SCROLL_LOCKS | OPTIMISTIC(是否允許在游標(biāo)中修改數(shù)據(jù))
• READ_ONLY：禁止通過該游標(biāo)進(jìn)行更新。 在 UPDATE 或 DELETE 語句的 WHERE CURRENT OF 子句中不能引用游標(biāo)。
• SCROLL_LOCKS ：將行讀入游標(biāo)時(shí)，鎖定這些行(行鎖)，確保刪除或更新一定會(huì)成功。
• OPTIMISTIC：當(dāng)將行讀入游標(biāo)時(shí)，SQL Server 不鎖定行。 它改為使用的比較時(shí)間戳列的值或如果表未包含任何校驗(yàn)和值時(shí)間戳列，來確定是否它已讀入游標(biāo)后已修改行。 如果讀的時(shí)候該行已被修改過，嘗試進(jìn)行的定位更新或定位刪除將失敗。
• FOR UPDATE [ OF column_name [ ,...n ] ]
  ??定義游標(biāo)中可更新的列。如果指定了 [ OF column_name [ ,...n ] ]，則只允許修改所列出的列，否則可以更新所有列。
• TYPE_WARNING：指定如果游標(biāo)從所請(qǐng)求的類型隱式轉(zhuǎn)換為另一種類型，則向客戶端發(fā)送警告消息。

• STATIC 和 FAST_FORWARD 游標(biāo)默認(rèn)為 READ_ONLY。

• DYNAMIC 和 KEYSET 游標(biāo)默認(rèn)為 OPTIMISTIC。
• 如果在指定 FORWARD_ONLY 時(shí)不指定 STATIC、KEYSET 和 DYNAMIC 關(guān)鍵字，則游標(biāo)作為 DYNAMIC 游標(biāo)進(jìn)行操作。
• 如果 FORWARD_ONLY 和 SCROLL 均未指定，那么除非指定了 STATIC、KEYSET 或 DYNAMIC 關(guān)鍵字，否則默認(rèn)值為 FORWARD_ONLY。 STATIC、KEYSET 和 DYNAMIC 游標(biāo)默認(rèn)為 SCROLL。 與 ODBC 和 ADO 這類數(shù)據(jù)庫 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游標(biāo)支持 FORWARD_ONLY。
• 動(dòng)態(tài)游標(biāo)不支持ABSOLUTE提取選項(xiàng)。
• 如果指定了 SCROLL 或 FOR_UPDATE，則不能也指定FAST_FORWARD。
• FAST_FORWARD 和 FORWARD_ONLY 可以同時(shí)用在同一個(gè) DECLARE CURSOR 語句中。
• 如果指定了 FAST_FORWARD 或 STATIC，則不能指定 SCROLL_LOCKS。

游標(biāo)分為游標(biāo)類型和游標(biāo)變量。游標(biāo)類型就是CURSRO關(guān)鍵字，正如下面所示。游標(biāo)變量支持兩種方式賦值，定義時(shí)賦值和先定義后賦值，定義游標(biāo)變量像定義其他局部變量一樣，在游標(biāo)前加”@”。注意，如果定義全局的游標(biāo)，只支持定義時(shí)直接賦值，并且不能在游標(biāo)名稱前面加“@”，兩種定義方式如下：

-- 定義后直接賦值
DECLARE test_cursor CURSRO FOR select * from dbo.tb1

-- 先定義后賦值
DECLARE @test_cursor2 CURSRO 
SET @test_cursor2=CURSRO FOR select * from dbo.tb2

游標(biāo)函數(shù)

（1）@@CURSOR_ROWS
??得到當(dāng)前游標(biāo)中存在的數(shù)據(jù)行數(shù)。注意：此變量為一個(gè)連接上的全局變量，因此只對(duì)應(yīng)最后一次打開的游標(biāo)。
??@@CURSOR_ROWS返回值為整數(shù)，意義如下：

（3）CURSOR_STATUS
??一個(gè)標(biāo)量函數(shù)，它允許存儲(chǔ)過程的調(diào)用方確定該存儲(chǔ)過程是否已為給定的參數(shù)返回了游標(biāo)和結(jié)果集。此函數(shù)的語法為：

CURSOR_STATUS   
     (  
          { 'local' , 'cursor_name' }   
          | { 'global' , 'cursor_name' }   
          | { 'variable' , 'cursor_variable' }   
     )  

• 'local'指定一個(gè)常量，該常量指示游標(biāo)的源是一個(gè)本地游標(biāo)名。
• 'global'指定一個(gè)常量，該常量指示游標(biāo)的源是一個(gè)全局游標(biāo)名。
• 'variable'指定一個(gè)常量，該常量指示游標(biāo)的源是一個(gè)本地變量。
• cursor_variable：游標(biāo)變量的名稱。
• cursor_name：游標(biāo)的名稱。

CURSOR_STATUS返回類型為smallint

打開游標(biāo)

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

• GLOBAL選項(xiàng)指定其后的游標(biāo)為全局游標(biāo)。
• cursor_name：已聲明的游標(biāo)的名稱。
• cursor_variable_name：游標(biāo)變量的名稱，該變量引用一個(gè)游標(biāo)。
• 當(dāng)執(zhí)行打開游標(biāo)的語句時(shí)，服務(wù)器將執(zhí)行聲明游標(biāo)時(shí)使用的select語句
• 如果聲明游標(biāo)中使用了STATIC關(guān)鍵字，則服務(wù)器會(huì)在TempDB中建立一個(gè)臨時(shí)表，存放游標(biāo)將要進(jìn)行操作的結(jié)果集的副本。
• 如果使用 KEYSET 選項(xiàng)聲明了游標(biāo)，則服務(wù)器會(huì)在TempDB中建立一個(gè)臨時(shí)表存放鍵值。
• 如果結(jié)果集中任意行的大小超過 SQL Server 表的最大行大小，OPEN 將失敗。
• 利用OPEN語句打開游標(biāo)后，游標(biāo)位置位于查詢結(jié)果集的第一行

提取數(shù)據(jù)

打開游標(biāo)后，就可以利用fetch語句從查詢結(jié)果集。使用fetch語句一次可以提取一條記錄，具體的語法格式如下：

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ]   

• NEXT：讀取當(dāng)前行的下一行，并把這個(gè)“下一行”置為當(dāng)前行。next為默認(rèn)的游標(biāo)提取選項(xiàng)。如果 FETCH NEXT 為對(duì)游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。
• PRIOR：讀取當(dāng)前行的上一行，并把這個(gè)“上一行”置為當(dāng)前行。
• FIRST：返回游標(biāo)中的第一行并將其作為當(dāng)前行。
• LAST：返回游標(biāo)中的最后一行并將其作為當(dāng)前行。
• ABSOLUTE { n| @nvar}
• 如果n或@nvar為正數(shù)，提取從游標(biāo)頭開始的第n行，并將返回的行變成新的當(dāng)前行；
• 如果n或@nvar為負(fù)數(shù)，提取從游標(biāo)尾之前的第n行，并將返回的行變成新的當(dāng)前行；
• 如果n或@nvar為0，則沒有行返回；
• n必須為整型常量，@nvar必須為smallint、tinyint或nit；
• RELATIVE { n| @nvar}
• 如果n或@nvar為正數(shù)，提取當(dāng)前行之后的第n行，并將返回的行變成新的當(dāng)前行；
• 如果n或@nvar為負(fù)數(shù)，提取當(dāng)前行之前的第n行，并將返回的行變成新的當(dāng)前行；
• 如果n或@nvar為0，提取當(dāng)前行；
• 如果對(duì)游標(biāo)的第一次提取操作時(shí)將fetch relative的n或@nvar指定為負(fù)數(shù)或0，則沒有行返回；
• n必須為整型常量，@nvar必須為smallint、tinyint或nit；
• GLOBAL：指定其后的游標(biāo)是全局游標(biāo)。
• @ INTOvariable_name[，...n]：允許將提取操作的列數(shù)據(jù)放到局部變量中。 列表中的各個(gè)變量從左到右與游標(biāo)結(jié)果集中的相應(yīng)列相關(guān)聯(lián)。 各變量的數(shù)據(jù)類型必須與相應(yīng)的結(jié)果集列的數(shù)據(jù)類型匹配，或是結(jié)果集列數(shù)據(jù)類型所支持的隱式轉(zhuǎn)換。 變量的數(shù)目必須與游標(biāo)選擇列表中的列數(shù)一致。

• 如果指定了 FORWARD_ONLY 或 FAST_FORWARD，則 NEXT 是唯一受支持的 FETCH 選項(xiàng)。

• 如果未指定 DYNAMIC、FORWARD_ONLY 或 FAST_FORWARD 選項(xiàng)，并且指定了 KEYSET、STATIC 或 SCROLL 中的某一個(gè)，則支持所有 FETCH 選項(xiàng)。
• DYNAMIC SCROLL 游標(biāo)支持除 ABSOLUTE 以外的所有 FETCH 選項(xiàng)。
• 如果想操作當(dāng)前行的話，就用current of <游標(biāo)名>|<游標(biāo)變量名>

關(guān)閉游標(biāo)

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

釋放游標(biāo)

關(guān)閉游標(biāo)并不改變游標(biāo)的定義，可以再次打開該游標(biāo)。但是，釋放游標(biāo)就釋放了與指定游標(biāo)有關(guān)的一切資源，再次使用該游標(biāo)必須重新聲明。
??對(duì)游標(biāo)進(jìn)行操作的語句使用游標(biāo)名稱或游標(biāo)變量引用游標(biāo)。 DEALLOCATE 刪除游標(biāo)與游標(biāo)名稱或游標(biāo)變量之間的關(guān)聯(lián)。 如果一個(gè)名稱或變量是最后引用游標(biāo)的名稱或變量，則將釋放游標(biāo)，游標(biāo)使用的任何資源也隨之釋放。 用于保護(hù)提取隔離的滾動(dòng)鎖在 DEALLOCATE 上釋放。 用于保護(hù)更新（包括通過游標(biāo)進(jìn)行的定位更新）的事務(wù)鎖一直到事務(wù)結(jié)束才釋放。

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }  

示例

A、 使用 FETCH 將值存入變量

DECLARE @LastName varchar(50), @FirstName varchar(50);  

DECLARE contact_cursor CURSOR FOR  
SELECT LastName, FirstName FROM Person.Person  
WHERE LastName LIKE 'B%'  
ORDER BY LastName, FirstName;  
  
OPEN contact_cursor;  
  
FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName    
   FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName;  
END  
  
CLOSE contact_cursor;  
DEALLOCATE contact_cursor;  
GO  

B、 聲明 SCROLL 游標(biāo)并使用其他 FETCH 選項(xiàng)

-- Declare the cursor.  
DECLARE contact_cursor SCROLL CURSOR FOR  
SELECT LastName, FirstName FROM Person.Person  
ORDER BY LastName, FirstName;  
  
OPEN contact_cursor;  
  
-- Fetch the last row in the cursor.  
FETCH LAST FROM contact_cursor;  
  
-- Fetch the row immediately prior to the current row in the cursor.  
FETCH PRIOR FROM contact_cursor;  
  
-- Fetch the second row in the cursor.  
FETCH ABSOLUTE 2 FROM contact_cursor;  
  
-- Fetch the row that is three rows after the current row.  
FETCH RELATIVE 3 FROM contact_cursor;  
  
-- Fetch the row that is two rows prior to the current row.  
FETCH RELATIVE -2 FROM contact_cursor;  
  
CLOSE contact_cursor;  
DEALLOCATE contact_cursor;  
GO  

C、CURSOR_STATUS
下面的示例使用 CURSOR_STATUS 函數(shù)顯示游標(biāo)在打開和關(guān)閉之前和之后的狀態(tài)。

CREATE TABLE #TMP (ii int)  
GO  
  
INSERT INTO #TMP(ii) VALUES(1)  
INSERT INTO #TMP(ii) VALUES(2)  
INSERT INTO #TMP(ii) VALUES(3)  
GO  
  
--Create a cursor.  
DECLARE cur CURSOR FOR SELECT * FROM #TMP  
  
--Display the status of the cursor before and after opening,closing the cursor.  
SELECT CURSOR_STATUS('global','cur') AS 'After declare'  
OPEN cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Open'  
CLOSE cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Close'  

DEALLOCATE cur  
DROP TABLE #TMP  

下面是結(jié)果集：

After declare
---------------
-1
After Open
----------
1
After Close
-----------
-1