guo第10章游標操作和應用

10.1游標聲明10.2游標數(shù)據(jù)操作10.3游標應用實例分析第10章游標操作和應用

110.1游標聲明

1．DECLARECURSOR命令(SQL-92標準)

語法格式：DECLARE<游標名>[INSENSITIVE][SCROLL]CURSORFOR<SELECT查詢>[FOR{READONLY|UPDATE[OF<列名>[,<列名>…]]}]

2參數(shù)說明：（1）INSENSITIVE指出所聲明的游標為不敏感游標，即靜態(tài)游標。當省略INSENSITIVE選項時，已提交的游標基表修改和刪除操作能夠反映到其后的游標提取結(jié)果中。（2）SCROLL指出該游標可以用FETCH命令里定義的所有方法來存取數(shù)據(jù)，允許刪除和更新（假定沒有使用INSENSITIVE選項）；（3）<SELECT查詢>語句決定游標結(jié)果集合，但在其中不能使用COMPUTE、COMPUTEBY、FORBROWSE和INTO等關(guān)鍵字。（4）FORREADONLY或FORUPDATE說明游標為只讀的或可修改的。默認是可修改的。（5）UPDATE[OF<列名>[,<列名>…]]定義可以修改的列。如果省略O(shè)F<列名>[,<列名>…]，則允許修改所有列。3【例10-1】下面語句聲明游標學生表_cur1

