Sqlserver 關於遊標

標籤：

對於sql來說查詢的思維方式的面向集合
對於遊標來說：思維方式是面向行的

效能上：遊標會吃更多記憶體，減少可見的並發，鎖定資源等

當窮盡了while迴圈，暫存資料表，表變數，自建函數，或其他方式仍然無法實現某些查詢的時候，可以考慮使用遊標

遊標的生命週期由5部分組成：

遊標可以很簡單，也可以很複雜，取決於遊標的參數

遊標可以理解為定義在資料集上的指標，可以控制這個指標遍曆資料集，或者僅僅指向特定的行，所以遊標是定義在以select開始的資料集上的

遊標的定義：

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變數的定義方法，支援兩種方式賦值，定義時賦值和先定義後賦值
如果定義局部遊標：在遊標前加 @
如果定義全域的遊標，只支援在定義的時候直接賦值，並且不能在遊標名稱前面加 @
例如：

--定義的全域遊標,遊標變數沒有@,全域遊標定義需要直接進行賦值DECLARE cur_test CURSOR FORSELECT * FROM AuthToken AS at--定義的局部遊標，需要使用@聲明變數DECLARE @cur_test2 CURSOR --先聲明變數，然後再進行賦值SET @cur_test2 = CURSOR FORSELECT * FROM AuthToken AS at

遊標的參數：
Local和global二選一

Local：意味著遊標的生存周期只在批處理或者函數或者預存程序中可見，
Global：意味著遊標對於特定串連作為上下文，全域內有效

全域遊標：在批處理後依然有效，
局部遊標：在批處理結束後被隱式釋放，無法在其他批處理中調用

--定義的全域遊標,遊標變數沒有@,全域遊標定義需要直接進行賦值DECLARE cur_test CURSOR GLOBAL FORSELECT * FROM AuthToken AS atDECLARE cur_test CURSOR LOCAL FORSELECT * FROM AuthToken AS at

如果不指定，預設為global

FORWARD_ONLY 和SCROLL二選一

forward_only:意味著遊標只能從資料集開始向資料集結束的方向讀取，Fetch next是唯一選項，
scroll支援遊標在定義的資料集中向任何方向，或者任何位置移動

例如：

--不加參數,預設為 forward_onlyDECLARE Test_Cursor CURSOR FORSELECT * FROM AuthToken AS at-- 加scroll參數,支援遊標指標向資料集的任意方向移動，DECLARE Test2_Cursor CURSOR SCROLL FORSELECT * FROM AuthToken AS at-- 加FORWARD_ONLY參數，支援遊標指標 只能從 資料集開始方向向結束方向移動，DECLARE Test3_Cursor CURSOR FORWARD_ONLY FORSELECT * FROM AuthToken AS atOPEN Test_CursorOPEN Test2_CursorOPEN Test3_Cursor--只支援從資料集開始方向向結束方向移動FETCH NEXT FROM Test_CursorFETCH NEXT FROM Test3_Cursor--SCROLL 支援向任意方向移動FETCH NEXT FROM Test2_CursorFETCH  LAST FROM Test2_Cursor

static，keyset, dynamic 和 fast_forward 四選一

這四個參數是遊標所在資料集所反應的表內資料和遊標讀取出的資料的關係

static：意味著當遊標被建立時，將會建立 for後面的 select語句所包含資料集的副本存入 tempdb資料庫中，任何對於底層表內資料的更改都不會影響到遊標的內容

dynamic：和static相反，當底層資料庫表內內容更改時，遊標的內容也隨之改變，下一次 fetch中，資料內容會隨之改變

keyset：是上面兩種的折中方案：將遊標所在結果集的唯一能確定每一行的主鍵存入tempdb，當結果集中任何行改變或者刪除時，@@Fetch_status 會為 -2，keyset無法探測新加入的資料

fast_forward:是forward_only的最佳化版本，forward_only執行的是 靜態計劃，
而Fast_forward是根據情況進行選擇採用動態計劃還是靜態計劃，

Read_only ,Scroll_locks,Optimistic三選一
Read_only：意味著聲明的遊標只能讀取資料，遊標不能做任何更新操作
scroll_locks:將讀入遊標的所有資料進行鎖定，防止其他程式變更，以確保更新的絕對成功

Optimistic：不鎖定任何資料，當需要在遊標中更新資料時，如果底層表資料更新，則遊標內資料更新不成功，如果底層表資料未更新，則遊標內表資料可以更新

 

開啟遊標：

當遊標定義完，需要開啟後才能使用
Open test_cursor

注意：當全域遊標和局部遊標變數重名時，預設會開啟局部變數遊標

3 使用遊標：

遊標的使用分為兩部分：
一部分是操作遊標在資料集內的指向，
一部分是將遊標所指向的行的部分或全部內容進行操作

支援6種移動選項
到第一行：first
最後一行：last
下一行：next
上一行：prior
直接跳到某行：absolute(n)
相對於目前跳幾行(relative(n))

對於未指定scroll選項的遊標來說，只支援next取值

 例如：

DEALLOCATE test_cursor--定義一個全域遊標，並且支援向任意方向移動DECLARE test_cursor CURSOR SCROLL FORSELECT c.Nickname FROM dbo.Customer AS c--定義好之後需要首先開啟遊標OPEN test_cursorDECLARE @a NVARCHAR(50)--使用遊標：取第一行資料到@aFETCH FIRST FROM test_cursor INTO @aPRINT @a--取當前位置的第n行資料(取絕對位置)FETCH ABSOLUTE 3 FROM test_cursor INTO @aPRINT @a--取相對位置FETCH RELATIVE 3 FROM test_cursor INTO @aPRINT @a--取當前位置的下一行資料FETCH NEXT FROM test_cursor INTO @aPRINT @a--取最後一條資料FETCH LAST FROM        test_cursor INTO @aPRINT @a--取遊標的當前位置的上一行資料FETCH PRIOR FROM test_cursor INTO @aPRINT @a--遊標使用完之後需要關閉遊標CLOSE test_cursor--如果不再需要遊標，可以進行刪除DEALLOCATE test_cursor

遊標經常會和全域變數 @@Fetch_status 與while迴圈來共同使用，以便達到遍曆遊標所在資料集的目的

例如：

 

DECLARE Test_Cursor CURSOR SCROLL FORSELECT c.Id,c.Nickname FROM Customer AS cOPEN Test_CursorDECLARE @i INTDECLARE @name NVARCHAR(20)WHILE @@FETCH_status = 0BEGIN    PRINT @i    PRINT @name    FETCH NEXT FROM Test_Cursor INTO @i,@nameENDCLOSE Test_CursorDEALLOCATE Test_Cursor

使用遊標註意點：

1 遊標能不用就盡量不要用遊標
2 用完之後一定要關閉和釋放
3 盡量不要在大量資料上定義遊標
4盡量不要使用遊標上更新資料
5 盡量不要使用insensitive，static，keyset這些參數定義遊標
6 如果可以，盡量使用fast_forward關鍵字定義遊標
7如果只對資料進行讀取，當讀取只用到Fetch next選項，則最好使用 forward_only參數

Sqlserver 關於遊標