SQL Server中的遊標介紹

2022-05-20 13:13:17

遊標是面向行的，它會使開發人員變懶，懶得去想用面向集合的查詢方式實現某些功能。

在效能上，遊標會吃更多的記憶體，減少可用的並行，佔用頻寬，鎖定資源，當然還有更多的程式碼量。用一個比喻來說明為什麼遊標會佔用更多的資源。當你從ATM機取款的時候，是一次取1000的效率更高呢，還是10次100呢？

遊標是非常邪惡的一種存在，使用遊標經常會比使用面向集合的方法慢2-3倍，當遊標定義在巨量資料量時，這個比例還會增加。如果可能，儘量使用while,子查詢，臨時表，函數，表變數等來替代遊標，記住，遊標永遠只是你最後無奈之下的選擇，而不是首選。

既然遊標那麼多缺點，為什麼要學習遊標呢？

現存系統有一些遊標，我們查詢必須通過遊標來實現。
作用一個備用方式，當使用while、子查詢，臨時表，表變數，自建函數或其他方式仍然無法實現某些查詢的時候，可以使用遊標實現。

遊標的定義語法：

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中，定義一個遊標可以使非常簡單，也可以相對複雜，這主要取決於遊標的引數。而遊標的引數設定取決於你對遊標原理的瞭解程度。
遊標其實可以理解成一個定義在特定資料集上的指標，我們可以控制這個指標遍歷資料集，或者僅僅是指向特定的行，所以遊標是定義在以SELECT開始的資料集上的。

遊標分為遊標型別和遊標變數。

遊標變數支援兩種方式賦值，定義時賦值和先定義後賦值，定義遊標變數像定義其他區域性變數一樣，在遊標前加”@”。

注意，如果定義全域性的遊標，只支援定義時直接賦值，並且不能在遊標名稱前面加“@”。

兩種定義方式如下:

--定義後直接賦值
declare test_Cursor cursor for
    select * from Person;

--先定義後賦值
declare @TEST_Cursor2 cursor;

set @TEST_Cursor2 = cursor for
    select * from Person;

引數解釋：

1、LOCAL和GLOBAL二選一

如果不指定遊標作用域，預設作用域為GLOBAL。

--全域性遊標，跨GLOBAL
declare test_Cursor cursor global for
    select * from Person;

--區域性遊標，跨LOCAL
declare test_Cursor2 cursor local for
    select * from Person;

go --用GO結束上面的作用域

open test_Cursor;
open test_Cursor2; --此行程式碼報錯，報遊標不存在，因此可以理解區域性遊標不跨批次處理，批次處理結束後，將被隱式釋放，無法在其他批次處理中呼叫

2、FORWARD_ONLY 和 SCROLL 二選一

FORWARD_ONLY意味著遊標只能從資料集開始向資料集結束的方向讀取，FETCH NEXT是唯一的選項。預設為Forward_Only。
SCROLL支援遊標在定義的資料集中向任何方向，或任何位置移動。

--不加引數，預設為Forward_Only
declare test_Cursor cursor for
    select * from Person;

--加Forward_Only
declare test_Cursor2 cursor forward_only for
    select * from Person;

--加SCROLL
declare test_Cursor3 cursor scroll for
    select * from Person;

open test_Cursor;
open test_Cursor2;
open test_Cursor3;

fetch last from test_Cursor; --報錯 fetch: 提取型別 last 不能與只進遊標一起使用。
fetch last from test_Cursor2; --報錯 fetch: 提取型別 last 不能與只進遊標一起使用。
fetch last from test_Cursor3; --正確執行

3、遊標的分類：STATIC、 KEYSET 、DYNAMIC 和 FAST_FORWARD 四選一

這四個關鍵字是遊標所在資料集所反映的表資料和遊標讀取出資料的關係