DECLARE學生表_cur1SCROLLCURSORFORSELECT*FROM學生表WHERE專業(yè)='計算機'42．DECLARECURSOR命令(Transact-SQL)語法格式：DECLARE<游標名>CURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWAR][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FOR<SELECT查詢>[FORUPDATE[OF<列名>[,<列名>…]]]5

參數(shù)說明：（1）LOCAL和GLOBAL選項分別說明DECLARECURSOR語句所聲明的游標為局部游標和全局游標。（2）FORWARD_ONLY選項聲明只進游標，即FETCH語句中只能使用NEXT選項。（3）STATIC與SQL-92聲明中的INSENTIVE關(guān)鍵字的功能相同，它將游標聲明為靜態(tài)游標，禁止應用程序通過它修改基表數(shù)據(jù)。（4）DYNAMIC將游標聲明為動態(tài)游標，也就是說，其結(jié)果結(jié)合是動態(tài)變化的，能夠隨時反映用戶已提交的更改結(jié)果。提取動態(tài)游標數(shù)據(jù)時，不能使用ABSOLUTE提取選項定位游標指針。（5）KEYSET關(guān)鍵字聲明鍵集驅(qū)動游標，鍵集游標中的數(shù)據(jù)行及其順序是固定的。6

（6）FAST_FORWARD指出起用優(yōu)化的FORWARD_ONLY和READ_ONLY游標。（7）SCROLL_LOCKS選項要求SQLServer在將數(shù)據(jù)讀入游標的同時鎖定基表中的數(shù)據(jù)行，以確保以后能夠通過游標成功地對基表進行定位刪除和修改操作。（8）OPTIMISTIC說明在填充游標時不鎖定基表中的數(shù)據(jù)行。（9）TYPE_WARNING指出在聲明游標過程中，如果無法建立用戶指定類型的游標而隱式轉(zhuǎn)換為另一類型時，給客戶端發(fā)出警告消息。（10）FORUPDATE[OF<列名>[,<列名>…]]定義游標內(nèi)可更新的列。如果提供了OF<列名>[,<列名>…]，則只允許修改列出的列。如果在UPDATE中未指定列的列表，除非指定了READ_ONLY并發(fā)選項，否則所有列均可更新。7【例10-2】下面語句聲明游標學生表_cur2

DECLARE學生表_cur2CURSORLOCALSCROLLDYNAMICTYPE_WARNINGFORSELECT學號,姓名,性別,籍貫,所在院系,累計學分FROM學生表WHERE專業(yè)='計算機'83．查看游標信息和狀態(tài)sp_cursor_list：檢索當前連接所有可見游標sp_describe_cursor：檢索游標屬性信息，如作用域、名稱、類型、狀態(tài)和行數(shù)。sp_describe_cursor_columns：檢索游標結(jié)果集合中的列屬性。sp_describe_cursor_tables：檢索游標鎖引用的基表信息。@@CURSOR_STATUS：讀取游標狀態(tài)或檢查游標變量是否與游標相關(guān)聯(lián)。@@FETCH_STATUS：讀取最后一次游標數(shù)據(jù)提取操作結(jié)果狀態(tài)。0：取操作成功；-1：取操作失敗，所指定的位置超出了范圍；-2：要取的行不在記錄集內(nèi)，已從集合中刪除。@@CURSOR_ROWS：顯示游標集合中的行數(shù)。-n：正在向游標中載入數(shù)據(jù)，反映的是結(jié)果集當前的數(shù)據(jù)行數(shù)；n：結(jié)果集合的行數(shù)；0：結(jié)果集中沒有匹配的行；-1：指出游標是動態(tài)的。910.1.2游標變量有兩種方法建立游標和游標變量之間的關(guān)聯(lián)：第一種：先聲明游標和游標變量，之后用SET語句將游標賦給游標變量。例如：DECLARE@cur_varCURSORDECLAREC1CURSORFORSELECT*FROM課程表SET@cur_var=C1第二種：不聲明游標，直接在SET語句中將各種游標定義賦給游標變量。例如：DECLARE@cur_varCURSORSET@cur_var=CURSORFORSELECT*FROM課程表

1010.1.3隱式游標轉(zhuǎn)換

應用程序可以請求一個游標類型，然后執(zhí)行不受所請求類型的服務器游標支持的一個Transact-SQL語句。MicrosoftSQLServe返回一個錯誤，指出游標類型已經(jīng)改變。 下表的因素可觸發(fā)SQLServer將游標從一種類型隱性轉(zhuǎn)換為另一種類型。11步驟觸發(fā)轉(zhuǎn)換的操作只進快速只進鍵集驅(qū)動Dynamic轉(zhuǎn)至步驟1游標引用包含TOP子句的視圖。變?yōu)殪o態(tài)變?yōu)殪o態(tài)變?yōu)殪o態(tài)變?yōu)殪o態(tài)完成2查詢FROM子句沒有引用表。變?yōu)殪o態(tài)變?yōu)殪o態(tài)變?yōu)殪o態(tài)完成3HAVING變?yōu)殪o態(tài)變?yōu)殪o態(tài)變?yōu)殪o態(tài)完成4查詢在觸發(fā)器內(nèi)引用inserted或deleted表。變?yōu)殪o態(tài)變?yōu)殪o態(tài)變?yōu)殪o態(tài)完成5查詢將觸發(fā)器表聯(lián)接到另一個表。變?yōu)殪o態(tài)完成6沒有指定READ_ONLY。變?yōu)殪o態(tài)87ODBCAPI服務器游標引用text、ntext或image列。變?yōu)閯討B(tài)88查詢生成內(nèi)部工作表，例如ORDERBY的列沒有被索引覆蓋。變?yōu)殒I集變?yōu)殒I集109查詢引用鏈接服務器中的遠程表。變?yōu)殒I集變?yōu)殒I集變?yōu)殒I集1010查詢至少引用了一個沒有唯一索引的表。變?yōu)殪o態(tài)完成11游標引用text、ntext或image列；并且查詢包含TOP子句。變?yōu)殒I集完成1210.2游標數(shù)據(jù)操作10.2.1打開游標打開游標在聲明以后，如果要從游標中讀取數(shù)據(jù)必須打開游標。打開一個Transact-SQL服務器游標使用OPEN命令。語法格式：OPEN{{[GLOBAL]<游標名>}|<游標變量>}參數(shù)說明：（1）GLOBAL定義游標為一全局游標。（2）游標名如果一個全局游標和一個局部游標都使用同一個游標名，則如果使用GLOBAL便表明其為全局游標，否則表明其為局部游標。（3）游標變量為定義的游標變量。當打開一個游標后時，MSSQLSERVER首先檢查聲明游標的語法是否正確，如果游標聲明中有變量，則將變量值帶入。13

