341
技術社區[雲棲]
SQL Server遊標的使用
原文地址：https://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html
遊標是邪惡的！
在關係數據庫中，我們對於查詢的思考是麵向集合的。而遊標打破了這一規則，遊標使得我們思考方式變為逐行進行.對於類C的開發人員來著，這樣的思考方式會更加舒服。
正常麵向集合的思維方式是:
而對於遊標來說:
這也是為什麼遊標是邪惡的，它會使開發人員變懶，懶得去想用麵向集合的查詢方式實現某些功能.
同樣的，在性能上，遊標會吃更多的內存，減少可用的並發，占用寬帶，鎖定資源，當然還有更多的代碼量……
從遊標對數據庫的讀取方式來說，不難看出遊標為什麼占用更多的資源，打個比方:
當你從ATM取錢的時候，是一次取1000效率更高呢，還是取10次100？
既然遊標這麼“邪惡”，為什麼還要學習遊標
我個人認為存在既是合理.歸結來說，學習遊標原因我歸納為以下2點
1.現存係統有一些遊標，我們查詢必須通過遊標來實現
2.作為一個備用方式，當我們窮盡了while循環,子查詢，臨時表，表變量,自建函數或其他方式扔來無法實現某些查詢的時候，使用遊標實現.
T-SQL中遊標的生命周期以及實現
在T-SQL中，遊標的生命周期由5部分組成
1.定義一個遊標
在T-SQL中，定義一個遊標可以是非常簡單，也可以相對複雜，取決於遊標的參數.而遊標的參數設置取決於你對遊標原理的了解程度.
遊標其實可以理解成一個定義在特定數據集上的指針，我們可以控製這個指針遍曆數據集，或者僅僅是指向特定的行，所以遊標是定義在以Select開始的數據集上的:
T-SQL中的遊標定義在MSDN中如下:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
看起來很讓人頭痛是吧.下麵仔細講一下如何定義遊標:
遊標分為遊標類型和遊標變量，對於遊標變量來說，遵循T-SQL變量的定義方法（啥，不知道T-SQL變量定義的規則？參考我前麵的博文）.遊標變量支持兩種方式賦值，定義時賦值和先定義後賦值，定義遊標變量像定義其他局部變量一樣，在遊標前加”@”,注意，如果定義全局的遊標，隻支持定義時直接賦值，並且不能在遊標名稱前麵加“@”，兩種定義方式如下:
下麵我們來看遊標定義的參數:
LOCAL和GLOBAL二選一
LOCAL意味著遊標的生存周期隻在批處理或函數或存儲過程中可見，而GLOBAL意味著遊標對於特定連接作為上下文，全局內有效,例如:
如果不指定遊標作用域，默認作用域為GLOBAL
FORWARD_ONLY 和 SCROLL 二選一
FORWARD_ONLY意味著遊標隻能從數據集開始向數據集結束的方向讀取，FETCH NEXT是唯一的選項，而SCROLL支持遊標在定義的數據集中向任何方向，或任何位置移動，如下圖:
STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四選一
這四個關鍵字是遊標所在數據集所反應的表內數據和遊標讀取出的數據的關係
STATIC意味著，當遊標被建立時，將會創建FOR後麵的SELECT語句所包含數據集的副本存入tempdb數據庫中，任何對於底層表內數據的更改不會影響到遊標的內容.
DYNAMIC是和STATIC完全相反的選項,當底層數據庫更改時，遊標的內容也隨之得到反映，在下一次fetch中，數據內容會隨之改變
KEYSET可以理解為介於STATIC和DYNAMIC的折中方案。將遊標所在結果集的唯一能確定每一行的主鍵存入tempdb,當結果集中任何行改變或者刪除時，@@FETCH_STATUS會為-2,KEYSET無法探測新加入的數據
FAST_FORWARD可以理解成FORWARD_ONLY的優化版本.FORWARD_ONLY執行的是靜態計劃，而FAST_FORWARD是根據情況進行選擇采用動態計劃還是靜態計劃，大多數情況下FAST_FORWARD要比FORWARD_ONLY性能略好.
READ_ONLY SCROLL_LOCKS OPTIMISTIC 三選一
READ_ONLY意味著聲明的遊標隻能讀取數據,遊標不能做任何更新操作
SCROLL_LOCKS是另一種極端，將讀入遊標的所有數據進行鎖定，防止其他程序進行更改，以確保更新的絕對成功
OPTIMISTIC是相對比較好的一個選擇，OPTIMISTIC不鎖定任何數據，當需要在遊標中更新數據時,如果底層表數據更新，則遊標內數據更新不成功，如果，底層表數據未更新，則遊標內表數據可以更新
2.打開遊標
當定義完遊標後，遊標需要打開後使用，隻有簡單一行代碼:
OPEN test_Cursor
注意，當全局遊標和局部遊標變量重名時，默認會打開局部變量遊標
3.使用遊標
遊標的使用分為兩部分,一部分是操作遊標在數據集內的指向，另一部分是將遊標所指向的行的部分或全部內容進行操作
隻有支持6種移動選項,分別為到第一行（FIRST),最後一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相對於目前跳幾行(RELATIVE(n)),例如:
對於未指定SCROLL選項的遊標來說，隻支持NEXT取值.
第一步操作完成後，就通過INTO關鍵字將這行的值傳入局部變量:
比如下麵代碼:
遊標經常會和全局變量@@FETCH_STATUS與WHILE循環來共同使用,以達到遍曆遊標所在數據集的目的,例如：
4.關閉遊標
在遊標使用完之後，一定要記得關閉,隻需要一行代碼:CLOSE+遊標名稱
CLOSE test_Cursor
5.釋放遊標
當遊標不再需要被使用後，釋放遊標，隻需要一行代碼:DEALLOCATE+遊標名稱
DEALLOCATE test_Cursor
對於遊標一些優化建議
- 如果能不用遊標，盡量不要使用遊標
- 用完用完之後一定要關閉和釋放
- 盡量不要在大量數據上定義遊標
- 盡量不要使用遊標上更新數據
- 盡量不要使用insensitive, static和keyset這些參數定義遊標
- 如果可以，盡量使用FAST_FORWARD關鍵字定義遊標
- 如果隻對數據進行讀取，當讀取時隻用到FETCH NEXT選項，則最好使用FORWARD_ONLY參數
總結
本文從遊標的基本概念，到生命周期來談遊標。遊標是非常邪惡的一種存在，使用遊標經常會比使用麵向集合的方法慢2-3倍，當遊標定義在大數據量時，這個比例還會增加。如果可能，盡量使用while,子查詢，臨時表，函數，表變量等來替代遊標，記住，遊標永遠隻是你最後無奈之下的選擇，而不是首選。
遊標是邪惡的！
小注：
@@FETCH_STATUS 函數報告上一個 FETCH 語句的狀態
1 > . 0 ：表示成功取出了一行。@@FETCH_STATUS=0
2 > . - 1表示未取到數據。遊標位置超出結果集。@@FETCH_STATUS=-1
3 > . - 2表示返回的行已經不再是結果集的一個成員，這種情況隻有在遊標不是insensitive的情況下出現，即其它進程已刪除了行或改變了遊標打開的關鍵值。@@FETCH_STATUS=-2
最後更新：2017-04-03 12:56:39