1、第10章游標操作和應用第10章 游標操作和應用 10.1 10.2 游標數(shù)據(jù)操作 10.3 游標應用實例分析 2022年9月21日210.1 游標聲明10.1.1 游標聲明（1）1DECLARE CURSOR命令(SQL-92標準) 語法格式： DECLARE INSENSITIVESCROLLCURSOR FOR FORREAD ONLY|UPDATEOF , 2022年9月21日310.1 游標聲明 10.1.1 游標聲明（1） 參數(shù)說明： （1）INSENSITIVE指出所聲明的游標為不敏感游標，即靜態(tài)游標。當省略INSENSITIVE選項時，已提交的游標基表修改和刪除操作能夠反映到其后

2、的游標提取結(jié)果中。 （2）SCROLL指出該游標可以用FETCH命令里定義的所有方法來存取數(shù)據(jù)，允許刪除和更新（假定沒有使用INSENSITIVE選項）； （3）語句決定游標結(jié)果集合，但在其中不能使用COMPUTE、COMPUTE BY、FOR BROWSE和INTO等關鍵字。 （4）FOR READ ONLY或FOR UPDATE說明游標為只讀的或可修改的。默認是可修改的。 （5）UPDATE OF , 定義可以修改的列。如果省略 OF ,，則允許修改所有列。2022年9月21日410.1.1 游標聲明10.1.1 游標聲明（1）【例10-1】下面語句聲明游標CSstudent_cur1：

3、【例10-1】下面的語句聲明游標“學生表_cur1”。DECLARE 學生表_cur1 SCROLL CURSORFOR SELECT 學號,姓名,性別,籍貫,所在院系,累計學分 FROM 學生表 WHERE 專業(yè)=計算機2022年9月21日10.1 游標聲明10.1.1 游標聲明（2）2DECLARE CURSOR命令(Transact-SQL) 語法格式： DECLARE CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWAR READ_ONLY|SCROLL_LOCKS|OPTIMISTIC TY

4、PE_WARNING FOR FOR UPDATEOF , 2022年9月21日610.1 游標聲明10.1.1 游標聲明（2）參數(shù)說明： （1）LOCAL和GLOBAL選項分別說明DECLARE CURSOR語句所聲明的游標為局部游標和全局游標。 （2）FORWARD_ONLY選項聲明只進游標，即FETCH語句中只能使用NEXT選項。 （3）STATIC與SQL-92聲明中的INSENTIVE關鍵字的功能相同，它將游標聲明為靜態(tài)游標，禁止應用程序通過它修改基表數(shù)據(jù)。 （4）DYNAMIC將游標聲明為動態(tài)游標，也就是說，其結(jié)果結(jié)合是動態(tài)變化的，能夠隨時反映用戶已提交的更改結(jié)果。提取動態(tài)游標數(shù)據(jù)

5、時，不能使用ABSOLUTE提取選項定位游標指針。 （5）KEYSET關鍵字聲明鍵集驅(qū)動游標，鍵集游標中的數(shù)據(jù)行及其順序是固定的。2022年9月21日10.1 游標聲明10.1.1 游標聲明（2）參數(shù)說明： （1）LOCAL和GLOBAL選項分別說明DECLARE CURSOR語句所聲明的游標為局部游標和全局游標。 （2）FORWARD_ONLY選項聲明只進游標，即FETCH語句中只能使用NEXT選項。 （3）STATIC與SQL-92聲明中的INSENTIVE關鍵字的功能相同，它將游標聲明為靜態(tài)游標，禁止應用程序通過它修改基表數(shù)據(jù)。 （4）DYNAMIC將游標聲明為動態(tài)游標，也就是說，其結(jié)果

