1、第13章 游標的使用1311游標的使用（ 視頻講解：13分鐘）第第 章章導讀游標是取用一組數據并能夠一次與一個單獨的數據進行交互的方法，然而，不能通過在整個行集中修改或者選取數據來獲得所需要的結果。本章將對游標的使用進行詳細講解。通過閱讀本章，您可以：: 掌握游標的概念: 了解游標的類型: 掌握游標的基本操作: 了解游標系統存儲過程: 掌握使用系統過程查看游標的方法13.1 游標的概述視頻講解：光盤TMlx13游標的概述.mp4游標是取用一組數據并能夠一次與一個單獨的數據進行交互的方法。關系數據庫中的操作會對整個行集起作用。由SELECT語句返回的行集包括滿足該語句的WHERE子句中條件的所有

2、行。這種由語句返回的完整行集稱為結果集。應用程序，特別是交互式聯機應用程序，并不總能將整個結果集作為一個單元來有效地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標就是提供這種機制并對結果集的一種擴展。游標通過以下方式來擴展結果處理： þ 允許定位在結果集的特定行。þ 從結果集的當前位置檢索一行或一部分行。þ 支持對結果集中當前位置的行進行數據修改。þ 為由其他用戶對顯示在結果集中的數據庫數據所做的更改提供不同級別的可見性支持。þ 提供腳本、存儲過程和觸發器中用于訪問結果集中的數據的 Transact-SQL 語句。游標可以定在該

3、單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用游標，但是需要逐條處理數據時，游標顯得十分重要。13.1.1 游標的實現游標提供了一種從表中檢索數據并進行操作的靈活手段，游標主要用在服務器上，處理由客戶端發送給服務器端的SQL語句，或是批處理、存儲過程、觸發器中的數據處理請求。游標的優點在于它可以定位到結果集中的某一行，并可以對該行數據執行特定操作，為用戶在處理數據的過程中提供了很大方便。一個完整的游標由5部分組成，并且這5個部分應符合下面的順序。（1）聲明游標。（2）打開游標。（3）從一個游標中查找信息。（4）關閉游標。（5）釋放游標。13.1.2 游標的

4、類型SQL Server提供了4種類型的游標：靜態游標、動態游標、只進游標和鍵集驅動的游標。這些游標的檢測結果集變化的能力和內存占用的情況都有所不同，數據源沒有辦法通知游標當前提取行的更改。游標檢測這些變化的能力也受事務隔離級別的影響。1靜態游標靜態游標的完整結果集在游標打開時建立在tempdb中。靜態游標總是按照游標打開時的原樣顯示結果集。靜態游標在滾動期間很少或根本檢測不到變化，雖然它在tempdb中存儲了整個游標，但消耗的資源很少。盡管動態游標使用tempdb的程度最低，在滾動期間它能夠檢測到所有變化，但消耗的資源也更多。鍵集驅動游標介于二者之間，它能檢測到大部分的變化，但比動態游標消耗

5、更少的資源。2動態游標動態游標與靜態游標相對。當滾動游標時，動態游標反映結果集中所做的所有更改。結果集中的行數據值、順序和成員在每次提取時都會改變。所有用戶做的全部UPDATE、INSERT和DELETE語句均通過游標可見。3只進游標只進游標不支持滾動，它只支持游標從頭到尾順序提取。只有從數據庫中提取出來后才能進行檢索。對所有由當前用戶發出或由其他用戶提交、并影響結果集中的行的INSERT、UPDATE和DELETE語句，其效果在這些行從游標中提取時是可見的。4鍵集驅動游標打開游標時，鍵集驅動游標中的成員和行順序是固定的。鍵集驅動游標由一套被稱為鍵集的唯一標識符（鍵）控制。鍵由以唯一方式在結果

