SQL Server游標的使用/關閉/釋放/優化是怎樣的

這篇文章將為大家詳細講解有關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參數。

關于SQL Server游標的使用/關閉/釋放/優化是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。