在游標被成功打開之后，@@CURSOR_ROWS全局變量將用來記錄游標內(nèi)數(shù)據(jù)行數(shù)。為了提高性能，MSSQLSERVER允許以異步方式從基礎(chǔ)表向KEYSET或靜態(tài)游標讀入數(shù)據(jù)，即如果MSSQLSERVER的查詢優(yōu)化器估計從基礎(chǔ)表中返回給游標的數(shù)據(jù)行已經(jīng)超過sp_configurecursorthreshold參數(shù)值，則MSSQLSERVER將啟動另外一個獨立的線程來繼續(xù)從基礎(chǔ)表中讀入符合游標定義的數(shù)據(jù)行，此時可以從游標中讀取數(shù)據(jù)進行處理而不必等到所有的符合游標定義的數(shù)據(jù)行都從基礎(chǔ)表中讀入游標。@@CURSOR_ROWS變量存儲的正是在調(diào)用@@CURSOR_ROWS時，游標已從基礎(chǔ)表讀入的數(shù)據(jù)行。@@CURSOR_ROWS的返回值有以下四個，如表10-2所示。14表10-2@@CURSOR_ROWS變量返回值描述-m表示正在向游標中載入數(shù)據(jù)，反映的是結(jié)果集當前的數(shù)據(jù)行數(shù)-1表示該游標是一個動態(tài)游標，由于動態(tài)游標反映基礎(chǔ)表的所有變化，因此符合游標定義的數(shù)據(jù)行經(jīng)常變動，故無法確定0表示無符合條件的記錄或游標已關(guān)閉n表示從基礎(chǔ)表讀入數(shù)據(jù)已經(jīng)結(jié)束，n即為游標中結(jié)果集的行數(shù)1510.2.2讀取游標數(shù)據(jù)

當游標被成功打開以后，就可以從游標中逐行地讀取數(shù)據(jù)，以進行相關(guān)處理。從游標中讀取數(shù)據(jù)主要使用FETCH命令。

格式：

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]<游標名>}|cursor_variable_name}[INTO@<變量名1>,@<變量名2>…]16參數(shù)說明：（1）NEXT說明如果是在OPEN后第一次執(zhí)行FETCH命令，則返回結(jié)果集的第一行，否則使游標（指針）指向結(jié)果集的下一行；NEXT是默認的選項，也是最常用的一種方法（2）PRIOR、FIRST、LAST、ABSOLUTE{n|@nvar}、RELATIVE{n|@nvar}等各項，只有在定義游標時使用了SCROLL選項才可以使用；PRIOR是返回結(jié)果集當前行的前一行；如果FETCHPRIOR是第一次讀取游標中數(shù)據(jù)，則無數(shù)據(jù)記錄返回，并把游標位置設(shè)為第一行。FIRST是返回結(jié)果集的第一行；LAST是返回結(jié)果集的最后一行；17

ABSOLUTE{n|@nvar}如果n或@nvar為正數(shù)，則返回游標結(jié)果集中的第n或@nvar行數(shù)據(jù)。如果n或@nvar為負數(shù)，則返回結(jié)果集內(nèi)倒數(shù)第n或@nvar行數(shù)據(jù)。若n或@nvar超過游標的數(shù)據(jù)子集范疇，則@@FETCH_STARS返回-1，在該情況下，如果n或@nvar為負數(shù)，則執(zhí)行FETCHNEXT命令會得到第一行數(shù)據(jù)，如果n或@nvar為正值，執(zhí)行FETCHPRIOR命令則會得到最后一行數(shù)據(jù)。n或@nvar可以是一固定值也可以是一smallint,tinyint或int類型的變量。18