6、結(jié)合是動態(tài)變化的，能夠隨時反映用戶已提交的更改結(jié)果。提取動態(tài)游標數(shù)據(jù)時，不能使用ABSOLUTE提取選項定位游標指針。 （5）KEYSET關鍵字聲明鍵集驅(qū)動游標，鍵集游標中的數(shù)據(jù)行及其順序是固定的。2022年9月21日10.1 游標申明10.1.1 游標申明（2）（6）FAST_FORWARD指出起用優(yōu)化的FORWARD_ONLY和READ_ONLY游標。 （7）SCROLL_LOCKS選項要求SQL Server在將數(shù)據(jù)讀入游標的同時鎖定基表中的數(shù)據(jù)行，以確保以后能夠通過游標成功地對基表進行定位刪除和修改操作。 （8）OPTIMISTIC說明在填充游標時不鎖定基表中的數(shù)據(jù)行。 （9）TYPE

7、_WARNING指出在聲明游標過程中，如果無法建立用戶指定類型的游標而隱式轉(zhuǎn)換為另一類型時，給客戶端發(fā)出警告消息。 （10）FOR UPDATEOF , 定義游標內(nèi)可更新的列。如果提供了 OF ,，則只允許修改列出的列。如果在 UPDATE 中未指定列的列表，除非指定了 READ_ONLY 并發(fā)選項，否則所有列均可更新。2022年9月21日10.1 游標聲明10.1.1 游標聲明（2）【例10-2】下面的語句聲明游標學生表_cur2。DECLARE 學生表_cur2 CURSORLOCAL SCROLL DYNAMICTYPE_WARNINGFOR SELECT 學號,姓名,性別,籍貫,所在院

8、系,累計學分 FROM 學生表 WHERE 專業(yè)=計算機2022年9月21日10.1 游標聲明10.1.1 游標聲明（2）3查看游標信息和狀態(tài)在Transact_SQL程序中，執(zhí)行以下系統(tǒng)存儲過程和函數(shù)能夠檢索游標屬性信息和狀態(tài)信息：sp_cursor_list：檢索當前連接所有可見游標sp_describe_cursor：檢索游標屬性信息，如作用域、名稱、類型、狀態(tài)和行數(shù)。sp_describe_cursor_columns：檢索游標結(jié)果集合中的列屬性。sp_describe_cursor_tables：檢索游標鎖引用的基表信息。2022年9月21日CURSOR_STATUS：讀取游標狀態(tài)或

9、檢查游標變量是否與游標相關聯(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)的。12 2022年9月21日1.1.2 游標變量游標變量聲明的格式：DECLARE cursor_variable_name CURSOR游標變量聲明后，必須和某個游標相關聯(lián)才能實現(xiàn)游標操作。有兩種方法建立游標和游標變量之間的關聯(lián)

10、：132022年9月21日1.1.2 游標變量 第一種： 先聲明游標和游標變量，之后用SET語句將游標賦給游標變量。例如： DECLARE cur_var CURSOR DECLARE C1 CURSOR FOR SELECT * FROM COURSE SET cur_var=C12022年9月21日1.1.2 游標變量第二種：不聲明游標，直接在SET語句中將各種游標定義賦給游標變量例如： DECLARE cur_var CURSOR SET cur_var= CURSOR FOR SELECT * FROM COURSE當游標變量與游標相關聯(lián)之后，在Transact-SQL游標語句中就可以

11、使用游標變量代替游標名，實現(xiàn)各種操作。 例如： OPEN cur_var2022年9月21日10.2 游標數(shù)據(jù)操作 10.2.1 打開游標打開游標在聲明以后，如果要從游標中讀取數(shù)據(jù)必須打開游標。打開一個Transact-SQL服務器游標使用OPEN命令。語法格式：OPEN GLOBAL | 參數(shù)說明：（1）GLOBAL 定義游標為一全局游標。（2）游標名 如果一個全局游標和一個局部游標都使用同一個游標名，則如果使用GLOBAL便表明其為全局游標，否則表明其為局部游標。（3）游標變量 為定義的游標變量。當打開一個游標后時，MS SQL SERVER首先檢查聲明游標的語法是否正確，如果游標聲明中有