STATIC：當遊標被建立時，將會建立FOR後面的SELECT語句所包含資料集的副本存入tempdb資料庫中，任何對於底層表內資料的更改不會影響到遊標內容。
DYNAMIC：和STATIC完全相反的選項，當底層資料庫更改時，遊標的內容也會隨之得到反映，在下一次fecth中，資料內容會隨之更改。
KEYSET：可以理解為介於STATIC和DYNAMIC的折中方案，將遊標所在結果集的唯一能確定每一行的主鍵存入tempdb，當結果集中任何行改變或者刪除時，@@FETCH_STATUS會為-2，KEYSET無法探測新加入的資料。
FAST_FORWARD：可以理解為FORWARD_ONLY的優化版本。FORWARD_ONLY執行的是靜態計劃，而FAST_FORWARD是根據情況進行選擇採用動態計劃還是靜態計劃，大多數情況下FAST_FORWARD要比FORWARD_ONLY效能略好。

4、READ_ONLY 、 SCROLL_LOCKS 和 OPTIMISTIC 三選一

READ_ONLY：意味著宣告的遊標只能讀取資料，遊標不能做任何更新操作。
SCROLL_LOCKS：是另一種極端，將讀入遊標的所有資料進行鎖定，防止其他程式進行更改，以確保更新的絕對成功。
OPTIMISTIC：相對比較好的一個選擇，OPTIMISTIC不鎖定任何資料，當需要在遊標中更新資料時，如果底層表資料更新，則遊標內資料更新不成功，如果，底層表資料未更新，則遊標內表資料可以更新。

5、For Update[of column_name ,....] :定義遊標中可更新的列。

二、開啟遊標

當定義完遊標後，遊標需要開啟後使用，只需一行程式碼便可開啟遊標:

OPEN test_Cursor

注意，當全域性遊標和區域性遊標變數重名時，預設會開啟區域性變數遊標。

三、使用遊標

1、利用遊標提取資料

遊標的使用分為兩部分，一部分是操作遊標在資料集內的指向，另一部分是將遊標所指向的行的部分或全部內容進行操作。
支援6種移動導航，分別為：

第一行(FIRST)
最後一行(LAST)
下一行(NEXT)
上一行(PRIOR)
直接跳到某行(ABSOLUTE(n))
相對於目前跳幾行(RELATIVE(n))

例如：

declare test_Cursor cursor scroll for
    select name from Person;

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;

--取相對目前來說上一行
fetch relative -1 from test_Cursor into @c;
print @c;

對於未指定SCROLL選項的遊標來說(未指定，則是隻進遊標)，只支援NEXT取值。

遊標經常會和全域性變數@@FETCH_STATUS與WHILE迴圈來共同使用,以達到遍歷遊標所在資料集的目的。

當執行一條Fetch語句之後，@@Fetch_Status可能出現3種值：

0，Fetch語句成功。
-1：Fetch語句失敗或行不在結果集中。
-2：提取的行不存在。

遊標總記錄數 @@CURSOR_ROWS

例如：

declare test_Cursor cursor fast_forward  for
    select id, name from Person;

open test_Cursor;

declare @id int;
declare @name nvarchar(10);

fetch next from test_Cursor into @id, @name;

while @@FETCH_STATUS = 0
    begin
        print @id;
        print @name;

        fetch next from test_Cursor into @id, @name;
    end;

close test_Cursor;
deallocate test_Cursor;

2、利用遊標更新刪除資料

遊標修改當前行資料語法：

Update 基表名 Set 列名=值[,...] Where Current of 遊標名

遊標刪除當前數行據語法：

Delete 基表名  Where Current of 遊標名

舉例：

---1.宣告遊標
declare orderNum_03_cursor cursor scroll for
    select OrderId, userId from bigorder where orderNum = 'ZEORD003402';

--2.開啟遊標
open orderNum_03_cursor;

--3.宣告遊標提取資料所要存放的變數
declare @OrderId int, @userId varchar(15);

--4.定位遊標到哪一行
fetch first from orderNum_03_cursor  into @OrderId, @userId; -- into的變數數量必須與遊標查詢結果集的列數相同

while @@fetch_status = 0 --提取成功，進行下一條資料的提取操作 
    begin
        if @OrderId = 122182
            begin
                update bigorder set UserId = '123' where current of orderNum_03_cursor; --修改當前行
            end;

        if @OrderId = 154074
            begin
                delete bigorder where current of orderNum_03_cursor; --刪除當前行
            end;

        fetch next from orderNum_03_cursor
        into @OrderId, @userId; --移動遊標
    end;

close orderNum_03_cursor;
deallocate orderNum_03_cursor;