【SQL】- 基礎知識梳理（六） - 遊標

標籤：服務   let   style   where   statement   聲明   nbsp   pre   取數   

遊標的概念

結果集，結果集就是select查詢之後返回的所有行資料的集合。

遊標（Cursor）：

• 是處理資料的一種方法。
• 它可以定位到結果集中的某一行，對資料進行讀寫。
• 也可以移動遊標定位到你需要的行中進行資料操作。
• 是面向集合的資料庫管理系統和面向行的程式設計之間的橋樑

遊標的分類

SQL Server支援的API伺服器資料指標分為4種：
靜態資料指標（ 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效能略好。

遊標的文法

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 ] ] ]

cursor_name：遊標名稱。
Local：範圍為局部，只在定義它的批處理，預存程序或觸發器中有效。
Global：範圍為全域，由串連執行的任何預存程序或批處理中，都可以引用該遊標。
[Local | Global]:預設為local。
Forward_Only:指定遊標智能從第一行滾到最後一行。 Fetch Next是唯一支援的提取選項。如果在指定Forward_Only時不指定Static、KeySet、Dynamic關鍵字，預設為Dynamic遊標。如果Forward_Only和Scroll沒有指定，Static、KeySet、Dynamic遊標預設為Scroll，Fast_Forward預設為Forward_Only
Static:靜態資料指標
KeySet：鍵集遊標
Dynamic：動態資料指標，不支援Absolute提取選項
Fast_Forward：指定啟用了效能最佳化的Forward_Only、Read_Only遊標。
Read_Only:意味著聲明的遊標只能讀取資料,遊標不能做任何更新操作
Scroll_Locks：將讀入遊標的所有資料進行鎖定，防止其他程式變更，以確保更新的絕對成功
Optimistic：是相對比較好的一個選擇，OPTIMISTIC不鎖定任何資料，當需要在遊標中更新資料時,如果底層表資料更新，則遊標內資料更新不成功，如果，底層表資料未更新，則遊標內表資料可以更新。
Type_Warning:指定將遊標從所請求的類型隱式轉換為另一種類型時向用戶端發送警告資訊。
For Update[of column_name ,....] :定義遊標中可更新的列。

如何定義遊標

遊標變數支援兩種方式賦值，定義時賦值和先定義後賦值，定義遊標變數像定義其他局部變數一樣，在遊標前加”@”,注意，如果定義全域的遊標，只支援定義時直接賦值，並且不能在遊標名稱前面加“@”，兩種定義方式如下:
--定義後直接賦值
DECLARE test_Cursor CURSOR FOR
SELECT * FROM TABLE1
--先定義後賦值
DECLARE @test_Cursor2 CURSOR
SET @test_Cursor2=CURSOR FOR
SELECT * FROM TABLE2

--定義後直接賦值
DECLARE test_Cursor CURSOR LOCAL FOR
SELECT * FROM TABLE1
DECLARE test_Cursor2 CURSOR GLOBAL FOR
SELECT * FROM TABLE2
--用GO結束上面範圍
GO
--開啟遊標
OPEN test_Cursor
OPEN test_Cursor2
全域遊標在批處理結束後依然有效
局部遊標在批處理結束後被隱式釋放，無法再其他批處理中引用
  如果不指定遊標範圍，預設範圍為GLOBAL
 注意，當全域遊標和局部遊標變數重名時，預設會開啟局部變數遊標

提取遊標文法 

Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from
[Global] cursor_name
into @variable_name[,....]]
參數說明：
Frist：結果集的第一行
Prior：當前位置的上一行
Next：當前位置的下一行
Last：最後一行
Absoute n：從遊標的第一行開始數，第n行。
Relative n：從相對當前位置數，第n行。（n為負數向前數，否則向後）
Into @variable_name[,...] : 將提取到的資料存放到變數variable_name中。
注意：    對於未指定SCROLL選項的遊標來說，只支援NEXT取值.

實戰建立遊標

準備表資料

建立遊標

--聲明遊標declare test_Cursortable3 CURSOR FORSELECT id,NAME FROM TABLE3--開啟遊標OPEN test_Cursortable3--聲明遊標提取變數所要存放的變數declare @id int,@name varchar(20)--定位遊標到哪一行fetch next from test_Cursortable3 into @id,@name    --into的變數數量必須需與遊標查詢結果的列數相同--fetch FIRST from test_Cursortable3 into @id,@namewhile @@FETCH_STATUS=0  --提取成功，進行下一條資料的提取操作begin    if @id=2    begin    Update TABLE3 Set sex=‘0‘ Where Current of test_Cursortable3 --更新當前行    end    if  @id=10    begin    delete TABLE3 where current  of test_Cursortable3    --刪除當前行    endfetch next from test_Cursortable3 into @id,@name    --移動遊標end--關閉遊標close    test_Cursortable3--釋放遊標deallocate test_Cursortable3

執行結果

注釋：

@@fetch_status是MicroSoft SQL SERVER的一個全域變數
其值有以下三種,分別表示三種不同含義[傳回型別integer]
0 FETCH 語句成功
-1 FETCH 語句失敗或此行不在結果集中
-2 被提取的行不存在

 

使用遊標時注意事項：

遊標開啟後，伺服器會專門為遊標分配一定的記憶體空間存放遊標操作的資料結果集，同時使用遊標也會對某些資料進行封鎖。所以遊標一旦用過，應及時關閉，避免伺服器資源浪費。

盡量不要在大量資料上定義遊標
盡量不要使用遊標上更新資料
盡量不要使用insensitive, static和keyset這些參數定義遊標
如果可以，盡量使用FAST_FORWARD關鍵字定義遊標
如果只對資料進行讀取，當讀取時只用到FETCH NEXT選項，則最好使用FORWARD_ONLY參數

如果能不用遊標，盡量不要使用遊標

【SQL】- 基礎知識梳理（六） - 遊標