走向DBA[MSSQL篇] 詳解遊标

前篇回顧：上一篇蟲子介紹了一些不常用的資料過濾方式，本篇詳細介紹下遊标。

概念

簡單點說遊标的作用就是存儲一個結果集，并根據文法将這個結果集的資料逐條處理。

觀點

正因為遊标可以将結果集一條條取出處理，是以會增加伺服器的負擔。再者使用遊标的效率遠遠沒有使用預設的結果集效率高，在預設結果集中，從用戶端發送到伺服器的唯一一個資料包是包含需執行語句的資料包。而在使用伺服器遊标時，每一個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

<code>DECLARE</code> <code>@Parm01 </code><code>varchar</code><code>(100)</code>

<code>DECLARE</code> <code>@Parm02 </code><code>varchar</code><code>(100)</code>

<code>DECLARE</code> <code>@Parm03 </code><code>varchar</code><code>(100)</code>

<code>DECLARE</code> <code>cur_Pay </code><code>INSENSITIVE</code> <code>CURSOR</code> <code>FOR</code>

<code> </code><code>SELECT</code> <code>GameName,CreateUser,CreateDate </code><code>FROM</code> <code>[PicPromotion].[dbo].[PPS_App_Infomation]</code>

<code>FETCH</code> <code>cur_Pay </code><code>INTO</code> <code>@Parm01 , @Parm02 , @Parm03</code>

<code>WHILE @@FETCH_STATUS = 0</code>

<code>BEGIN</code>

<code> </code><code>WAITFOR DELAY </code><code>'00:00:01'</code>

<code> </code><code>PRINT @Parm01+</code><code>'__'</code><code>+@Parm02+</code><code>'__'</code><code>+@Parm03</code>

<code> </code><code>FETCH</code> <code>cur_Pay </code><code>INTO</code> <code>@Parm01 , @Parm02 , @Parm03</code>

<code>DEALLOCATE</code> <code>cur_Pay</code>

其中 cur_Pay為遊标名稱 INSENSITIVE 用于設定遊标是否使用副本 OPEN 打開遊标 fetch循環讀入遊标 DEALLOCATE 删除遊标

INSENSITIVE

舉個簡單的例子在遊标讀取過程中我們将熬夜蟲子改成早起蟲子看看是否添加INSENSITIVE 會是什麼樣的差別

前一個有INSENSITIVE 關鍵字使用資料副本，後一個無INSENSITIVE 是即時資料

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

scroll

我們再來看看scroll關鍵字

<code>DECLARE</code> <code>cur_Pay </code><code>INSENSITIVE</code> <code>SCROLL</code> <code>CURSOR</code> <code>FOR</code>

<code>BEGIN</code>

<code> </code><code>FETCH</code> <code>LAST</code> <code>FROM</code> <code>cur_Pay </code><code>INTO</code> <code>@Parm01 , @Parm02 , @Parm03</code>

上面的程式是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

（下文中隻摘要不重複或者必要的代碼）

<code>BEGIN</code>

<code> </code><code>UPDATE</code> <code>PPS_App_Infomation </code><code>SET</code> <code>GameName =</code><code>'熬夜蟲子forupdate'</code> <code>WHERE</code> <code>CURRENT</code> <code>OF</code> <code>cur_Pay</code>

消息 16929，級别 16，狀态 1，第 9 行

遊标是隻讀的。

語句已終止。熬夜蟲子__Maoya__06 20 2009 1:32PM

<code> </code><code>FOR</code> <code>UPDATE</code>

消息 1048，級别 15，狀态 1，第 7 行

遊标選項 FOR UPDATE 和 INSENSITIVE 沖突。

<code>DECLARE</code> <code>cur_Pay </code><code>SCROLL</code> <code>CURSOR</code> <code>FOR</code>

<code> </code><code>FOR</code> <code>Update</code>

(1 行受影響)