RELATIVE{n|@nvar}若n或@nvar為正數(shù)，則讀取游標當前位置起向后的第n或@nvar行數(shù)據(jù)；如果n或@nvar為負數(shù)，則讀取游標當前位置起向前的第n或@nvar行數(shù)據(jù)。若n或@nvar超過游標的數(shù)據(jù)子集范疇，則@@FETCH_STARS返回-1，在該情況下，如果n或@nvar為負數(shù)，則執(zhí)行FETCHNEXT命令則會得到第一行數(shù)據(jù)；如果n或@nvar為正值，執(zhí)行FETCHPRIOR命令則會得到最后一行數(shù)據(jù)。n或@nvar可以是一固定值也可以是一smallint,tinyint或int類型的變量。19返回值描述0FETCH命令被成功執(zhí)行。-1FETCH命令失敗或所指定的范圍超出了范圍。-2要取的行不在記錄集內(nèi)，已從集合中刪除

表10-3@@FETCH_STATUS變量2010.2.3關(guān)閉游標

在處理完游標中數(shù)據(jù)之后必須關(guān)閉游標來釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖，有兩種方法關(guān)閉游標。1．使用CLOSE命令關(guān)閉游標 格式：CLOSE{{[GLOBAL]游標名}|游標變量}說明：CLOSE語句關(guān)閉游標，但不釋放游標占用的數(shù)據(jù)結(jié)構(gòu)，應用程序可以再次執(zhí)行OPEN語句打開和填充游標。2．自動關(guān)閉游標游標可應用在存儲過程、觸發(fā)器和Transact_SQL腳本中。如果在聲明游標與釋放游標之間使用了事務結(jié)構(gòu)，則在結(jié)束事務時游標會自動關(guān)閉。21

使用游標的執(zhí)行過程：（1）聲明一個游標（2）打開游標（3）讀取游標（4）BEGINTRANSATION（5）數(shù)據(jù)處理（6）COMMITTRANSATION（6）回到步驟(3)2210.2.4釋放游標

當CLOSE命令關(guān)閉游標時，并沒有釋放游標占用的數(shù)據(jù)結(jié)構(gòu)。因此要使用DEALLOCATE命令，刪除掉游標與游標名或游標變量之間的聯(lián)系，并且釋放游標占用的所有系統(tǒng)資源。語法格式：DEALLOCATE{{[GLOBAL]游標名}|游標變量}23參數(shù)說明：（1）對游標進行操作的語句使用游標名稱或游標變量引用游標。DEALLOCATE刪除游標與游標名稱或游標變量之間的關(guān)聯(lián)。如果一個名稱或變量是最后引用游標的名稱或變量，則將釋放游標，游標使用的任何資源也隨之釋放。（2）DEALLOCATE{游標變量}語句只刪除對游標命名變量的引用。直到批處理、存儲過程或觸發(fā)器結(jié)束時變量離開作用域，才釋放變量。在DEALLOCATE@cursor_variable_name語句之后，可以使用SET語句使變量與另一個游標關(guān)聯(lián)。24例如：USE教學管理GODECLARE@mycursorCURSORSET@mycursor=CURSORLOCALSCROLLFORSELECT*FROM 學生表--下面語句釋放游標及游標占用的資源DEALLOCATE@mycursor--用SET命令將游標變量@mycursor同另一個游標關(guān)聯(lián)SET@mycursor=CURSORLOCALSCROLLFORSELECT*FROM課程表GO25游標變量的使用和釋放示例

