色香欲天天影视综合网,人妻日韩精品中文字幕,香蕉EEWW99国产精选免费

走向DBA[MSSQL篇] 詳解游標

2011-11-07 10:05 熬夜的蟲子閱讀(2449) 評論(0) 收藏舉報

前篇回顧：上一篇蟲子介紹了一些不常用的數據過濾方式，本篇詳細介紹下游標。

概念

簡單點說游標的作用就是存儲一個結果集，并根據語法將這個結果集的數據逐條處理。

觀點

正因為游標可以將結果集一條條取出處理，所以會增加服務器的負擔。再者使用游標的效率遠遠沒有使用默認的結果集效率高，在默認結果集中，從客戶端發送到服務器的唯一一個數據包是包含需執行語句的數據包。而在使用服務器游標時，每一個FETCH語句都必須從客戶端發送到服務器，然后在服務器中將它解析并編譯為執行計劃。除非要再sqlserver上進行很復雜的數據操作。

基本知識

一. SQL Server 2005 支持兩種請求游標的方法

　　1.Transact-SQL （支持 SQL-92）；

　　2.數據庫應用程序編程接口（API）游標函數（ADO、OLE DB、ODBC）應用程序不能混合使用這兩種請求游標的方法。ODBC 還支持客戶端游標，即在客戶　　端實現的游標。

二. 游標根據范圍可以分成全局游標和局部游標。全局游標可以在整個會話過程中使用，局部游標只能在一個T-SQL批、存儲過程或觸發器中執行，當執行完畢后局部游標會自動刪除。

三. 游標的基本操作，定義游標、打開游標、循環讀取游標、關閉游標和刪除游標。

語法基礎（SQL-92）

以SQL-92方式為例

初始數據PPS_App_Infomation

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為游標名稱 INSENSITIVE 用于設置游標是否使用副本 OPEN 打開游標 fetch循環讀入游標 DEALLOCATE 刪除游標

INSENSITIVE

舉個簡單的例子在游標讀取過程中我們將熬夜蟲子改成早起蟲子看看是否添加INSENSITIVE 會是什么樣的區別

前一個有INSENSITIVE 關鍵字使用數據副本，后一個無INSENSITIVE 是即時數據

游標的敏感性行為定義了對基行（用于建立游標）所做的更新是否對游標可見。敏感性也定義了能否通過游標進行更新。

scroll

我們再來看看scroll關鍵字

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的成功輸出熬夜蟲子__Maoya__06 20 2009 1:32PM

如果去掉SCROLL關鍵字會提示

消息 16911，級別 16，狀態 1，第 8 行
fetch: 提取類型 last 不能與只進游標一起使用。

SCROLL通過Transact-SQL服務器游標檢索特定行。如果SCROLL 選項未在SQL-92樣式的DECLARE CURSOR語句中指定，則NEXT是唯一受支持的FETCH 選項。如果在SQL-92樣式的DECLARE CURSOR語句中指定了SCROLL 選項，則支持所有FETCH 選項。

FETCH 語法

除了last參數（返回游標中的最后一行并將其作為當前行）再介紹下其他的

NEXT 緊跟當前行返回結果行，并且當前行遞增為返回行。如果FETCH NEXT為對游標的第一次提取操作，則返回結果集中的第一行NEXT為默認的游標提取選項。

PRIOR 返回緊鄰當前行前面的結果行，并且當前行遞減為返回行。如果 FETCH PRIOR 為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前。

FIRST 返回游標中的第一行并將其作為當前行。

ABSOLUTE { n | @nvar}
如果 n 或 @nvar 為正數，則返回從游標頭開始的第 n 行，并將返回行變成新的當前行。如果 n 或 @nvar 為負數，則返回從游標末尾開始的第 n 行，并將返回行變成新的當前行。如果 n 或 @nvar 為 0，則不返回行。n 必須是整數常量，并且 @nvar 的數據類型必須為 smallint、tinyint 或 int。

RELATIVE { n | @nvar}
如果 n 或 @nvar 為正數，則返回從當前行開始的第 n 行，并將返回行變成新的當前行。如果 n 或 @nvar 為負數，則返回當前行之前第 n 行，并將返回行變成新的當前行。如果 n 或 @nvar 為 0，則返回當前行。在對游標完成第一次提取時，如果在將 n 或 @nvar 設置為負數或 0 的情況下指定 FETCH RELATIVE，則不返回行。n 必須是整數常量，@nvar 的數據類型必須為 smallint、tinyint 或 int。

定義全局游標