6、集中標識行的列構成。鍵集是游標打開時來自所有適合SELECT語句的行中的一系列鍵值。鍵集驅動游標的鍵集在游標打開時建立在tempdb中。對非鍵集列中的數據值所做的更改（由游標所有者更改或其他用戶提交）在用戶滾動游標時是可見的。在游標外對數據庫所做的插入在游標內是不可見的，除非關閉并重新打開游標。13.2 游標的基本操作視頻講解：光盤TMlx13游標的基本操作.mp4游標的基本操作包括聲明游標、打開游標、讀取游標中的數據、關閉游標和釋放游標。本節就詳細介紹如何操作游標。13.2.1 聲明游標聲明游標可以使用DECLARE CURSOR語句。此語句有兩種語法聲明格式，分別為ISO標準語法和Tran

7、sact-SQL擴展的語法，下面將分別介紹聲明游標的兩種語法格式。1ISO標準語法語法如下：DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 參數說明如下。þ DECLARE cursor_name：指定一個游標名稱，其游標名稱必須符合標識符規則。þ INSENSITIVE：定義一個游標，以創建將由該游標使用的數據的臨時復本。對游標的所有請求都從tempdb中的臨時表中得到應答；因此，在對該游標進行提取操作時

8、返回的數據中不反映對基表所做的修改，并且該游標不允許修改。使用SQL-92語法時，如果省略INSENSITIVE，（任何用戶）對基表提交的刪除和更新都反映在后面的提取中。þ SCROLL：指定所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。Ø FIRST：取第一行數據。Ø LAST：取最后一行數據。Ø PRIOR：取前一行數據。Ø NEXT：取后一行數據。Ø RELATIVE：按相對位置取數據。Ø ABSOLUTE：按絕對位置取數據。如果未指定SCROLL，則NEXT是

9、唯一支持的提取選項。þ select_statement：定義游標結果集的標準SELECT語句。在游標聲明的select_statement內不允許使用關鍵字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。þ READ ONLY：表明不允許游標內的數據被更新，盡管在默認狀態下游標是允許更新的。在UPDATE或DELETE語句的WHERE CURRENT OF子句中不允許引用游標。þ UPDATE OF column_name ,.n ：定義游標內可更新的列。如果指定OF column_name ,.n參數，則只允許修改所列出的列。如果在UPD

10、ATE中未指定列的列表，則可以更新所有列。2Transact-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 DECLARE CURSOR語句的參數及說明如表13.1所示。表13.1 DECLARE CURS

11、OR語句的參數及說明參 數描 述DECLARE cursor_name指定一個游標名稱，其游標名稱必須符合標識符規則LOCAL定義游標的作用域僅限在其所在的批處理、存儲過程或觸發器中。當建立游標在存儲過程執行結束后，游標會被自動釋放GLOBAL指定該游標的作用域對連接是全局的。在由連接執行的任何存儲過程或批處理中，都可以引用該游標名稱。該游標僅在脫接時隱性釋放FORWARD_ONLY指定游標只能從第一行滾動到最后一行。FETCH NEXT是唯一受支持的提取選項非指定STATIC、KEYSET或DYNAMIC關鍵字，否則默認為FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游

12、標默認為SCROLL。與ODBC和ADO這類數據庫API不同，STATIC、KEYSET和DYNAMIC Transact-SQL游標支持FORWARD_ONLY。FAST_ FORWARD和FORWARD_ONLY是互斥的；如果指定一個，則不能指定另一個STATIC定義一個游標，以創建將由該游標使用的數據的臨時復本。對游標的所有請求都從tempdb中的該臨時表中得到應答；因此，在對該游標進行提取操作時返回的數據中不反映對基表所做的修改，并且該游標不允許修改KEYSET指定當游標打開時，游標中行的成員資格和順序已經固定。對行進行唯一標識的鍵集內置在tempdb內一個稱為keyset的

13、表中。對基表中的非鍵值所做的更改（由游標所有者更改或由其他用戶提交）在用戶滾動游標時是可視的。其他用戶進行的插入是不可視的（不能通過Transact-SQL服務器游標進行插入）。如果某行已刪除，則對該行的提取操作將返回FETCH_STATUS值-2。從游標外更新鍵值類似于刪除舊行后接著插入新行的操作。含有新值的行不可視，對含有舊值的行的提取操作將返回FETCH_STATUS值-2。如果通過指定WHERE CURRENT OF子句用游標完成更新，則新值可視DYNAMIC定義一個游標，以反映在滾動游標時對結果集內的行所做的所有數據的更改。行的數據值、順序和成員在每次提取時都會更改。動態游標不支持A

