SQL Server游標(biāo)的使用【轉(zhuǎn)】
（?? 如果能不用游標(biāo)，盡量不要使用游標(biāo)） ? 游標(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)為什么占用更多的資源，打個(gè)比方:
?????????
????? 當(dāng)你從ATM取錢的時(shí)候，是一次取1000效率更高呢，還是取10次100？
?
?
既然游標(biāo)這么“邪惡”，為什么還要學(xué)習(xí)游標(biāo)
????? 我個(gè)人認(rèn)為存在既是合理.歸結(jié)來說，學(xué)習(xí)游標(biāo)原因我歸納為以下2點(diǎn)
??? 1.現(xiàn)存系統(tǒng)有一些游標(biāo)，我們查詢必須通過游標(biāo)來實(shí)現(xiàn)
??? 2.作為一個(gè)備用方式，當(dāng)我們窮盡了while循環(huán),子查詢，臨時(shí)表，表變量,自建函數(shù)或其他方式扔來無法實(shí)現(xiàn)某些查詢的時(shí)候，使用游標(biāo)實(shí)現(xiàn).
?
T-SQL中游標(biāo)的生命周期以及實(shí)現(xiàn)
??? 在T-SQL中，游標(biāo)的生命周期由5部分組成
1.定義一個(gè)游標(biāo)
???? 在T-SQL中，定義一個(gè)游標(biāo)可以是非常簡單，也可以相對復(fù)雜，取決于游標(biāo)的參數(shù).而游標(biāo)的參數(shù)設(shè)置取決于你對游標(biāo)原理的了解程度.
???? 游標(biāo)其實(shí)可以理解成一個(gè)定義在特定數(shù)據(jù)集上的指針，我們可以控制這個(gè)指針遍歷數(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)變量支持兩種方式賦值，定義時(shí)賦值和先定義后賦值，定義游標(biāo)變量像定義其他局部變量一樣，在游標(biāo)前加”@”,注意，如果定義全局的游標(biāo)，只支持定義時(shí)直接賦值，并且不能在游標(biāo)名稱前面加“@”，兩種定義方式如下:
???
??? 下面我們來看游標(biāo)定義的參數(shù):
???? LOCAL和GLOBAL二選一
???? LOCAL意味著游標(biāo)的生存周期只在批處理或函數(shù)或存儲過程中可見，而GLOBAL意味著游標(biāo)對于特定連接作為上下文，全局內(nèi)有效,例如:
????
???? 如果不指定游標(biāo)作用域，默認(rèn)作用域?yàn)镚LOBAL
?
???? FORWARD_ONLY 和 SCROLL 二選一
???? FORWARD_ONLY意味著游標(biāo)只能從數(shù)據(jù)集開始向數(shù)據(jù)集結(jié)束的方向讀取，F(xiàn)ETCH NEXT是唯一的選項(xiàng)，而SCROLL支持游標(biāo)在定義的數(shù)據(jù)集中向任何方向，或任何位置移動(dòng)，如下圖:
????
????
??? STATIC? KEYSET? DYNAMIC? 和 FAST_FORWARD 四選一
??? 這四個(gè)關(guān)鍵字是游標(biāo)所在數(shù)據(jù)集所反應(yīng)的表內(nèi)數(shù)據(jù)和游標(biāo)讀取出的數(shù)據(jù)的關(guān)系
??? STATIC意味著，當(dāng)游標(biāo)被建立時(shí)，將會創(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ù)庫更改時(shí)，游標(biāo)的內(nèi)容也隨之得到反映，在下一次fetch中，數(shù)據(jù)內(nèi)容會隨之改變
??? KEYSET可以理解為介于STATIC和DYNAMIC的折中方案。將游標(biāo)所在結(jié)果集的唯一能確定每一行的主鍵存入tempdb,當(dāng)結(jié)果集中任何行改變或者刪除時(shí)，@@FETCH_STATUS會為-2,KEYSET無法探測新加入的數(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性能略好.
?
??? READ_ONLY? SCROLL_LOCKS? OPTIMISTIC 三選一
??? READ_ONLY意味著聲明的游標(biāo)只能讀取數(shù)據(jù),游標(biāo)不能做任何更新操作
??? SCROLL_LOCKS是另一種極端，將讀入游標(biāo)的所有數(shù)據(jù)進(jìn)行鎖定，防止其他程序進(jìn)行更改，以確保更新的絕對成功
??? OPTIMISTIC是相對比較好的一個(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ù)可以更新
??
?
?
2.打開游標(biāo)
??? 當(dāng)定義完游標(biāo)后，游標(biāo)需要打開后使用，只有簡單一行代碼:
OPEN?test_Cursor
??? 注意，當(dāng)全局游標(biāo)和局部游標(biāo)變量重名時(shí)，默認(rèn)會打開局部變量游標(biāo)
3.使用游標(biāo)
?
?? 游標(biāo)的使用分為兩部分,一部分是操作游標(biāo)在數(shù)據(jù)集內(nèi)的指向，另一部分是將游標(biāo)所指向的行的部分或全部內(nèi)容進(jìn)行操作
?? 只有支持6種移動(dòng)選項(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)讀取時(shí)只用到FETCH NEXT選項(xiàng)，則最好使用FORWARD_ONLY參數(shù)
?
總結(jié)
???? 本文從游標(biāo)的基本概念，到生命周期來談?dòng)螛?biāo)。游標(biāo)是非常邪惡的一種存在，使用游標(biāo)經(jīng)常會比使用面向集合的方法慢2-3倍，當(dāng)游標(biāo)定義在大數(shù)據(jù)量時(shí)，這個(gè)比例還會增加。如果可能，盡量使用while,子查詢，臨時(shí)表，函數(shù)，表變量等來替代游標(biāo)，記住，游標(biāo)永遠(yuǎn)只是你最后無奈之下的選擇，而不是首選。
???? 游標(biāo)是邪惡的！
下載文檔