12、變量，則將變量值帶入。162022年9月21日10.2.1 打開游標 在游標被成功打開之后，CURSOR_ROWS 全局變量將用來記錄游標內(nèi)數(shù)據(jù)行數(shù)。為了提高性能，MS SQL SERVER允許以異步方式從基礎表向KEYSET或靜態(tài)游標讀入數(shù)據(jù)，即如果MS SQL SERVER的查詢優(yōu)化器估計從基礎表中返回給游標的數(shù)據(jù)行已經(jīng)超過sp_configure cursor threshold參數(shù)值，則MS SQL SERVER將啟動另外一個獨立的線程來繼續(xù)從基礎表中讀入符合游標定義的數(shù)據(jù)行，此時可以從游標中讀取數(shù)據(jù)進行處理而不必等到所有的符合游標定義的數(shù)據(jù)行都從基礎表中讀入游標。CURSOR_ROW

13、S 變量存儲的正是在調(diào)用CURSOR_ROWS時，游標已從基礎表讀入的數(shù)據(jù)行。CURSOR_ROWS的返回值有以下四個，如表10-1所示。2022年9月21日表10-1 CURSOR_ROWS變量182022年9月21日10.2.2 讀取游標數(shù)據(jù) 當游標被成功打開以后，就可以從游標中逐行地讀取數(shù)據(jù)，以進行相關處理。從游標中讀取數(shù)據(jù)主要使用FETCH 命令。格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar | RELATIVE n |nvar FROM GLOBAL |cursor_variable_name INTO , 2022年9月21日 參數(shù)說

14、明：（1）NEXT說明如果是在OPEN后第一次執(zhí)行FETCH命令，則返回結(jié)果集的第一行，否則使游標（指針）指向結(jié)果集的下一行；NEXT是默認的選項，也是最常用的一種方法（2）PRIOR、FIRST、LAST、ABSOLUTE n|nvar、RELATIVE n|nvar等各項，只有在定義游標時使用了SCROLL選項才可以使用； PRIOR是返回結(jié)果集當前行的前一行；如果FETCH PRIOR是第一次讀取游標中數(shù)據(jù)，則無數(shù)據(jù)記錄返回，并把游標位置設為第一行。 FIRST是返回結(jié)果集的第一行； LAST是返回結(jié)果集的最后一行；202022年9月21日 ABSOLUTE n | nvar 如果n或n

15、var為正數(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í)行FETCH NEXT命令會得到第一行數(shù)據(jù)，如果n或nvar為正值，執(zhí)行FETCH PRIOR命令則會得到最后一行數(shù)據(jù)。n或nvar 可以是一固定值也可以是一smallint, tinyint 或int類型的變量。212022年9月21日RELATIVE n | nvar 若n或nvar為正數(shù)，則讀取游標當前位置起向后的第n或nvar行數(shù)據(jù)；如果n或nvar為負

16、數(shù)，則讀取游標當前位置起向前的第n或nvar行數(shù)據(jù)。若n或nvar超過游標的數(shù)據(jù)子集范疇，則FETCH_STARS 返回-1，在該情況下，如果n或nvar為負數(shù)，則執(zhí)行FETCH NEXT命令則會得到第一行數(shù)據(jù)；如果n或nvar為正值，執(zhí)行FETCH PRIOR命令則會得到最后一行數(shù)據(jù)。n 或nvar可以是一固定值也可以是一smallint, tinyint或int類型的變量。222022年9月21日23 表10-2 FETCH_STATUS變量2022年9月21日10.2.3 關閉游標在處理完游標中數(shù)據(jù)之后必須關閉游標來釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖，有兩種方法關閉游標。1使用CLOS

