游標(biāo)是面向行的，它會(huì)使開(kāi)發(fā)人員變懶，懶得去想用面向集合的查詢方式實(shí)現(xiàn)某些功能。

在性能上，游標(biāo)會(huì)吃更多的內(nèi)存，減少可用的并發(fā)，占用帶寬，鎖定資源，當(dāng)然還有更多的代碼量。用一個(gè)比喻來(lái)說(shuō)明為什么游標(biāo)會(huì)占用更多的資源。當(dāng)你從ATM機(jī)取款的時(shí)候，是一次取1000的效率更高呢，還是10次100呢？

游標(biāo)是非常邪惡的一種存在，使用游標(biāo)經(jīng)常會(huì)比使用面向集合的方法慢2-3倍，當(dāng)游標(biāo)定義在大數(shù)據(jù)量時(shí)，這個(gè)比例還會(huì)增加。如果可能，盡量使用while,子查詢，臨時(shí)表，函數(shù)，表變量等來(lái)替代游標(biāo)，記住，游標(biāo)永遠(yuǎn)只是你最后無(wú)奈之下的選擇，而不是首選。

既然游標(biāo)那么多缺點(diǎn)，為什么要學(xué)習(xí)游標(biāo)呢？

• 現(xiàn)存系統(tǒng)有一些游標(biāo)，我們查詢必須通過(guò)游標(biāo)來(lái)實(shí)現(xiàn)。
• 作用一個(gè)備用方式，當(dāng)使用while、子查詢，臨時(shí)表，表變量，自建函數(shù)或其他方式仍然無(wú)法實(shí)現(xiàn)某些查詢的時(shí)候，可以使用游標(biāo)實(shí)現(xiàn)。

游標(biāo)的定義語(yǔ)法：

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

一、定義游標(biāo)

在T-SQL中，定義一個(gè)游標(biāo)可以使非常簡(jiǎn)單，也可以相對(duì)復(fù)雜，這主要取決于游標(biāo)的參數(shù)。而游標(biāo)的參數(shù)設(shè)置取決于你對(duì)游標(biāo)原理的了解程度。 
游標(biāo)其實(shí)可以理解成一個(gè)定義在特定數(shù)據(jù)集上的指針，我們可以控制這個(gè)指針遍歷數(shù)據(jù)集，或者僅僅是指向特定的行，所以游標(biāo)是定義在以SELECT開(kāi)始的數(shù)據(jù)集上的。

游標(biāo)分為游標(biāo)類型和游標(biāo)變量。

游標(biāo)變量支持兩種方式賦值，定義時(shí)賦值和先定義后賦值，定義游標(biāo)變量像定義其他局部變量一樣，在游標(biāo)前加”@”。

注意，如果定義全局的游標(biāo)，只支持定義時(shí)直接賦值，并且不能在游標(biāo)名稱前面加“@”。

兩種定義方式如下:

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

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

set @TEST_Cursor2 = cursor for
    select * from Person;

參數(shù)解釋：

1、LOCAL和GLOBAL二選一

如果不指定游標(biāo)作用域，默認(rèn)作用域?yàn)镚LOBAL。

--全局游標(biāo)，跨GLOBAL
declare test_Cursor cursor global for
    select * from Person;

--局部游標(biāo)，跨LOCAL
declare test_Cursor2 cursor local for
    select * from Person;

go --用GO結(jié)束上面的作用域

open test_Cursor;
open test_Cursor2; --此行代碼報(bào)錯(cuò)，報(bào)游標(biāo)不存在，因此可以理解局部游標(biāo)不跨批處理，批處理結(jié)束后，將被隱式釋放，無(wú)法在其他批處理中調(diào)用

2、FORWARD_ONLY 和 SCROLL 二選一

• FORWARD_ONLY意味著游標(biāo)只能從數(shù)據(jù)集開(kāi)始向數(shù)據(jù)集結(jié)束的方向讀取，F(xiàn)ETCH NEXT是唯一的選項(xiàng)。默認(rèn)為Forward_Only。
• SCROLL支持游標(biāo)在定義的數(shù)據(jù)集中向任何方向，或任何位置移動(dòng)。

