SQL Server游標(biāo)的使用/關(guān)閉/釋放/優(yōu)化小結(jié)
在關(guān)系數(shù)據(jù)庫中,我們對于查詢的思考是面向集合的。而游標(biāo)打破了這一規(guī)則,游標(biāo)使得我們思考方式變?yōu)橹鹦羞M(jìn)行.對于類C的開發(fā)人員來著,這樣的思考方式會更加舒服。
正常面向集合的思維方式是:
而對于游標(biāo)來說:
同樣的,在性能上,游標(biāo)會吃更多的內(nèi)存,減少可用的并發(fā),占用寬帶,鎖定資源,當(dāng)然還有更多的代碼量……
從游標(biāo)對數(shù)據(jù)庫的讀取方式來說,不難看出游標(biāo)為什么占用更多的資源,打個比方:
當(dāng)你從ATM取錢的時候,是一次取1000效率更高呢,還是取10次100?
我個人認(rèn)為存在既是合理.歸結(jié)來說,學(xué)習(xí)游標(biāo)原因我歸納為以下2點(diǎn)
1.現(xiàn)存系統(tǒng)有一些游標(biāo),我們查詢必須通過游標(biāo)來實現(xiàn)
2.作為一個備用方式,當(dāng)我們窮盡了while循環(huán),子查詢,臨時表,表變量,自建函數(shù)或其他方式扔來無法實現(xiàn)某些查詢的時候,使用游標(biāo)實現(xiàn).
T-SQL中游標(biāo)的生命周期以及實現(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ù)據(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)作用域為GLOBAL
FORWARD_ONLY 和 SCROLL 二選一
FORWARD_ONLY意味著游標(biāo)只能從數(shù)據(jù)集開始向數(shù)據(jù)集結(jié)束的方向讀取,F(xiàn)ETCH NEXT是唯一的選項,而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完全相反的選項,當(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)計劃,而FAST_FORWARD是根據(jù)情況進(jìn)行選擇采用動態(tài)計劃還是靜態(tài)計劃,大多數(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種移動選項,分別為到第一行(FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相對于目前跳幾行(RELATIVE(n)),例如:
對于未指定SCROLL選項的游標(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選項,則最好使用FORWARD_ONLY參數(shù)
相關(guān)文章
SQL Server的鏈接服務(wù)器技術(shù)小結(jié)
這篇文章主要介紹了SQL Server的鏈接服務(wù)器技術(shù)的相關(guān)資料,需要的朋友可以參考下2014-07-07SqlServer數(shù)據(jù)庫中文亂碼問題解決方法
SQL版的亂碼問題還是出現(xiàn)在SQL SERVER的安裝設(shè)置上。默認(rèn)安裝時系統(tǒng)默認(rèn)的排序規(guī)則是拉丁文的排序規(guī)則,但一般人在安裝時沒有考慮到這一點(diǎn),安裝時只是點(diǎn)取下一步,安裝完成后,造成了SQL版在使用過程中出現(xiàn)亂碼2020-03-03阿拉伯?dāng)?shù)字轉(zhuǎn)大寫中文_財務(wù)常用sql存儲過程
需求:輸入阿拉伯?dāng)?shù)字,給出對應(yīng)的中文大寫,編寫為存儲過程或函數(shù)2008-10-10SQL對冗余數(shù)據(jù)的刪除重復(fù)記錄只保留單條的說明
本文講一下數(shù)據(jù)庫中通常存在一些冗余數(shù)據(jù),如重復(fù)記錄就是一種,那這樣的記錄如何刪除呢?2010-03-03SQL?Server實現(xiàn)group_concat函數(shù)的詳細(xì)舉例
這篇文章主要給大家介紹了關(guān)于SQL?Server實現(xiàn)group_concat函數(shù)的詳細(xì)舉例,GROUP_CONCAT函數(shù)可以拼接某個字段值成字符串,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11