熬夜蟲子forupdate__Maoya__06 20 2009 1:32PM

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

文法基礎（SQL SERVER擴充格式）

基礎資料同上

<code>DECLARE</code> <code>cur_Pay </code><code>CURSOR</code>

<code>GLOBAL</code> <code>--和SQL-92格式同理可選LOCAL本地遊标</code>

<code>SCROLL</code> <code>--可選FORWARD_ONLY 指定遊标隻能從第一行滾動到最後一行</code>

<code>DYNAMIC</code> <code>--和上一行參數關聯 static表示臨時副本 DYNAMIC直接反映在滾動遊标時對結果集内行所做的修改</code>

<code> </code><code>--keyset表示除了唯一鍵其他都擷取最新值 FAST_FORWARD性能優化的FOR_WARD READONLY遊标</code>

<code>OPTIMISTIC </code><code>--可選READ_ONLY同上 SCROLL_LOCKS定位更新并對目前資料加鎖 OPTIMISTIC想當與樂觀鎖可以進行更新動作但是如果讀取的資料被更新會導緻操作失敗</code>

<code>TYPE_WARNING </code><code>--如果遊标從所請求的類型隐形轉換到另一種類型，則給用戶端發送警告</code>

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

遊标應用

定義遊标變量cursor_variable_name

<code>declare</code> <code>@tcur </code><code>cursor</code>

<code>set</code> <code>@tcur = </code><code>cursor</code> <code>for</code> <code>SELECT</code> <code>* </code><code>FROM</code> <code>PPS_App_Infomation</code>

打開遊标 OPEN {{[GLOBAL]cursor_name}|cursor_variable_name}

關閉遊标 CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}

釋放遊标 DEALLOCATE{{[GLOBAL]cursor_name}|cursor_variable_name}

擷取遊标行數 @@CURSOR_ROWS

<code> </code><code>PRINT @@CURSOR_ROWS</code>

輸出 4

檢測fetch操作的狀态@@FETCH_STATUS 傳回值0表示fetch語句執行成功 -1表示fetch語句執行失敗或此行不再結果集中 -2表示所要讀取的資料資訊不存在

<code>FETCH</code> <code>next</code> <code>FROM</code> <code>cur_Pay </code>

<code> </code><code>if(@@FETCH_STATUS = 0) print(</code><code>'FETCH 語句成功'</code><code>)</code>

<code> </code><code>if(@@FETCH_STATUS = -1) print(</code><code>'FETCH 語句失敗或行不在結果集中'</code><code>)</code>

<code> </code><code>if(@@FETCH_STATUS = -2) print(</code><code>'提取的行不存在'</code><code>)</code>

消息 16917，級别 16，狀态 2，第 6 行

遊标未打開。

FETCH 語句失敗或行不在結果集中

遊标嵌套

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

添加一張資料表PPS_AppConfig_Infomation

<code>DECLARE</code> <code>@Parm01 </code><code>int</code>

<code>DECLARE</code> <code>@Parm04 </code><code>varchar</code><code>(100)</code>

<code> </code><code>SELECT</code> <code>AppId,GameName,CreateUser,CreateDate </code><code>FROM</code> <code>PPS_App_Infomation</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>cur_Pay </code><code>INTO</code> <code>@Parm01 , @Parm02 , @Parm03 ,@Parm04</code>

<code> </code><code>WHILE(@@FETCH_STATUS =0)</code>

<code> </code><code>BEGIN</code>

<code> </code><code>PRINT(</code><code>'目前遊戲編号'</code><code>+</code><code>cast</code><code>(@Parm01 </code><code>as</code> <code>varchar</code><code>(4)) +</code><code>' 遊戲名稱為'</code><code>+@Parm02)</code>

<code> </code><code>DECLARE</code> <code>sub_cur </code><code>CURSOR</code> <code>FOR</code>