14、BSOLUTE提取選項FAST_FORWARD指明一個FORWARD_ONLY、READ_ONLY型游標SCROLL_LOCKS指定確保通過游標完成的定位更新或定位刪除可以成功。將行讀入游標以確保它們可用于以后的修改時，SQL Server會鎖定這些行。如果還指定了FAST_FORWARD，則不能指定SCROLL_LOCKSOPTIMISTIC指明在數據被讀入游標后，如果游標中某行數據已發生變化，那么對游標數據進行更新或刪除可能會導致失敗TYPE_WARNING指定如果游標從所請求的類型隱性轉換為另一種類型，則給客戶端發送警告消息【例13.1】 創建一個名為Cur_Emp的標準游標。（實例位置

15、：光盤TMsl131）SQL語句如下：USE db_2012DECLARE Cur_Emp CURSOR FORSELECT * FROM EmployeeGO運行結果如圖13.1所示。【例13.2】 創建一個名為Cur_Emp_01的只讀游標。（實例位置：光盤TMsl132）SQL語句如下：USE db_2012DECLARE Cur_Emp_01 CURSOR FORSELECT * FROM EmployeeFOR READ ONLY -只讀游標GO運行結果如圖13.2所示。 圖13.1 創建標準游標   圖13.2 創建只讀游標【例13.3】 創建一個名為Cur_Emp_02

16、的更新游標。（實例位置：光盤TMsl133）SQL語句如下：USE db_2012DECLARE Cur_Emp_02 CURSOR FORSELECT Name,Sex,Age FROM EmployeeFOR UPDATE -更新游標GO運行結果如圖13.3所示。圖13.3 創建更新游標13.2.2 打開游標打開一個聲明的游標可以使用OPEN命令。語法如下：OPEN GLOBAL cursor_name | cursor_variable_name 參數說明如下。þ GLOBAL：指定cursor_name為全局游標。þ cursor_name：已聲明的游標名稱，如果全

17、局游標和局部游標都使用cursor_name作為其名稱，那么如果指定了GLOBAL，cursor_name指的是全局游標，否則，cursor_name指的是局部游標。þ cursor_variable_name：游標變量的名稱，該名稱引用一個游標。如果使用INSENSITIV或STATIC選項聲明了游標，那么OPEN將創建一個臨時表以保留結果集。如果結果集中任意行的大小超過SQL Server表的最大行大小，OPEN將失敗。如果使用KEYSET選項聲明了游標，那么OPEN將創建一個臨時表以保留鍵集。臨時表存儲在tempdb中。【例13.4】 首先聲明一個名為Emp_01的游標，然后使

18、用OPEN命令打開該游標。（實例位置：光盤TMsl134）SQL語句如下：USE db_2012DECLARE Emp_01 CURSOR FOR-聲明游標SELECT * FROM EmployeeWHERE ID = '1'OPEN Emp_01-打開游標GO運行結果如圖13.4所示。圖13.4 打開游標13.2.3 讀取游標中的數據當打開一個游標之后，就可以讀取游標中的數據了。可以使用FETCH命令讀取游標中的某一行數據。語法如下：FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nv

19、ar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n FETCH命令的參數及說明如表13.2所示。表13.2 FETCH命令的參數及說明參 數描 述NEXT返回緊跟當前行之后的結果行，并且當前行遞增為結果行。如果FETCH NEXT為對游標的第一次提取操作，則返回結果集中的第一行。NEXT為默認的游標提取選項PRIOR返回緊臨當前行前面的結果行，并且當前行遞減為結果行。如果FETCH PRIOR為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前FIRST返回游標中的第一行并將其作為當前行LAS

