本文格式為Word版，下載可任意編輯——SQL游標原理和使用方法

SQL游標原理和使用方法

1.1游標和游標的優點

在數據庫中，游標是一個十分重要的概念。游標提供了一種對從表中檢索出的數據進行操作的靈活手段，就本質而言，游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。游標總是與一條T_SQL選擇語句相關聯由于游標由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的游標位置組成。當決定對結果集進行處理時，必需聲明一個指向該結果集的游標。假使曾經用C語言寫過對文件進行處理的程序，那么游標就像您開啟文件所得到的文件句柄一樣，只要文件開啟成功，該文件句柄就可代表該文件。對于游標而言，其道理是一致的。可見游標能夠實現按與傳統程序讀取平面文件類似的方式處理來自基礎表的結果集，從而把表中數據以平面文件的形式浮現給程序。

我們知道關系數據庫管理系統實質是面向集合的，在MSSQLSERVER中并沒有一種描述表中單一記錄的表達形式，除非使用where子句來限制只有一條記錄被選中。因此我們必需借助于游標來進行面向單條記錄的數據處理。

由此可見，游標允許應用程序對查詢語句select返回的行結果集中每一行進行一致或不同的操作，而不是一次對整個結果集進行同一種操作；它還提供對基于游標位置而對表中數據進行刪除或更新的能力；而且，正是游標把作為面向集合的數據庫管理系統和面向行的程序設計兩者聯系起來，使兩個數據處理方式能夠進行溝通。

1.2游標種類

MSSQLSERVER支持三種類型的游標：Transact_SQL游標，API服務器游標和客戶游標。

（1）Transact_SQL游標

Transact_SQL游標是由DECLARECURSOR語法定義、主要用在Transact_SQL腳本、存儲過程和觸發器中。Transact_SQL游標主要用在服務器上，由從客戶端發送給服務器的Transact_SQL語句或是批處理、存儲過程、觸發器中的Transact_SQL進行管理。Transact_SQL游標不支持提取數據塊或多行數據。

（2）API游標

API游標支持在OLEDB，ODBC以及DB_library中使用游標函數，主要用在服務器上。每一次客戶端應用程序調用API游標函數，MSSQLSEVER的OLEDB提供者、ODBC驅動器或DB_library的動態鏈接庫（DLL）都會將這些客戶請求傳送給服務器以對API游標進行處理

。

（3）客戶游標

客戶游標主要是當在客戶機上緩存結果集時才使用。在客戶游標中，有一個缺省的結果集被用來在客戶機上緩存整個結果集。客戶游標僅支持靜態游標而非動態游標。由于服務器游標并不支持所有的Transact-SQL語句或批處理，所以客戶游標往往僅被用作服務器游標的輔助。因

SQL游標原理和使用方法

面所談到的,游標只能一次從后臺數據庫中取一條記錄,而在多數狀況下,我們所想要作的是在數據庫中從第一條記錄開始提取,一直到終止。所以我們一般要將游標提取數據的語句放在一個循環體內,直至將結果集中的全部數據提取后,跳出循環圈。通過檢測SQLCA.SQL-CODE的值,可以得知最終一條FETCH語句是否成功。一般,當SQLCODE值為0時說明一切正常,100表示已經取到了結果集的末尾,而其它值均說明操作出了問題,這樣我們可以編寫以下的代碼:

lb_continue=True

ll_total=0

DOWHILElb_continue

FETCHCustomerCur-sor

INTO:ls_acct_no,

:ls_name,

:ll_balance;

Ifsqlca.sqlcode=0Then

ll_total+=ll_balance

Else

lb_continue=False

EndIf

LOOP

循環體的結構有多種,這里提到的是最常見的一種。也有的程序員愛好將一條FETCH語句放在循環體的前面,循環體內再放置另外一條FETCH語句,并檢測SQLCA.SQLCODE是否為100。但是這樣做,維護時需同時修改兩條FETCH語句,稍麻煩了些。

關閉游標

在游標操作的最終請不要忘掉關閉游標,這是一個好的編程習慣,以使系統釋放游標占用的資源。關閉游標的語句很簡單:

CLOSECustomerCursor;

使用Where子句子

我們可以動態地定義游標中的Where子句的參數,例如在本例中我們是直接定義了查詢省份是北京的記錄,但可能在應用中我們要使用一個下拉式列表框,由用戶來選擇要查詢的省份,我們該怎樣做呢?

我們在前面曾經提到過,DECLARE語句的作用只是定義一個游標,在OPEN語句中這個游標才會真正地被執行。了解了這些,我們就可以很便利地實現這樣的功能,在DECLARE的Where子句中參與變量作參數,如下所示:

DECLARECustomerCursorCURSORFOR

SELCECTacct_no,name,balance

FROMcustomer

WHEREprovince=:ls_province;

∥定義ls_province的值

OPENCustomerCursor;

游標的類型

同其它變量一樣,我們也可以定義游標的訪問類型:全局、共享、實例或局部,游標變量的命名規范建議也同其它變量一樣。

--聲明游標

declaremy_cursorcursorkeysetforselect*frominfo

--刪除游標資源

deallocatemy_cursor

--開啟游標,在游標關閉或刪除前都有效

openmy_cursor

--關閉游標

closemy_cursor

--聲明局部變量

declare@idint,@namevarchar(20),@addressvarchar(20)

--定位到指定位置的記錄

fetchabsolute56488frommy_cursorinto@id,@name,@address

select@idasid,@nameasname,@addressasaddress

--定位到當前記錄相對位置記錄

fetchrelative-88frommy_cursorinto@id,@name,@address

select@idasid,@nameasname,@addressasaddress

--定位到當前記錄前一條

fetchprio

rfrommy_cursorinto@id,@name,@address

select@idasid,@nameasname,@addressasaddress

--定位到當前記錄后一條

fetchnextfrommy_cursorinto@i

SQL游標原理和使用方法

d,@name,@address

select@idasid,@nameasname,@addressasaddress

--定位到首記錄

fetchfirstfrommy_cursorinto@id,@name,@address

select@idasid,@nameasname,@addressasaddress

--定位到尾記錄

fetchlastfrommy_cursorinto@id,@name,@address

select@idasid,@nameasname,@addressasaddress

實例：

usedatabase1

declaremy_cursorcursorscrolldynamic

/**//*scroll表示可隨意移動游標指針（否則只能向前），dynamic表示可以讀寫游標（否則游標只讀）*/

for

selectproductnamefromproduct

openmy_cursor

declare@pnamesysname

fetchnextfrommy_cursorinto@pname

while(@@fetch_status=0)

begin

print'ProductName:'+@pname

fetchnextfrommy_cursorinto@pname

end

fetchfirstfrommy_cursorinto@pname

print@pname

/**//*updateproductsetproductname='zzg'wherecurrentofmy_cursor*/

/**//*deletefromproductwherecurrentofmy_cursor*/

closemy_cursor

deallocatemy_cursor

1.4游標的高級技巧

盡管目前基于SQL語句的后臺數據庫所支持的語言都大致相當,但對游標的支持卻有著一些差異,例如對滾動游標支持。所謂滾動游標,就是程序員可以指定游標向前后任意一個方向滾動。如在Informix中,您甚至還可以將游標滾向結果集開頭或末尾,使用的語句分別是FETCHFIRST,FETCHLAST、FETCHPRIOR和FETCHNEXT。當程序員用FETCH語句,其缺省是指FETCHNEXT。由于滾動是在數據庫后臺實現的,所以滾動游標為用戶編程提供了極大的便利。

對游標支持的另一個不同是可修改游標。上述游標的使用都是指只讀游標,而象Oracle、Sybase等數據庫卻另外支持可作修改的游標。使用這樣的數據庫,您可以修改或刪除當前游標所在的行。例如修改當前游標所在行的用戶的余額,我們可以如下操作:

UPDATEcustomer

SETbalance=1000

WHERECURRENTofcustomerCursor;

刪除當前行的操作如下:

DELETEFROMCustomer

WHERECURRENTOFCustomerCursor;