SQL Server遊標的使用/關閉/釋放/最佳化小結

遊標是邪惡的！

在關聯式資料庫中，我們對於查詢的思考是面向集合的。而遊標打破了這一規則，遊標使得我們思考方式變為逐行進行.對於類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,子查詢，暫存資料表，函數，表變數等來替代遊標，記住，遊標永遠只是你最後無奈之下的選擇，而不是首選。