guo第10章游標(biāo)操作和應(yīng)用

10.1游標(biāo)聲明10.2游標(biāo)數(shù)據(jù)操作10.3游標(biāo)應(yīng)用實(shí)例分析第10章游標(biāo)操作和應(yīng)用

110.1游標(biāo)聲明

1．DECLARECURSOR命令(SQL-92標(biāo)準(zhǔn))

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

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

DECLARE學(xué)生表_cur1SCROLLCURSORFORSELECT*FROM學(xué)生表WHERE專業(yè)='計(jì)算機(jī)'42．DECLARECURSOR命令(Transact-SQL)語法格式：DECLARE<游標(biāo)名>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選項(xiàng)分別說明DECLARECURSOR語句所聲明的游標(biāo)為局部游標(biāo)和全局游標(biāo)。（2）FORWARD_ONLY選項(xiàng)聲明只進(jìn)游標(biāo)，即FETCH語句中只能使用NEXT選項(xiàng)。（3）STATIC與SQL-92聲明中的INSENTIVE關(guān)鍵字的功能相同，它將游標(biāo)聲明為靜態(tài)游標(biāo)，禁止應(yīng)用程序通過它修改基表數(shù)據(jù)。（4）DYNAMIC將游標(biāo)聲明為動(dòng)態(tài)游標(biāo)，也就是說，其結(jié)果結(jié)合是動(dòng)態(tài)變化的，能夠隨時(shí)反映用戶已提交的更改結(jié)果。提取動(dòng)態(tài)游標(biāo)數(shù)據(jù)時(shí)，不能使用ABSOLUTE提取選項(xiàng)定位游標(biāo)指針。（5）KEYSET關(guān)鍵字聲明鍵集驅(qū)動(dòng)游標(biāo)，鍵集游標(biāo)中的數(shù)據(jù)行及其順序是固定的。6

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

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

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

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

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

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

格式：

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

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

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

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

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

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

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

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

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

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

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

31游標(biāo)的定位修改示例OPENstu_up_curFETCHNEXTFROMstu_up_curINTO@學(xué)號,@姓名,@移動(dòng)電話,@籍貫WHILE@@fetch_status=0BEGINSELECT@學(xué)號,@姓名,@移動(dòng)電話,@籍貫IF@學(xué)號='S060109'UPDATE學(xué)生表SET移動(dòng)電話=,籍貫='天津'WHERECURRENTOFstu_up_curFETCHNEXTFROMstu_up_curINTO@學(xué)號,@姓名,@移動(dòng)電話,@籍貫ENDCLOSEstu_up_cur3210.3游標(biāo)應(yīng)用實(shí)例分析

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

3310.3游標(biāo)應(yīng)用實(shí)例分析SET

NOCOUNT

ONDECLARE@開課號

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

INT,@已選人數(shù)

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

cursorFORSELECT

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

開課表FOR

UPDATE

OF

已選人數(shù)3410.3游標(biāo)應(yīng)用實(shí)例分析--打開游標(biāo)，根據(jù)開課表依次提取每個(gè)開課計(jì)劃的數(shù)據(jù)OPENCUR_