SQL Server 中遊標的使用

標籤：

SQL Server 中遊標的使用

1.遊標是行讀取，佔用資源比sql多
2.遊標的使用情景：
  ->現存的系統中使用的是遊標，查詢必須通過遊標來實現
  ->用盡了while、子查詢暫存資料表、表變數、自訂函數以及其他方式仍然無法實現的時候，使用遊標
3.T-SQL 中遊標的生命週期由5部分組成
   ->定義遊標：遊標的定義遵循T-Sql的定義方法，賦值有兩種方法，定義時賦值，和先定義後賦值，定義遊標像定義其他局部變數一樣前面要加@，注意如果是全域的遊標，只支援定義時直接賦值，並且不能在遊標前面加@
   --定義時直接賦值
   Declare test_Cursor Cursor For
   select * from dbo.tb1
   
   --先定義後賦值
   Declare @test_Cursor2 Cursor
   set @test_Cursor2=Cursor For
   select * from dbo.tb2
   
   Local和Global
     Local意味著遊標的生命週期只在批處理或函數或預存程序中可見，而Global意味著遊標對於特定的串連作為上下文，全域有效
     --定義後直接賦值(全域)
     Declare test_Cursor Cursor GLOBAL For
     select * from dbo.tb1
     --(局部)
     Declare test_Cursor2 Cursor LOCAL For
     select * from dbo.tb2
     --用GO結束上面的範圍
     GO
     Open test_Cursor
     Open test_Cursor2
     
     報錯：test_Cursor2不存在
     
     注意：如果不指定遊標範圍，預設為GLOBAL
   Forward_Only和Scroll 二選一
     Forward_Only意味著遊標只能從資料集開始向著資料集結束的方向讀取，唯一選項Fetch Next,而Scroll支援遊標在定義資料集上向著任何方向或者任何位置移動
     --不加參數預設為Forward_only
     Declare test_Cursor Cursor For
     select * from dbo.tb1
     --加Forward_Only
     Declare test_Cursor2 Cursor Forward_Only For
     select * from dbo.tb1
     --加Scroll
     Declare test_Cursor3 Cursor Scroll For
     select * from dbo.tb1
     
     Open test_Cursor
     Open test_Cursor2
     Open test_Cursor3
     Fetch Last from test_Cursor
     Fetch Last from test_Cursor2
     Fetch Last from test_Cursor3--讀取最後一行
     
     報錯：順向資料指標test_Cursor不能與last一起使用，順向資料指標test_Cursor2不能與last一起使用
   Static Keyset Dynamic 和 Fast_Forward四選一
     這四個關鍵字是遊標所在的資料集所反應的表內資料和遊表讀取出資料的關係。
     Static：意味著，當遊標被建立時候，將會建立For後面的select 語句所包含資料集的副本存入tempdb資料庫中，任何對於底層表內資料的更改不會影響到遊標的內容
     Dynamic：是和Static完全相反的選項，當底層資料庫更改時，遊標的內容也隨之待到放映，在下一次fetch中，資料內容會隨之改變
     Keyset：可以理解為介於Static和Dynamic的折中方案。蔣友柏所在結果集的唯一能確定每一行的主鍵存入tempdb，當結果中任何行改變或者刪除時，@@FETCH_STATUS為-2，KEYSET無法探測新加入的資料
     FAST_FORWAED可以理解成FORWARD_ONLY的最佳化版本，FORWARD_ONLY執行的是經計劃，而FAST_FORWARD是根據情況進行選擇採用動態計劃還是靜態計劃，大多數情況下FAST_FORWARD要比FORWARD_ONLY效能略好
   READ_ONLY SCROLL_LOCKS OPTIMISTIC三選一
     READ_ONLY：意味著聲明的遊標只能讀取資料，遊標不能做任何更新操作
     SCROLL_LOCKS：是另一種極端，將讀入遊標的所有資料進行鎖定，防止其他程式變更，以確保更行的絕對成功
     OPTIMISTIC：是相對比較好的一個選擇，OPTIMISTIC不鎖定任何資料，當需要在遊標中更新資料時，如果底層資料更新，測遊標內資料更新不成功，如果底層表資料為更新，則遊標南日表資料可以更新
   ->開啟遊標：Open test_Cursor 注意，當全域遊標和局部遊標變數重名時，預設會開啟局部變數遊標
   ->使用遊標：遊標分為兩部分，一部分是操作遊標在資料集內的指向，另一部分是將遊標所直向的行的部分或全部內容進行操作
   只支援6中移動選項，到第一行(FIRST)，最後一行(LAST)，下一行(NEXT)，上一行(PRIOR)，直接跳到某一行(ABSOLUTE(n))，相對於目前跳到幾行(RELATIVE(n))
     --必須指定SCROLL否則只支援next只進選項
     DECLARE test_Cursor SCROLL FOR
     select * from dbo.tb1
     
     OPEN test_Cursor
     DECLARE @c nvarchar(10)
     --取下一行
     Fetch next from test_Cursor into @c
     print @c
     --取最後一行
     Fetch Last From test_Cursor into @c
     print @c
     --取第一行
     Fetch first from test_Cursor into @c
     print @c
     --取上一行
     fetch prior from test_Cursor into @c
     print @c
     --取第三行
     fetch absolute 3 from test_Cursor into @c
     print @c
     --取相對目前來說上一行（-2相對於目前來說向上移動2行，2對目前來說向下移動2行）
     fetch relative -1 from test_Cursor into @c
     print @c
   對於為指定SCROLL的，只能用NEXT選項
   遊標經常回和全域變數@@FETCH_STATUS 與WHITLE迴圈來共同使用，從來遍曆遊標所在的資料集
     Declare test_Cursor cursor scroll for
     select id,name from dbo.tb1
     Open test_Cursor
     Declare @id int
     Declare @name nvarchar(10)
     
     while @@FETCH_STATUS=0
     begin
     print @id
     print @name
     Fetch next from test_Cursor into @id,@name
     end
     
     Close test_Cursor
     Deallocate test_Cursor
   ->關閉遊標： Close test_Cursor
   ->釋放遊標： Deallocate test_Cursor
  
   注釋1：
  ->遊標的定義的複雜程度是和參數有關，而遊標的參數設定是對遊標的原理瞭解程度。
  ->遊標的原理：遊標是定義在特定資料集上的指標，我們控制這個指標遍曆資料集，或者僅僅是指向特定的行，所以遊標是定義在以select開始的資料集上的。
  
  
 注釋2：
  如果能不用遊標，盡量不要使用遊標
     用完用完之後一定要關閉和釋放
     盡量不要在大量資料上定義遊標
     盡量不要使用遊標上更新資料
     盡量不要使用insensitive, static和keyset這些參數定義遊標
     如果可以，盡量使用FAST_FORWARD關鍵字定義遊標
     如果只對資料進行讀取，當讀取時只用到FETCH NEXT選項，則最好使用FORWARD_ONLY參數

SQL Server 中遊標的使用