20、T返回游標中的最后一行并將其作為當前行ABSOLUTE n | nvar如果n或nvar為正數，返回從游標頭開始的第n行，并將返回的行變成新的當前行。如果n或nvar為負數，返回游標尾之前的第n行，并將返回的行變成新的當前行。如果n或nvar為0，則沒有行返回RELATIVE n | nvar如果n或nvar為正數，返回當前行之后的第n行，并將返回的行變成新的當前行。如果n或nvar為負數，返回當前行之前的第n行，并將返回的行變成新的當前行。如果n或nvar為0，返回當前行。如果對游標的第一次提取操作時將FETCHRELATIVE的n或nvar指定為負數或0，則沒有行返回。n必須為整型常量且n

21、var必須為smallint、tinyint或intGLOBAL指定cursor_name為全局游標cursor_name要從中進行提取的開放游標的名稱。如果同時有以cursor_name作為名稱的全局和局部游標存在，若指定為GLOBAL，則cursor_name對應于全局游標，未指定GLOBAL，則對應于局部游標cursor_variable_name游標變量名，引用要進行提取操作的打開的游標INTO variable_name,.n允許將提取操作的列數據放到局部變量中。列表中的各個變量從左到右與游標結果集中的相應列相關聯。各變量的數據類型必須與相應的結果列的數據類型匹配或是結果列數據類型所

22、支持的隱性轉換。變量的數目必須與游標選擇列表中的列的數目一致FETCH_STATUS返回上次執行FETCH命令的狀態。在每次用FETCH從游標中讀取數據時，都應檢查該變量，以確定上次FETCH操作是否成功，決定如何進行下一步處理。FETCH_STATUS變量有3個不同的返回值，說明如下：（1）返回值為0，FETCH語句成功；（2）返回值為-1，FETCH語句失敗或此行不在結果集中；（3）返回值為-2，被提取的行不存在（1）在前兩個參數中，包含n和nvar其表示游標相對于作為基準的數據行所偏離的位置。（2）當使用SQL-92語法來聲明一個游標時，沒有選擇SCROLL選項，則只能使用FETCH N

23、EXT命令來從游標中讀取數據，即只能從結果集第一行按順序地每次讀取一行。由于不能使用FIRST、LAST、PRIOR，所以無法回滾讀取以前的數據。如果選擇了SCROLL選項，則可以使用所有的FETCH操作。【例13.5】 用FETCH_STATUS 控制一個WHILE循環中的游標活動，SQL語句及運行結果如圖13.5所示。（實例位置：光盤TMsl135）圖13.5 從游標中讀取數據SQL語句如下：USE db_2012 -引入數據庫DECLARE ReadCursor CURSOR FOR-聲明一個游標SELECT * FROM StudentOPEN ReadCursor-打開游標FETCH

24、 NEXT FROM ReadCursor-執行取數操作WHILE FETCH_STATUS=0-檢查FETCH_STATUS，以確定是否還可以繼續取數BEGIN FETCH NEXT FROM ReadCursorEND13.2.4 關閉游標當游標使用完畢之后，使用CLOSE語句可以關閉游標，但不釋放游標占用的系統資源。語法如下：CLOSE GLOBAL cursor_name | cursor_variable_name 參數說明如下。þ GLOBAL：指定cursor_name為全局游標。þ cursor_name：開放游標的名稱。如果全局游標和局部游標都使用curs

25、or_name作為它們的名稱，那么當指定GLOBAL時，cursor_name引用全局游標；否則，cursor_name引用局部游標。þ cursor_variable_name：與開放游標關聯的游標變量名稱。【例13.6】 聲明一個名為CloseCursor的游標，并使用Close語句關閉游標。（實例位置：光盤 TMsl136）SQL語句如下：USE db_2012DECLARE CloseCursor Cursor FOR SELECT * FROM StudentFOR READ ONLYOPEN CloseCursorCLOSE CloseCursor運行結果如圖13.6所示

26、。圖13.6 關閉游標13.2.5 釋放游標當游標關閉之后，并沒有在內存中釋放所占用的系統資源，所以可以使用DEALLOCATE命令刪除游標引用。當釋放最后的游標引用時，組成該游標的數據結構由SQL Server釋放。語法如下：DEALLOCATE GLOBAL cursor_name | cursor_variable_name 參數說明如下。þ cursor_name：已聲明游標的名稱。當全局和局部游標都以cursor_name作為它們的名稱存在時，如果指定GLOBAL，則cursor_name引用全局游標，如果未指定GLOBAL，則cursor_name引用局部游標。þ

