共計(jì) 2773 個字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。

這篇文章將為大家詳細(xì)講解有關(guān) SQL Server 游標(biāo)的使用 / 關(guān)閉 / 釋放 / 優(yōu)化是怎樣的，文章內(nèi)容質(zhì)量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

游標(biāo)是邪惡的！

在關(guān)系數(shù)據(jù)庫中，我們對于查詢的思考是面向集合的。而游標(biāo)打破了這一規(guī)則，游標(biāo)使得我們思考方式變?yōu)橹鹦羞M(jìn)行. 對于類 C 的開發(fā)人員來著，這樣的思考方式會更加舒服。

正常面向集合的思維方式是:

而對于游標(biāo)來說:

這也是為什么游標(biāo)是邪惡的，它會使開發(fā)人員變懶，懶得去想用面向集合的查詢方式實(shí)現(xiàn)某些功能.

同樣的，在性能上，游標(biāo)會吃更多的內(nèi)存，減少可用的并發(fā)，占用寬帶，鎖定資源，當(dāng)然還有更多的代碼量……

從游標(biāo)對數(shù)據(jù)庫的讀取方式來說，不難看出游標(biāo)為什么占用更多的資源，打個比方:

當(dāng)你從 ATM 取錢的時候，是一次取 1000 效率更高呢，還是取 10 次 100？

既然游標(biāo)這么“邪惡”，為什么還要學(xué)習(xí)游標(biāo)

我個人認(rèn)為存在既是合理. 歸結(jié)來說，學(xué)習(xí)游標(biāo)原因我歸納為以下 2 點(diǎn)

1. 現(xiàn)存系統(tǒng)有一些游標(biāo)，我們查詢必須通過游標(biāo)來實(shí)現(xiàn)

2. 作為一個備用方式，當(dāng)我們窮盡了 while 循環(huán), 子查詢，臨時表，表變量, 自建函數(shù)或其他方式扔來無法實(shí)現(xiàn)某些查詢的時候，使用游標(biāo)實(shí)現(xiàn).

T-SQL 中游標(biāo)的生命周期以及實(shí)現(xiàn)

在 T -SQL 中，游標(biāo)的生命周期由 5 部分組成

1. 定義一個游標(biāo)

在 T -SQL 中，定義一個游標(biāo)可以是非常簡單，也可以相對復(fù)雜，取決于游標(biāo)的參數(shù). 而游標(biāo)的參數(shù)設(shè)置取決于你對游標(biāo)原理的了解程度.

游標(biāo)其實(shí)可以理解成一個定義在特定數(shù)據(jù)集上的指針，我們可以控制這個指針遍歷數(shù)據(jù)集，或者僅僅是指向特定的行，所以游標(biāo)是定義在以 Select 開始的數(shù)據(jù)集上的:

T-SQL 中的游標(biāo)定義在 MSDN 中如下:

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

看起來很讓人頭痛是吧. 下面仔細(xì)講一下如何定義游標(biāo):

游標(biāo)分為游標(biāo)類型和游標(biāo)變量，對于游標(biāo)變量來說，遵循 T -SQL 變量的定義方法（啥，不知道 T -SQL 變量定義的規(guī)則？參考我前面的博文）. 游標(biāo)變量支持兩種方式賦值，定義時賦值和先定義后賦值，定義游標(biāo)變量像定義其他局部變量一樣，在游標(biāo)前加”@”, 注意，如果定義全局的游標(biāo)，只支持定義時直接賦值，并且不能在游標(biāo)名稱前面加“@”，兩種定義方式如下:

下面我們來看游標(biāo)定義的參數(shù):

LOCAL 和 GLOBAL 二選一

LOCAL 意味著游標(biāo)的生存周期只在批處理或函數(shù)或存儲過程中可見，而 GLOBAL 意味著游標(biāo)對于特定連接作為上下文，全局內(nèi)有效, 例如:

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

FORWARD_ONLY 和 SCROLL 二選一

