sqlserver 遊標

標籤：

遊標分為遊標類型和遊標變數，對於遊標變數來說，遵循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,子查詢，暫存資料表，函數，表變數等來替代遊標，記住，遊標永遠只是你最後無奈之下的選擇，而不是首選。

sqlserver 遊標