详解游标
概念
簡(jiǎn)單點(diǎn)說(shuō)游標(biāo)的作用就是存儲(chǔ)一個(gè)結(jié)果集，并根據(jù)語(yǔ)法將這個(gè)結(jié)果集的數(shù)據(jù)逐條處理。
觀點(diǎn)
正因?yàn)橛螛?biāo)可以將結(jié)果集一條條取出處理，所以會(huì)增加服務(wù)器的負(fù)擔(dān)。再者使用游標(biāo)的效率遠(yuǎn)遠(yuǎn)沒(méi)有使用默認(rèn)的結(jié)果集效率高，在默認(rèn)結(jié)果集中，從客戶端發(fā)送到服務(wù)器的唯一一個(gè)數(shù)據(jù)包是包含需執(zhí)行語(yǔ)句的數(shù)據(jù)包。而在使用服務(wù)器游標(biāo)時(shí)，每一個(gè)FETCH語(yǔ)句都必須從客戶端發(fā)送到服務(wù)器，然后在服務(wù)器中將它解析并編譯為執(zhí)行計(jì)劃。除非要再sqlserver上進(jìn)行很復(fù)雜的數(shù)據(jù)操作。
基本知識(shí)
一. SQL Server 2005 支持兩種請(qǐng)求游標(biāo)的方法
1.Transact-SQL (支持 SQL-92)；
2.數(shù)據(jù)庫(kù)應(yīng)用程序編程接口(API)游標(biāo)函數(shù)(ADO、OLE DB、ODBC)應(yīng)用程序不能混合使用這兩種請(qǐng)求游標(biāo)的方法。ODBC 還支持客戶端游標(biāo)，即在客戶 端實(shí)現(xiàn)的游標(biāo)。
二. 游標(biāo)根據(jù)范圍可以分成全局游標(biāo)和局部游標(biāo)。全局游標(biāo)可以在整個(gè)會(huì)話過(guò)程中使用，局部游標(biāo)只能在一個(gè)T-SQL批、存儲(chǔ)過(guò)程或觸發(fā)器中執(zhí)行，當(dāng)執(zhí)行完畢后局部游標(biāo)會(huì)自動(dòng)刪除。
三. 游標(biāo)的基本操作，定義游標(biāo)、打開(kāi)游標(biāo)、循環(huán)讀取游標(biāo)、關(guān)閉游標(biāo)和刪除游標(biāo)。
語(yǔ)法基礎(chǔ)(SQL-92)
以SQL-92方式為例
初始數(shù)據(jù)PPS_App_Infomation
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE @Parm01 varchar(100) DECLARE @Parm02 varchar(100) DECLARE @Parm03 varchar(100) DECLARE cur_Pay INSENSITIVE CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] OPEN cur_Pay FETCH cur_Pay INTO @Parm01 , @Parm02 , @Parm03 WHILE @@FETCH_STATUS = 0 BEGIN ??? WAITFOR DELAY '00:00:01' ??? PRINT @Parm01+'__'+@Parm02+'__'+@Parm03 ??? FETCH cur_Pay INTO @Parm01 , @Parm02 , @Parm03 END DEALLOCATE cur_Pay |
其中 cur_Pay為游標(biāo)名稱 INSENSITIVE 用于設(shè)置游標(biāo)是否使用副本 OPEN 打開(kāi)游標(biāo) fetch循環(huán)讀入游標(biāo) DEALLOCATE 刪除游標(biāo)
INSENSITIVE
舉個(gè)簡(jiǎn)單的例子 在游標(biāo)讀取過(guò)程中 我們將熬夜蟲(chóng)子改成早起蟲(chóng)子看看 是否添加INSENSITIVE 會(huì)是什么樣的區(qū)別
前一個(gè)有INSENSITIVE 關(guān)鍵字使用數(shù)據(jù)副本，后一個(gè)無(wú)INSENSITIVE 是即時(shí)數(shù)據(jù)
游標(biāo)的敏感性行為定義了對(duì)基行(用于建立游標(biāo))所做的更新是否對(duì)游標(biāo)可見(jiàn)。敏感性也定義了能否通過(guò)游標(biāo)進(jìn)行更新。
scroll
我們?cè)賮?lái)看看scroll關(guān)鍵字
?| 1 2 3 4 5 6 7 8 9 10 11 | DECLARE @Parm01 varchar(100) DECLARE @Parm02 varchar(100) DECLARE @Parm03 varchar(100) DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] OPEN cur_Pay BEGIN????? ??? FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ??? PRINT @Parm01+'__'+@Parm02+'__'+@Parm03 END DEALLOCATE cur_Pay |
上面的程序是ok的 成功輸出 熬夜蟲(chóng)子__Maoya__06 20 2009? 1:32PM
如果去掉SCROLL關(guān)鍵字會(huì)提示
消息 16911，級(jí)別 16，狀態(tài) 1，第 8 行 fetch: 提取類型 last 不能與只進(jìn)游標(biāo)一起使用。
SCROLL通過(guò)Transact-SQL服務(wù)器游標(biāo)檢索特定行。如果SCROLL 選項(xiàng)未在SQL-92樣式的DECLARE CURSOR語(yǔ)句中指定，則NEXT是唯一受支持的FETCH 選項(xiàng)。如果在SQL-92樣式的DECLARE CURSOR語(yǔ)句中指定了SCROLL 選項(xiàng)，則支持所有FETCH 選項(xiàng)。
FETCH 語(yǔ)法
除了last參數(shù)(返回游標(biāo)中的最后一行并將其作為當(dāng)前行)再介紹下其他的
NEXT 緊跟當(dāng)前行返回結(jié)果行，并且當(dāng)前行遞增為返回行。如果FETCH NEXT為對(duì)游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行NEXT為默認(rèn)的游標(biāo)提取選項(xiàng)。
PRIOR 返回緊鄰當(dāng)前行前面的結(jié)果行，并且當(dāng)前行遞減為返回行。如果 FETCH PRIOR 為對(duì)游標(biāo)的第一次提取操作，則沒(méi)有行返回并且游標(biāo)置于第一行之前。
FIRST 返回游標(biāo)中的第一行并將其作為當(dāng)前行。
ABSOLUTE { n | @nvar} 如果 n 或 @nvar 為正數(shù)，則返回從游標(biāo)頭開(kāi)始的第 n 行，并將返回行變成新的當(dāng)前行。如果 n 或 @nvar 為負(fù)數(shù)，則返回從游標(biāo)末尾開(kāi)始的第 n 行，并將返回行變成新的當(dāng)前行。如果 n 或 @nvar 為 0，則不返回行。n 必須是整數(shù)常量，并且 @nvar 的數(shù)據(jù)類型必須為 smallint、tinyint 或 int。
RELATIVE { n | @nvar} 如果 n 或 @nvar 為正數(shù)，則返回從當(dāng)前行開(kāi)始的第 n 行，并將返回行變成新的當(dāng)前行。如果 n 或 @nvar 為負(fù)數(shù)，則返回當(dāng)前行之前第 n 行，并將返回行變成新的當(dāng)前行。如果 n 或 @nvar 為 0，則返回當(dāng)前行。在對(duì)游標(biāo)完成第一次提取時(shí)，如果在將 n 或 @nvar 設(shè)置為負(fù)數(shù)或 0 的情況下指定 FETCH RELATIVE，則不返回行。n 必須是整數(shù)常量，@nvar 的數(shù)據(jù)類型必須為 smallint、tinyint 或 int。
定義全局游標(biāo)
FETCH NEXT FROM GLOBAL cur_Pay INTO @Parm01 , @Parm02 , @Parm03
如果未指定 GLOBAL，則指局部游標(biāo)。
READ ONLY? 與 UPDATE
(下文中只摘要不重復(fù)或者必要的代碼)
?| 1 2 3 4 5 6 7 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] ??? FOR READ ONLY ??? OPEN cur_Pay BEGIN? ??? FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 |
| 1 2 3 | ???????? UPDATE PPS_App_Infomation SET GameName ='熬夜蟲(chóng)子forupdate' WHERE CURRENT OF cur_Pay ??? PRINT @Parm01+'__'+@Parm02+'__'+@Parm03 END |
消息 16929，級(jí)別 16，狀態(tài) 1，第 9 行 游標(biāo)是只讀的。 語(yǔ)句已終止。熬夜蟲(chóng)子__Maoya__06 20 2009? 1:32PM
?| 1 2 3 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] ??? FOR UPDATE |
消息 1048，級(jí)別 15，狀態(tài) 1，第 7 行 游標(biāo)選項(xiàng) FOR UPDATE 和 INSENSITIVE 沖突。
?| 1 2 3 4 5 6 7 8 9 10 | DECLARE cur_Pay? SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] ??? FOR Update ??? OPEN cur_Pay BEGIN? ??? FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ??? UPDATE PPS_App_Infomation SET GameName ='熬夜蟲(chóng)子forupdate' WHERE CURRENT OF cur_Pay??? ??? FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ??? PRINT @Parm01+'__'+@Parm02+'__'+@Parm03 END |
(1 行受影響) 熬夜蟲(chóng)子forupdate__Maoya__06 20 2009? 1:32PM
READ ONLY不允許通過(guò)游標(biāo)進(jìn)行定位更新，并且不持有針對(duì)組成結(jié)果集的行的鎖。UPDATE與READ ONLY相對(duì)，并且UPDATE可以定義到可更新的列。
語(yǔ)法基礎(chǔ)(SQL SERVER擴(kuò)展格式)
基礎(chǔ)數(shù)據(jù)同上
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE @Parm01 varchar(100) DECLARE @Parm02 varchar(100) DECLARE @Parm03 varchar(100) DECLARE cur_Pay? CURSOR GLOBAL --和SQL-92格式同理 可選LOCAL本地游標(biāo) SCROLL --可選FORWARD_ONLY 指定游標(biāo)只能從第一行滾動(dòng)到最后一行 DYNAMIC --和上一行參數(shù)關(guān)聯(lián) static表示臨時(shí)副本 DYNAMIC直接反映在滾動(dòng)游標(biāo)時(shí)對(duì)結(jié)果集內(nèi)行所做的修改 ??????? --keyset表示除了唯一鍵其他都獲取最新值 FAST_FORWARD性能優(yōu)化的FOR_WARD READONLY游標(biāo) OPTIMISTIC --可選READ_ONLY同上 SCROLL_LOCKS定位更新并對(duì)當(dāng)前數(shù)據(jù)加鎖 OPTIMISTIC想當(dāng)與樂(lè)觀鎖可以進(jìn)行更新動(dòng)作但是如果讀取的數(shù)據(jù)被更新會(huì)導(dǎo)致操作失敗 TYPE_WARNING --如果游標(biāo)從所請(qǐng)求的類型隱形轉(zhuǎn)換到另一種類型，則給客戶端發(fā)送警告 FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] ??? FOR UPDATE OPEN cur_Pay BEGIN? ??? FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ??? PRINT @Parm01+'__'+@Parm02+'__'+@Parm03 END DEALLOCATE cur_Pay |
相關(guān)語(yǔ)法都直接注釋在code里了 和SQL-92相似的部分就不贅述了
游標(biāo)應(yīng)用
定義游標(biāo)變量cursor_variable_name
?| declare @tcur cursor set @tcur = cursor for SELECT * FROM PPS_App_Infomation |
打開(kāi)游標(biāo) OPEN {{[GLOBAL]cursor_name}|cursor_variable_name}
關(guān)閉游標(biāo) CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}
釋放游標(biāo) DEALLOCATE{{[GLOBAL]cursor_name}|cursor_variable_name}
獲取游標(biāo)行數(shù) @@CURSOR_ROWS
?| 1 2 3 4 5 6 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] OPEN cur_Pay BEGIN????? ??? PRINT @@CURSOR_ROWS END |
輸出 4
檢測(cè)fetch操作的狀態(tài)@@FETCH_STATUS 返回值0表示fetch語(yǔ)句執(zhí)行成功 -1表示fetch語(yǔ)句執(zhí)行失敗或此行不再結(jié)果集中 -2表示所要讀取的數(shù)據(jù)信息不存在
?| 1 2 3 4 5 6 7 8 9 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation] FETCH next FROM cur_Pay???? OPEN cur_Pay BEGIN????? ??? if(@@FETCH_STATUS = 0) print('FETCH 語(yǔ)句成功') ??? if(@@FETCH_STATUS = -1) print('FETCH 語(yǔ)句失敗或行不在結(jié)果集中') ??? if(@@FETCH_STATUS = -2) print('提取的行不存在') END |
消息 16917，級(jí)別 16，狀態(tài) 2，第 6 行 游標(biāo)未打開(kāi)。 FETCH 語(yǔ)句失敗或行不在結(jié)果集中
游標(biāo)嵌套
在游標(biāo)中使用另一個(gè)游標(biāo)。一般來(lái)說(shuō)使用游標(biāo)已經(jīng)很占用系統(tǒng)資源了，再嵌套游標(biāo)會(huì)大影響效率，本文只作參考用。
添加一張數(shù)據(jù)表PPS_AppConfig_Infomation
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | DECLARE @Parm01 int DECLARE @Parm02 varchar(100) DECLARE @Parm03 varchar(100) DECLARE @Parm04 varchar(100) DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT AppId,GameName,CreateUser,CreateDate FROM PPS_App_Infomation OPEN cur_Pay BEGIN? ?? FETCH next FROM cur_Pay? INTO @Parm01 , @Parm02 , @Parm03 ,@Parm04 ?? WHILE(@@FETCH_STATUS =0) ?? BEGIN ??????? PRINT('當(dāng)前游戲編號(hào)'+cast(@Parm01 as varchar(4)) +' 游戲名稱為'+@Parm02) ??????? DECLARE sub_cur CURSOR FOR ??????? SELECT WM_Type,WM_Text FROM PPS_AppConfig_Infomation WHERE AppId = @Parm01 ??????? DECLARE @Parm05 varchar(100) ??????? DECLARE @Parm06 varchar(100) ??????? OPEN sub_cur ??????? FETCH next FROM sub_cur INTO @Parm05 , @Parm06 ??????? WHILE(@@FETCH_STATUS =0) ??????? BEGIN ??????????? PRINT('當(dāng)前游戲類型'+@Parm05+'默認(rèn)軟文為'+@Parm06) ??????????? FETCH next FROM sub_cur INTO @Parm05 , @Parm06 ??????? END ??????? CLOSE sub_cur ??????? DEALLOCATE sub_cur ??????? FETCH next FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ,@Parm04 ?? END END DEALLOCATE cur_Pay |
輸出結(jié)果為
游標(biāo)關(guān)聯(lián)的系統(tǒng)存儲(chǔ)過(guò)程
sp_cursor_list 報(bào)告當(dāng)前為連接打開(kāi)的服務(wù)器游標(biāo)的屬性。
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT AppId,GameName,CreateUser,CreateDate FROM PPS_App_Infomation OPEN cur_Pay ?? DECLARE @REPORT CURSOR BEGIN? ?? FETCH next FROM cur_Pay? ?? WHILE(@@FETCH_STATUS =0) ?? BEGIN?????????? ??????????? FETCH next FROM cur_Pay ?? END ?? exec sp_cursor_list @cursor_return = @REPORT output,@cursor_scope =3? --設(shè)置1時(shí)報(bào)告所有本地游標(biāo) 設(shè)置2時(shí)報(bào)告所有全局游標(biāo) 設(shè)置3時(shí)報(bào)告所有本地游標(biāo)和全局游標(biāo) END CLOSE cur_Pay DEALLOCATE cur_Pay |
sp_describe_cursor查看游標(biāo)的全局特性 作用和sp_cursor_list差不多。
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR ??? SELECT GameName,CreateUser,CreateDate FROM PPS_App_Infomation OPEN cur_Pay ?? DECLARE @REPORT CURSOR BEGIN? ?? FETCH next FROM cur_Pay? ?? WHILE(@@FETCH_STATUS =0) ?? BEGIN?????????? ??????????? FETCH next FROM cur_Pay ?? END ?? exec sp_describe_cursor @cursor_return = @REPORT output,@cursor_source =N'globle',@cur_identity=N'cur_Pay' ???????? --@cursor_source 可選N'local'，N'globle'，N'variable' 選擇本地、全局還是變量 ???????? --@cur_identity 查看的游標(biāo)名稱 END CLOSE cur_Pay DEALLOCATE cur_Pay |
關(guān)系數(shù)據(jù)庫(kù)中的操作會(huì)對(duì)整個(gè)行集起作用。由 SELECT 語(yǔ)句返回的行集包括滿足該語(yǔ)句的 WHERE 子句中條件的所有行。這種由語(yǔ)句返回的完整行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個(gè)結(jié)果集作為一個(gè)單元來(lái)有效地處理。這些應(yīng)用程序需要一種機(jī)制以便每次處理一行或一部分行。游標(biāo)就是提供這種機(jī)制的對(duì)結(jié)果集的一種擴(kuò)展。
http://www.cnblogs.com/dubing/archive/2011/11/07/2238868.html
總結(jié)
- 上一篇： 缴纳增值税的会计分录怎么写
- 下一篇： 清明小客车高速免费 出去旅游别浪费这个机