FORWARD_ONLY 意味著游標(biāo)只能從數(shù)據(jù)集開始向數(shù)據(jù)集結(jié)束的方向讀取，F(xiàn)ETCH NEXT 是唯一的選項(xiàng)，而 SCROLL 支持游標(biāo)在定義的數(shù)據(jù)集中向任何方向，或任何位置移動，如下圖:

STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四選一

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

STATIC 意味著，當(dāng)游標(biāo)被建立時，將會創(chuàng)建 FOR 后面的 SELECT 語句所包含數(shù)據(jù)集的副本存入 tempdb 數(shù)據(jù)庫中，任何對于底層表內(nèi)數(shù)據(jù)的更改不會影響到游標(biāo)的內(nèi)容.

DYNAMIC 是和 STATIC 完全相反的選項(xiàng), 當(dāng)?shù)讓訑?shù)據(jù)庫更改時，游標(biāo)的內(nèi)容也隨之得到反映，在下一次 fetch 中，數(shù)據(jù)內(nèi)容會隨之改變

KEYSET 可以理解為介于 STATIC 和 DYNAMIC 的折中方案。將游標(biāo)所在結(jié)果集的唯一能確定每一行的主鍵存入 tempdb, 當(dāng)結(jié)果集中任何行改變或者刪除時，@@FETCH_STATUS 會為 -2,KEYSET 無法探測新加入的數(shù)據(jù)

FAST_FORWARD 可以理解成 FORWARD_ONLY 的優(yōu)化版本.FORWARD_ONLY 執(zhí)行的是靜態(tài)計(jì)劃，而 FAST_FORWARD 是根據(jù)情況進(jìn)行選擇采用動態(tài)計(jì)劃還是靜態(tài)計(jì)劃，大多數(shù)情況下 FAST_FORWARD 要比 FORWARD_ONLY 性能略好.

READ_ONLY SCROLL_LOCKS OPTIMISTIC 三選一 READ_ONLY 意味著聲明的游標(biāo)只能讀取數(shù)據(jù), 游標(biāo)不能做任何更新操作

SCROLL_LOCKS 是另一種極端，將讀入游標(biāo)的所有數(shù)據(jù)進(jìn)行鎖定，防止其他程序進(jìn)行更改，以確保更新的絕對成功

OPTIMISTIC 是相對比較好的一個選擇，OPTIMISTIC 不鎖定任何數(shù)據(jù)，當(dāng)需要在游標(biāo)中更新數(shù)據(jù)時, 如果底層表數(shù)據(jù)更新，則游標(biāo)內(nèi)數(shù)據(jù)更新不成功，如果，底層表數(shù)據(jù)未更新，則游標(biāo)內(nèi)表數(shù)據(jù)可以更新

2. 打開游標(biāo)

當(dāng)定義完游標(biāo)后，游標(biāo)需要打開后使用，只有簡單一行代碼:

OPEN test_Cursor

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

3. 使用游標(biāo)

游標(biāo)的使用分為兩部分, 一部分是操作游標(biāo)在數(shù)據(jù)集內(nèi)的指向，另一部分是將游標(biāo)所指向的行的部分或全部內(nèi)容進(jìn)行操作

只有支持 6 種移動選項(xiàng), 分別為到第一行（FIRST), 最后一行 (LAST), 下一行 (NEXT), 上一行 (PRIOR), 直接跳到某行 (ABSOLUTE(n)), 相對于目前跳幾行 (RELATIVE(n)), 例如:

對于未指定 SCROLL 選項(xiàng)的游標(biāo)來說，只支持 NEXT 取值.

第一步操作完成后，就通過 INTO 關(guān)鍵字將這行的值傳入局部變量:

比如下面代碼:

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

4. 關(guān)閉游標(biāo)

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

CLOSE test_Cursor

5. 釋放游標(biāo)

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

DEALLOCATE test_Cursor

對于游標(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)如果只對數(shù)據(jù)進(jìn)行讀取，當(dāng)讀取時只用到 FETCH NEXT 選項(xiàng)，則最好使用 FORWARD_ONLY 參數(shù)。

關(guān)于 SQL Server 游標(biāo)的使用 / 關(guān)閉 / 釋放 / 優(yōu)化是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。