17、E命令關閉游標格式：CLOSE GLOBAL 游標名 | 游標變量 說明：CLOSE語句關閉游標，但不釋放游標占用的數(shù)據(jù)結(jié)構(gòu)，應用程序可以再次執(zhí)行OPEN語句打開和填充游標。2自動關閉游標 游標可應用在存儲過程、觸發(fā)器和Transact_SQL腳本中。如果在聲明游標與釋放游標之間使用了事務結(jié)構(gòu)，則在結(jié)束事務時游標會自動關閉。2022年9月21日例如以下執(zhí)行過程：（1）聲明一個游標（2）打開游標（3）讀取游標（4）BEGIN TRANSATION（5）數(shù)據(jù)處理（6）COMMIT TRANSATION（6）回到步驟(3)252022年9月21日 10.2.4 釋放游標當CLOSE命令關閉游標時，并

18、沒有釋放游標占用的數(shù)據(jù)結(jié)構(gòu)。因此要使用DEALLOCATE命令，刪除掉游標與游標名或游標變量之間的聯(lián)系，并且釋放游標占用的所有系統(tǒng)資源。語法格式：DEALLOCATE GLOBAL 游標名 | 游標變量262022年9月21日27參數(shù)說明：（1）對游標進行操作的語句使用游標名稱或游標變量引用游標。DEALLOCATE刪除游標與游標名稱或游標變量之間的關聯(lián)。如果一個名稱或變量是最后引用游標的名稱或變量，則將釋放游標，游標使用的任何資源也隨之釋放。（2）DEALLOCATE游標變量語句只刪除對游標命名變量的引用。直到批處理、存儲過程或觸發(fā)器結(jié)束時變量離開作用域，才釋放變量。在DEALLOCATE

19、cursor_variable_name 語句之后，可以使用SET語句使變量與另一個游標關聯(lián)。2022年9月21日例如：USE 教學管理GODECLARE MyCursor CURSORSET MyCursor = CURSOR LOCAL SCROLL FORSELECT * FROM STUDENT-下面語句釋放游標及游標占用的資源DEALLOCATE MyCursor-用SET命令將游標變量MyCursor同另一個游標關聯(lián)SET MyCursor = CURSOR LOCAL SCROLL FORSELECT * FROM COURSEGO282022年9月21日10.2.5 游標定位修

20、改和刪除操作 如果在聲明游標時使用了FOR UPDATE語句，那么就可以在UPDATE或DELETE命令中以WHERE CURRENT OF關鍵字直接修改或刪除當前游標中當前行的數(shù)據(jù)。當改變游標中數(shù)據(jù)時，這種變化會自動地影響到游標的基礎表。但是如果在聲明游標時選擇了INSENSITIVE選項時，該游標中的數(shù)據(jù)不能被修改，具體含義請參看聲明游標一節(jié)中對INSENSITIVE選項的詳細解釋。292022年9月21日10.2.5 游標定位修改和刪除操作語法格式：游標定位修改UPDATE語句的格式如下UPDATE 表名 SET 子句 WHERE CURRENT OF GLOBAL 游標名 |游標變量游

21、標定位刪除DELETE語句的格式為如下DELETE FROM 表名 WHERE CURRENT OF GLOBAL 游標名 |游標變量2022年9月21日10.3 游標應用實例分析 【例10-6】使用游標逐個檢查并修改每個開課號在選課表中的學生選修人數(shù)，并顯示輸出。SET NOCOUNT ONDECLARE 開課號 CHAR(6), 限選人數(shù) INT, 已選人數(shù) INTDECLARE message CHAR(80)-創(chuàng)建包含開課號、限選人數(shù)和已選人數(shù)信息的游標CUR_選課人數(shù)。DECLARE CUR_選課人數(shù) cursor FOR SELECT 開課號,限選人數(shù)FROM 開課表 FOR UPDATE OF 已選人數(shù)-打開游標，根據(jù)開課表依次提取每個開課計劃的數(shù)據(jù)。OPEN CUR_選課人數(shù)FETCH NEXT FROM CUR_選課人數(shù) INTO 開課號,限選人數(shù)WHILE fetch_s