27、; cursor_variable_name：cursor變量的名稱。cursor_variable_name必須為cursor類型。當使用DEALLOCATE cursor_variable_name來刪除游標時，游標變量并不會被釋放，除非超過使用該游標的存儲過程和觸發器的范圍。【例13.7】 使用DEALLOCATE命令釋放名為FreeCursor的游標。（實例位置：光盤TMsl137）SQL語句如下：USE db_2012DECLARE FreeCursor Cursor FORSELECT * FROM StudentOPEN FreeCursorClose FreeCursorDEA

28、LLOCATE FreeCursor運行結果如圖13.7所示。圖13.7 釋放游標13.3 使用系統過程查看游標視頻講解：光盤TMlx13使用系統過程查看游標.mp4創建游標后，通常使用sp_cursor_list和sp_describe_cursor查看游標的屬性。sp_cursor_list用來報告當前為連接打開的服務器游標的屬性，sp_describe_cursor用于報告服務器游標的屬性。本節就詳細介紹這兩個系統過程。13.3.1 sp_cursor_listsp_cursor_list報告當前為連接打開的服務器游標的屬性。語法如下：sp_cursor_list cursor_retu

29、rn = cursor_variable_name OUTPUT , cursor_scope = cursor_scope參數說明如下。þ cursor_return = cursor_variable_name OUTPUT：已聲明的游標變量的名稱。cursor_ variable_name的數據類型為 cursor，無默認值。游標是只讀的可滾動動態游標。þ cursor_scope = cursor_scope：指定要報告的游標級別。cursor_scope的數據類型為int，無默認值，可取值如表13.3所示。表13.3 cursor_scope可取的值值說 明1報告

30、所有本地游標2報告所有全局游標3報告本地游標和全局游標【例13.8】 聲明一個游標Cur_Employee，并使用sp_cursor_list報告該游標的屬性。（實例位置：光盤TMsl138）SQL語句如下：USE db_2012GODECLARE Cur_Employee CURSOR FORSELECT NameFROM EmployeeWHERE Name LIKE '王%'OPEN Cur_EmployeeDECLARE Report CURSOREXEC master.dbo.sp_cursor_list cursor_return = Report OUTPUT,

31、cursor_scope = 2FETCH NEXT from ReportWHILE (FETCH_STATUS <> -1)BEGIN FETCH NEXT from ReportENDCLOSE ReportDEALLOCATE ReportGOCLOSE Cur_EmployeeDEALLOCATE Cur_EmployeeGO運行結果如圖13.8所示。圖13.8 sp_cursor_list屬性13.3.2 sp_describe_cursorsp_describe_cursor用于報告服務器游標的屬性。語法如下：sp_describe_cursor cursor_ret

32、urn = output_cursor_variable OUTPUT , cursor_source = N'local' , cursor_identity = N'local_cursor_name' | , cursor_source = N'global' , cursor_identity = N'global_cursor_name' | , cursor_source = N'variable' , cursor_identity = N'input_cursor_variable'

33、 sp_describe_cursor語句的參數及說明如表13.4所示。表13.4 sp_describe_cursor語句的參數及說明參 數描 述 cursor_return = output_ cursor_variable OUTPUT用于接收游標輸出的聲明游標變量的名稱。output_cursor_variable的數據類型為cursor，無默認值。調用sp_describe_cursor時，該參數不得與任何游標關聯。返回的游標是可滾動的動態只讀游標 cursor_source = N'local'| N'global' | N'variable' 指定是使用局部游標的名稱、全局游標的名稱還是游標變量的名稱來指定要報告的游標。該參數的類型為nvarchar(30) cursor_identity = N'local_ cursor_name' 由具有LOCAL關鍵字或默認設置為LOCAL的DECLARE CURSOR語句創建的游標