--不加參數(shù)，默認(rèn)為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; --報(bào)錯(cuò) fetch: 提取類型 last 不能與只進(jìn)游標(biāo)一起使用。
fetch last from test_Cursor2; --報(bào)錯(cuò) fetch: 提取類型 last 不能與只進(jìn)游標(biāo)一起使用。
fetch last from test_Cursor3; --正確執(zhí)行

3、游標(biāo)的分類：STATIC、 KEYSET 、DYNAMIC 和 FAST_FORWARD 四選一

這四個(gè)關(guān)鍵字是游標(biāo)所在數(shù)據(jù)集所反映的表數(shù)據(jù)和游標(biāo)讀取出數(shù)據(jù)的關(guān)系

• STATIC：當(dāng)游標(biāo)被建立時(shí)，將會(huì)創(chuàng)建FOR后面的SELECT語(yǔ)句所包含數(shù)據(jù)集的副本存入tempdb數(shù)據(jù)庫(kù)中，任何對(duì)于底層表內(nèi)數(shù)據(jù)的更改不會(huì)影響到游標(biāo)內(nèi)容。
• DYNAMIC：和STATIC完全相反的選項(xiàng)，當(dāng)?shù)讓訑?shù)據(jù)庫(kù)更改時(shí)，游標(biāo)的內(nèi)容也會(huì)隨之得到反映，在下一次fecth中，數(shù)據(jù)內(nèi)容會(huì)隨之更改。
• KEYSET：可以理解為介于STATIC和DYNAMIC的折中方案，將游標(biāo)所在結(jié)果集的唯一能確定每一行的主鍵存入tempdb，當(dāng)結(jié)果集中任何行改變或者刪除時(shí)，@@FETCH_STATUS會(huì)為-2，KEYSET無(wú)法探測(cè)新加入的數(shù)據(jù)。
• FAST_FORWARD：可以理解為FORWARD_ONLY的優(yōu)化版本。FORWARD_ONLY執(zhí)行的是靜態(tài)計(jì)劃，而FAST_FORWARD是根據(jù)情況進(jìn)行選擇采用動(dòng)態(tài)計(jì)劃還是靜態(tài)計(jì)劃，大多數(shù)情況下FAST_FORWARD要比FORWARD_ONLY性能略好。

4、READ_ONLY 、 SCROLL_LOCKS 和 OPTIMISTIC 三選一

• READ_ONLY：意味著聲明的游標(biāo)只能讀取數(shù)據(jù)，游標(biāo)不能做任何更新操作 。
• SCROLL_LOCKS：是另一種極端，將讀入游標(biāo)的所有數(shù)據(jù)進(jìn)行鎖定，防止其他程序進(jìn)行更改，以確保更新的絕對(duì)成功。
• OPTIMISTIC：相對(duì)比較好的一個(gè)選擇，OPTIMISTIC不鎖定任何數(shù)據(jù)，當(dāng)需要在游標(biāo)中更新數(shù)據(jù)時(shí)，如果底層表數(shù)據(jù)更新，則游標(biāo)內(nèi)數(shù)據(jù)更新不成功，如果，底層表數(shù)據(jù)未更新，則游標(biāo)內(nèi)表數(shù)據(jù)可以更新。

5、For Update[of column_name ,....] :定義游標(biāo)中可更新的列。

二、打開(kāi)游標(biāo)

當(dāng)定義完游標(biāo)后，游標(biāo)需要打開(kāi)后使用，只需一行代碼便可打開(kāi)游標(biāo):

OPEN test_Cursor

注意，當(dāng)全局游標(biāo)和局部游標(biāo)變量重名時(shí)，默認(rèn)會(huì)打開(kāi)局部變量游標(biāo)。

三、使用游標(biāo)

1、利用游標(biāo)提取數(shù)據(jù)