USE教學管理GO--創(chuàng)建一個全局游標，使其在創(chuàng)建它的批的外部仍然有效。DECLARES_curCURSORGLOBALSCROLLFORSELECT*FROM學生表OPENS_curGO--定義一個游標變量并使其與abc游標關(guān)聯(lián)。DECLARE@mycrsrref1CURSORSET@mycrsrref1=S_cur26游標變量的使用和釋放示例--取消游標變量和游標的關(guān)聯(lián)。DEALLOCATE@mycrsrref1--游標S-CUR依然存在。FETCHNEXTFROMS_curGO--再次與游標建立關(guān)聯(lián)。DECLARE@mycrsrref2CURSORSET@mycrsrref2=S_cur--現(xiàn)在釋放游標S-CUR。DEALLOCATES_cur27游標變量的使用和釋放示例--但游標依然存在，因為被游標變量@mycrsrref2引用著。FETCHNEXTFROM@mycrsrref2--游標在批結(jié)束后，隨著最后一個游標變量作用域的結(jié)束而最終被釋放，因變量的作用域是定義它的批。GO--創(chuàng)建一個未命名的游標DECLARE@mycursorCURSORSET@mycursor=CURSORLOCALSCROLLFORSELECT*FROM課程表--以下語句釋放游標，因為已沒有其它變量引用該游標DEALLOCATE@mycursorGO2810.2.5游標定位修改和刪除操作

如果在聲明游標時使用了FORUPDATE語句，那么就可以在UPDATE或DELETE命令中以WHERECURRENTOF關(guān)鍵字直接修改或刪除當前游標中當前行的數(shù)據(jù)。當改變游標中數(shù)據(jù)時，這種變化會自動地影響到游標的基礎(chǔ)表。但是如果在聲明游標時選擇了INSENSITIVE選項時，該游標中的數(shù)據(jù)不能被修改，具體含義請參看聲明游標一節(jié)中對INSENSITIVE選項的詳細解釋。

29語法格式：游標定位修改UPDATE語句的格式如下UPDATE表名SET子句WHERECURRENTOF{{[GLOBAL]游標名}|游標變量}游標定位刪除DELETE語句的格式為如下DELETEFROM表名WHERECURRENTOF{{[GLOBAL]游標名}|游標變量}30游標的定位修改示例

首先查看學生表表中每一行，將學號等于’S060109’記錄的移動電話改并將城市改為‘天津’。SETNOCOUNTONDECLARE@學號CHAR(6),@姓名CHAR(10),@移動電話CHAR(11),@籍貫CHAR(10)DECLAREstu_up_curcursorFORSELECT學號,姓名,移動電話,籍貫FROM學生表FORUPDATEOF移動電話,籍貫

31游標的定位修改示例OPENstu_up_curFETCHNEXTFROMstu_up_curINTO@學號,@姓名,@移動電話,@籍貫WHILE@@fetch_status=0BEGINSELECT@學號,@姓名,@移動電話,@籍貫IF@學號='S060109'UPDATE學生表SET移動電話=,籍貫='天津'WHERECURRENTOFstu_up_curFETCHNEXTFROMstu_up_curINTO@學號,@姓名,@移動電話,@籍貫ENDCLOSEstu_up_cur3210.3游標應用實例分析

【例】在開課表里，由于每一個開課號對應的學生選課都有限制，所以學生一旦選課確定，需要和該開課號的限選人數(shù)進行比對，如果沒有超過限選人數(shù)，已選人數(shù)應及時更正。第7章我們用插入語句在選課表了輸入了一些數(shù)據(jù)，即進行了選課，但并沒有采取必要的約束改變開課表里的已選人數(shù)（后面可以通過觸發(fā)器進行處理），因此開課表和選課表的數(shù)據(jù)并不一致。為了糾正錯誤，可以使用游標，逐個檢查并修改每個開課號在選課表中的學生選修人數(shù)，顯示輸出。

3310.3游標應用實例分析SET

NOCOUNT

ONDECLARE@開課號

CHAR(6),@限選人數(shù)

INT,@已選人數(shù)

INTDECLARE@messageCHAR(80)--創(chuàng)建包含開課號、限選人數(shù)和已選人數(shù)信息的游標CUR_選課人數(shù)DECLARECUR_選課人數(shù)

cursorFORSELECT

開課號,限選人數(shù)FROM

開課表FOR

UPDATE

OF

已選人數(shù)3410.3游標應用實例分析--打開游標，根據(jù)開課表依次提取每個開課計劃的數(shù)據(jù)OPENCUR_