FETCH NEXT FROM GLOBAL cur_Pay INTO @Parm01 , @Parm02 , @Parm03

如果未指定 GLOBAL，則指局部游標。

READ ONLY 與 UPDATE

（下文中只摘要不重復或者必要的代碼）

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

         UPDATE PPS_App_Infomation SET GameName ='熬夜蟲子forupdate' WHERE CURRENT OF cur_Pay
	PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END

消息 16929，級別 16，狀態 1，第 9 行
游標是只讀的。
語句已終止。熬夜蟲子__Maoya__06 20 2009 1:32PM

DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
	SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
    FOR UPDATE

消息 1048，級別 15，狀態 1，第 7 行
游標選項 FOR UPDATE 和 INSENSITIVE 沖突。

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 ='熬夜蟲子forupdate' WHERE CURRENT OF cur_Pay	
	FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 
	PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END

(1 行受影響)
熬夜蟲子forupdate__Maoya__06 20 2009 1:32PM

READ ONLY不允許通過游標進行定位更新，并且不持有針對組成結果集的行的鎖。UPDATE與READ ONLY相對，并且UPDATE可以定義到可更新的列。

語法基礎（SQL SERVER擴展格式）

基礎數據同上

DECLARE @Parm01 varchar(100)
DECLARE @Parm02 varchar(100)
DECLARE @Parm03 varchar(100)
DECLARE cur_Pay  CURSOR 
GLOBAL --和SQL-92格式同理 可選LOCAL本地游標
SCROLL --可選FORWARD_ONLY 指定游標只能從第一行滾動到最后一行
DYNAMIC --和上一行參數關聯 static表示臨時副本 DYNAMIC直接反映在滾動游標時對結果集內行所做的修改 
        --keyset表示除了唯一鍵其他都獲取最新值 FAST_FORWARD性能優化的FOR_WARD READONLY游標
OPTIMISTIC --可選READ_ONLY同上 SCROLL_LOCKS定位更新并對當前數據加鎖 OPTIMISTIC想當與樂觀鎖可以進行更新動作但是如果讀取的數據被更新會導致操作失敗
TYPE_WARNING --如果游標從所請求的類型隱形轉換到另一種類型，則給客戶端發送警告
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

相關語法都直接注釋在code里了和SQL-92相似的部分就不贅述了

游標應用

定義游標變量cursor_variable_name

declare @tcur cursor
set @tcur = cursor for SELECT * FROM PPS_App_Infomation

打開游標 OPEN {{[GLOBAL]cursor_name}|cursor_variable_name}

關閉游標 CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}

釋放游標 DEALLOCATE{{[GLOBAL]cursor_name}|cursor_variable_name}

獲取游標行數 @@CURSOR_ROWS

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

檢測fetch操作的狀態@@FETCH_STATUS 返回值0表示fetch語句執行成功 -1表示fetch語句執行失敗或此行不再結果集中 -2表示所要讀取的數據信息不存在

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 語句成功')
    if(@@FETCH_STATUS = -1) print('FETCH 語句失敗或行不在結果集中')
	if(@@FETCH_STATUS = -2) print('提取的行不存在')
END

消息 16917，級別 16，狀態 2，第 6 行
游標未打開。
FETCH 語句失敗或行不在結果集中

游標嵌套

在游標中使用另一個游標。一般來說使用游標已經很占用系統資源了，再嵌套游標會大影響效率，本文只作參考用。

添加一張數據表PPS_AppConfig_Infomation

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('當前游戲編號'+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('當前游戲類型'+@Parm05+'默認軟文為'+@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

輸出結果為

游標關聯的系統存儲過程

sp_cursor_list 報告當前為連接打開的服務器游標的屬性。

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  --設置1時報告所有本地游標 設置2時報告所有全局游標 設置3時報告所有本地游標和全局游標
END
CLOSE cur_Pay
DEALLOCATE cur_Pay

sp_describe_cursor查看游標的全局特性作用和sp_cursor_list差不多。

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 查看的游標名稱
END
CLOSE cur_Pay
DEALLOCATE cur_Pay

關系數據庫中的操作會對整個行集起作用。由 SELECT 語句返回的行集包括滿足該語句的 WHERE 子句中條件的所有行。這種由語句返回的完整行集稱為結果集。應用程序，特別是交互式聯機應用程序，并不總能將整個結果集作為一個單元來有效地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標就是提供這種機制的對結果集的一種擴展。

刷新頁面返回頂部

熬夜的蟲子

走向DBA[MSSQL篇] 詳解游標

About