<code> </code><code>SELECT</code> <code>WM_Type,WM_Text </code><code>FROM</code> <code>PPS_AppConfig_Infomation </code><code>WHERE</code> <code>AppId = @Parm01</code>

<code> </code><code>DECLARE</code> <code>@Parm05 </code><code>varchar</code><code>(100)</code>

<code> </code><code>DECLARE</code> <code>@Parm06 </code><code>varchar</code><code>(100)</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>sub_cur </code><code>INTO</code> <code>@Parm05 , @Parm06</code>

<code> </code><code>WHILE(@@FETCH_STATUS =0)</code>

<code> </code><code>BEGIN</code>

<code> </code><code>PRINT(</code><code>'目前遊戲類型'</code><code>+@Parm05+</code><code>'預設軟文為'</code><code>+@Parm06)</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>sub_cur </code><code>INTO</code> <code>@Parm05 , @Parm06</code>

<code> </code><code>CLOSE</code> <code>sub_cur</code>

<code> </code><code>DEALLOCATE</code> <code>sub_cur</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>cur_Pay </code><code>INTO</code> <code>@Parm01 , @Parm02 , @Parm03 ,@Parm04</code>

輸出結果為

遊标關聯的系統存儲過程

sp_cursor_list 報告目前為連接配接打開的伺服器遊标的屬性。

<code> </code><code>DECLARE</code> <code>@REPORT </code><code>CURSOR</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>cur_Pay </code>

<code> </code><code>BEGIN</code>

<code> </code><code>FETCH</code> <code>next</code> <code>FROM</code> <code>cur_Pay</code>

<code> </code><code>exec</code> <code>sp_cursor_list @cursor_return = @REPORT </code><code>output</code><code>,@cursor_scope =3 </code><code>--設定1時報告所有本地遊标設定2時報告所有全局遊标設定3時報告所有本地遊标和全局遊标</code>

<code>CLOSE</code> <code>cur_Pay</code>

sp_describe_cursor檢視遊标的全局特性作用和sp_cursor_list差不多。

<code> </code><code>SELECT</code> <code>GameName,CreateUser,CreateDate </code><code>FROM</code> <code>PPS_App_Infomation</code>

<code> </code><code>exec</code> <code>sp_describe_cursor @cursor_return = @REPORT </code><code>output</code><code>,@cursor_source =N</code><code>'globle'</code><code>,@cur_identity=N</code><code>'cur_Pay'</code>

<code> </code><code>--@cursor_source 可選N'local'，N'globle'，N'variable' 選擇本地、全局還是變量</code>

<code> </code><code>--@cur_identity 檢視的遊标名稱</code>

關系資料庫中的操作會對整個行集起作用。由 SELECT 語句傳回的行集包括滿足該語句的 WHERE 子句中條件的所有行。這種由語句傳回的完整行集稱為結果集。應用程式，特别是互動式聯機應用程式，并不總能将整個結果集作為一個單元來有效地處理。這些應用程式需要一種機制以便每次處理一行或一部分行。遊标就是提供這種機制的對結果集的一種擴充。

本文轉自熬夜的蟲子 51CTO部落格，原文連結:http://blog.51cto.com/dubing/712443

走向DBA[MSSQL篇] 詳解遊标

繼續閱讀

資料庫設計理論及應用（4）——概念結構設計1．概念模型 2．銷售子系統的分E-R圖 3．視圖的內建 4．設計基本E-R圖

資料流圖的設計

資料庫規範化設計理論摘要要

黑馬程式員——C#結構及常用基本類型

試分析如何把數組array中的所有元素循環右移p位

Flash AS3 連續加載外部若幹圖檔

DB2表壓縮功能

Java Web項目開發需要掌握的技術

華為筆試軟體

項目管理那些事兒

OS --written test1

OS-written test2

壓縮編碼M-JPEG、MPEG4、H.264

轉詳解C#資料庫存取圖檔三大方式

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名