游標(biāo)的使用分為兩部分，一部分是操作游標(biāo)在數(shù)據(jù)集內(nèi)的指向，另一部分是將游標(biāo)所指向的行的部分或全部?jī)?nèi)容進(jìn)行操作。 
支持6種移動(dòng)導(dǎo)航，分別為：

• 第一行(FIRST)
• 最后一行(LAST)
• 下一行(NEXT)
• 上一行(PRIOR)
• 直接跳到某行(ABSOLUTE(n))
• 相對(duì)于目前跳幾行(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;

--取相對(duì)目前來(lái)說(shuō)上一行
fetch relative -1 from test_Cursor into @c;
print @c;

對(duì)于未指定SCROLL選項(xiàng)的游標(biāo)來(lái)說(shuō)(未指定，則是只進(jìn)游標(biāo))，只支持NEXT取值。

游標(biāo)經(jīng)常會(huì)和全局變量@@FETCH_STATUS與WHILE循環(huán)來(lái)共同使用,以達(dá)到遍歷游標(biāo)所在數(shù)據(jù)集的目的。

當(dāng)執(zhí)行一條Fetch語(yǔ)句之后，@@Fetch_Status可能出現(xiàn)3種值：

• 0，F(xiàn)etch語(yǔ)句成功。
• -1：Fetch語(yǔ)句失敗或行不在結(jié)果集中。
• -2：提取的行不存在。

游標(biāo)總記錄數(shù) @@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、利用游標(biāo)更新刪除數(shù)據(jù)

游標(biāo)修改當(dāng)前行數(shù)據(jù)語(yǔ)法：

Update 基表名 Set 列名=值[,...] Where Current of 游標(biāo)名

游標(biāo)刪除當(dāng)前數(shù)行據(jù)語(yǔ)法：

Delete 基表名  Where Current of 游標(biāo)名

舉例：

---1.聲明游標(biāo)
declare orderNum_03_cursor cursor scroll for
    select OrderId, userId from bigorder where orderNum = 'ZEORD003402';

--2.打開(kāi)游標(biāo)
open orderNum_03_cursor;

--3.聲明游標(biāo)提取數(shù)據(jù)所要存放的變量
declare @OrderId int, @userId varchar(15);

--4.定位游標(biāo)到哪一行
fetch first from orderNum_03_cursor  into @OrderId, @userId; -- into的變量數(shù)量必須與游標(biāo)查詢結(jié)果集的列數(shù)相同

while @@fetch_status = 0 --提取成功，進(jìn)行下一條數(shù)據(jù)的提取操作 
    begin
        if @OrderId = 122182
            begin
                update bigorder set UserId = '123' where current of orderNum_03_cursor; --修改當(dāng)前行
            end;

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

        fetch next from orderNum_03_cursor
        into @OrderId, @userId; --移動(dòng)游標(biāo)
    end;

close orderNum_03_cursor;
deallocate orderNum_03_cursor;

四、關(guān)閉游標(biāo)

在游標(biāo)使用完之后，一定要記得關(guān)閉,只需要一行代碼:CLOSE+游標(biāo)名稱

close  test_Cursor

五、釋放游標(biāo)

當(dāng)游標(biāo)不再需要被使用后，釋放游標(biāo)，只需要一行代碼:DEALLOCATE+游標(biāo)名稱

deallocate test_Cursor

六、對(duì)于游標(biāo)一些優(yōu)化建議

• 如果能不用游標(biāo)，盡量不要使用游標(biāo)
• 用完之后一定要關(guān)閉和釋放
• 盡量不要在大量數(shù)據(jù)上定義游標(biāo)
• 盡量不要使用游標(biāo)上更新數(shù)據(jù)
• 盡量不要使用insensitive, static和keyset這些參數(shù)定義游標(biāo)
• 如果可以，盡量使用FAST_FORWARD關(guān)鍵字定義游標(biāo)
• 如果只對(duì)數(shù)據(jù)進(jìn)行讀取，當(dāng)讀取時(shí)只用到FETCH NEXT選項(xiàng)，則最好使用FORWARD_ONLY參數(shù)

到此這篇關(guān)于SQL Server游標(biāo)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論