在 數(shù)據(jù)庫(kù) 開(kāi)發(fā)過(guò)程中，當(dāng)你檢索的數(shù)據(jù)只是一條記錄時(shí)，你所編寫(xiě)的事務(wù)語(yǔ)句代碼往往使用SELECT INSERT 語(yǔ)句。但是我們常常會(huì)遇到這樣情況，即從某一結(jié)果集中逐一地讀取一條記錄。那么如何解決這種問(wèn)題呢？游標(biāo)為我們提供了一種極為優(yōu)秀的解決方案。
1.1 游標(biāo)和游標(biāo)的優(yōu)點(diǎn)
??? 在數(shù)據(jù)庫(kù)中，游標(biāo)是一個(gè)十分重要的概念。游標(biāo)提供了一種對(duì)從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)總是與一條T_SQL 選擇語(yǔ)句相關(guān)聯(lián)因?yàn)橛螛?biāo)由結(jié)果集（可以是零條、一條或由相關(guān)的選擇語(yǔ)句檢索出的多條記錄）和結(jié)果集中指向特定記錄的游標(biāo)位置組成。當(dāng)決定對(duì)結(jié)果集進(jìn)行處理時(shí)，必須聲明一個(gè)指向該結(jié)果集的游標(biāo)。如果曾經(jīng)用 C 語(yǔ)言寫(xiě)過(guò)對(duì)文件進(jìn)行處理的程序，那么游標(biāo)就像您打開(kāi)文件所得到的文件句柄一樣，只要文件打開(kāi)成功，該文件句柄就可代表該文件。對(duì)于游標(biāo)而言，其道理是相同的。可見(jiàn)游標(biāo)能夠?qū)崿F(xiàn)按與傳統(tǒng)程序讀取平面文件類似的方式處理來(lái)自基礎(chǔ)表的結(jié)果集，從而把表中數(shù)據(jù)以平面文件的形式呈現(xiàn)給程序。

??? 我們知道關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)實(shí)質(zhì)是面向集合的，在MS SQL SERVER 中并沒(méi)有一種描述表中單一記錄的表達(dá)形式，除非使用where 子句來(lái)限制只有一條記錄被選中。因此我們必須借助于游標(biāo)來(lái)進(jìn)行面向單條記錄的數(shù)據(jù)處理。
??? 由此可見(jiàn)，游標(biāo)允許應(yīng)用程序?qū)Σ樵冋Z(yǔ)句select 返回的行結(jié)果集中每一行進(jìn)行相同或不同的操作，而不是一次對(duì)整個(gè)結(jié)果集進(jìn)行同一種操作；它還提供對(duì)基于游標(biāo)位置而對(duì)表中數(shù)據(jù)進(jìn)行刪除或更新的能力；而且，正是游標(biāo)把作為面向集合的數(shù)據(jù)庫(kù)管理系統(tǒng)和面向行的程序設(shè)計(jì)兩者聯(lián)系起來(lái)，使兩個(gè)數(shù)據(jù)處理方式能夠進(jìn)行溝通。

1.2 游標(biāo)種類
MS SQL SERVER 支持三種類型的游標(biāo)：Transact_SQL 游標(biāo)，API 服務(wù)器游標(biāo)和客戶游標(biāo)。
（1） Transact_SQL 游標(biāo)
??? Transact_SQL 游標(biāo)是由DECLARE CURSOR 語(yǔ)法定義、主要用在Transact_SQL 腳本、 存儲(chǔ)過(guò)程 和觸發(fā)器中。Transact_SQL 游標(biāo)主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的Transact_SQL 語(yǔ)句或是批處理、存儲(chǔ)過(guò)程、觸發(fā)器中的Transact_SQL 進(jìn)行管理。 Transact_SQL 游標(biāo)不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)。
（2） API 游標(biāo)
??? API 游標(biāo)支持在OLE DB， ODBC 以及DB_library 中使用游標(biāo)函數(shù)，主要用在服務(wù)器上。每一次客戶端應(yīng)用程序調(diào)用API 游標(biāo)函數(shù)，MS SQL SEVER 的OLE DB 提供者、ODBC驅(qū)動(dòng)器或DB_library 的動(dòng)態(tài)鏈接庫(kù)（DLL） 都會(huì)將這些客戶請(qǐng)求傳送給服務(wù)器以對(duì)API游標(biāo)進(jìn)行處理。
（3） 客戶游標(biāo)
??? 客戶游標(biāo)主要是當(dāng)在客戶機(jī)上緩存結(jié)果集時(shí)才使用。在客戶游標(biāo)中，有一個(gè)缺省的結(jié)果集被用來(lái)在客戶機(jī)上緩存整個(gè)結(jié)果集。客戶游標(biāo)僅支持靜態(tài)游標(biāo)而非動(dòng)態(tài)游標(biāo)。由于服務(wù)器游標(biāo)并不支持所有的Transact-SQL 語(yǔ)句或批處理，所以客戶游標(biāo)常常僅被用作服務(wù)器游標(biāo)的輔助。因?yàn)樵谝话闱闆r下，服務(wù)器游標(biāo)能支持絕大多數(shù)的游標(biāo)操作。
??? 由于API 游標(biāo)和Transact-SQL 游標(biāo)使用在服務(wù)器端，所以被稱為服務(wù)器游標(biāo)，也被稱為后臺(tái)游標(biāo)，而客戶端游標(biāo)被稱為前臺(tái)游標(biāo)。在本章中我們主要講述服務(wù)器（后臺(tái)）游標(biāo)。

select count(id) from info

select * from info

--清除所有記錄
truncate table info

declare @i int
set @i=1
while @i<1000000
begin
?insert into info values('Justin'+str(@i),'深圳'+str(@i))
?set @i=@i+1
end

1.3 游標(biāo)操作
使用游標(biāo)有四種基本的步驟:聲明游標(biāo)、打開(kāi)游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)。
聲明游標(biāo)
象使用其它類型的變量一樣,使用一個(gè)游標(biāo)之前,首先應(yīng)當(dāng)聲明它。游標(biāo)的聲明包括兩個(gè)部分:游標(biāo)的名稱;這個(gè)游標(biāo)所用到的SQL語(yǔ)句。如要聲明一個(gè)叫作Cus-tomerCursor的游標(biāo)用以查詢地址在北京的客戶的姓名、帳號(hào)及其余額,您可以編寫(xiě)如下代碼:
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="北京";
在游標(biāo)的聲明中有一點(diǎn)值得注意的是,如同其它變量的聲明一樣,聲明游標(biāo)的這一段代碼行是不執(zhí)行的,您不能將debug時(shí)的斷點(diǎn)設(shè)在這一代碼行上,也不能用IF...END IF語(yǔ)句來(lái)聲明兩個(gè)同名的游標(biāo),如下列的代碼就是錯(cuò)誤的。
IF Is_prov="北京"THEN
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="北京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province〈〉"北京";
END IF
打開(kāi)游標(biāo)
聲明了游標(biāo)后在作其它操作之前,必須打開(kāi)它。打開(kāi)游標(biāo)是執(zhí)行與其相關(guān)的一段SQL語(yǔ)句,例如打開(kāi)上例聲明的一個(gè)游標(biāo),我們只需鍵入:
OPEN CustomerCursor;
由于打開(kāi)游標(biāo)是對(duì)數(shù)據(jù)庫(kù)進(jìn)行一些SQL SELECT的操作,它將耗費(fèi)一段時(shí)間,主要取決于您使用的系統(tǒng)性能和這條語(yǔ)句的復(fù)雜程度。如果執(zhí)行的時(shí)間較長(zhǎng),可以考慮將屏幕上顯示的鼠標(biāo)改為hourglass。
提取數(shù)據(jù)
當(dāng)用OPEN語(yǔ)句打開(kāi)了游標(biāo)并在數(shù)據(jù)庫(kù)中執(zhí)行了查詢后,您不能立即利用在查詢結(jié)果集中的數(shù)據(jù)。您必須用FETCH語(yǔ)句來(lái)取得數(shù)據(jù)。一條FETCH語(yǔ)句一次可以將一條記錄放入程序員指定的變量中。事實(shí)上,FETCH語(yǔ)句是游標(biāo)使用的核心。在DataWindow和DataStore中,執(zhí)行了Retrieve()函數(shù)以后,查詢的所有結(jié)果全部可以得到;而使用游標(biāo),我們只能逐條記錄地得到查詢結(jié)果。
已經(jīng)聲明并打開(kāi)一個(gè)游標(biāo)后,我們就可以將數(shù)據(jù)放入任意的變量中。在FETCH語(yǔ)句中您可以指定游標(biāo)的名稱和目標(biāo)變量的名稱。如下例:
FETCH CustmerCur-sor
INTO:ls_acct_no,
:ls_name,
:ll_balance;
從語(yǔ)法上講,上面所述的就是一條合法的取數(shù)據(jù)的語(yǔ)句,但是一般我們使用游標(biāo)卻還應(yīng)當(dāng)包括其它的部分。正如我們前面所談到的,游標(biāo)只能一次從后臺(tái)數(shù)據(jù)庫(kù)中取一條記錄,而在多數(shù)情況下,我們所想要作的是在數(shù)據(jù)庫(kù)中從第一條記錄開(kāi)始提取,一直到結(jié)束。所以我們一般要將游標(biāo)提取數(shù)據(jù)的語(yǔ)句放在一個(gè)循環(huán)體內(nèi),直至將結(jié)果集中的全部數(shù)據(jù)提取后,跳出循環(huán)圈。通過(guò)檢測(cè)SQLCA.SQL-CODE的值,可以得知最后一條FETCH語(yǔ)句是否成功。一般,當(dāng)SQLCODE值為0時(shí)表明一切正常,100表示已經(jīng)取到了結(jié)果集的末尾,而其它值均表明操作出了問(wèn)題,這樣我們可以編寫(xiě)以下的代碼:
lb_continue=True
ll_total=0
DO WHILE lb_continue
FETCH CustomerCur-sor
INTO:ls_acct_no,
:ls_name,
:ll_balance;
If sqlca.sqlcode=0 Then
ll_total+=ll_balance
Else
lb_continue=False
End If
LOOP
循環(huán)體的結(jié)構(gòu)有多種,這里提到的是最常見(jiàn)的一種。也有的程序員喜愛(ài)將一條FETCH語(yǔ)句放在循環(huán)體的前面,循環(huán)體內(nèi)再放置另外一條FETCH語(yǔ)句,并檢測(cè)SQLCA.SQLCODE是否為100。但是這樣做,維護(hù)時(shí)需同時(shí)修改兩條FETCH語(yǔ)句,稍麻煩了些。
關(guān)閉游標(biāo)
在游標(biāo)操作的最后請(qǐng)不要忘記關(guān)閉游標(biāo),這是一個(gè)好的編程習(xí)慣,以使系統(tǒng)釋放游標(biāo)占用的資源。關(guān)閉游標(biāo)的語(yǔ)句很簡(jiǎn)單:
CLOSE CustomerCursor;
使用Where子句子
我們可以動(dòng)態(tài)地定義游標(biāo)中的Where子句的參數(shù),例如在本例中我們是直接定義了查詢省份是北京的記錄,但也許在應(yīng)用中我們要使用一個(gè)下拉式列表框,由用戶來(lái)選擇要查詢的省份,我們?cè)撛鯓幼瞿?
我們?cè)谇懊嬖?jīng)提到過(guò),DECLARE語(yǔ)句的作用只是定義一個(gè)游標(biāo),在OPEN語(yǔ)句中這個(gè)游標(biāo)才會(huì)真正地被執(zhí)行。了解了這些,我們就可以很方便地實(shí)現(xiàn)這樣的功能,在DECLARE的Where子句中加入變量作參數(shù),如下所示:
DECLARE CustomerCursor CURSOR FOR
SELCECT acct_no,name,balance
FROM customer
WHERE province=:ls_province;
∥定義ls_province的值
OPEN CustomerCursor;
游標(biāo)的類型
同其它變量一樣,我們也可以定義游標(biāo)的訪問(wèn)類型:全局、共享、實(shí)例或局部,游標(biāo)變量的命名規(guī)范建議也同其它變量一樣。
--聲明游標(biāo)
declare my_cursor cursor keyset for select * from info
--刪除游標(biāo)資源
deallocate my_cursor

--打開(kāi)游標(biāo),在游標(biāo)關(guān)閉或刪除前都有效
open my_cursor
--關(guān)閉游標(biāo)
close my_cursor

--聲明局部變量
declare @id int,@name varchar(20),@address varchar(20)
--定位到指定位置的記錄
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到當(dāng)前記錄相對(duì)位置記錄
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到當(dāng)前記錄前一條
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到當(dāng)前記錄后一條
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到首記錄
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到尾記錄
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address

實(shí)例：
?use database1
declare my_cursor cursor scroll dynamic
?/**//*scroll表示可隨意移動(dòng)游標(biāo)指??????? 針（否則只能向前），dynamic表示可以讀寫(xiě)游標(biāo)（否則游標(biāo)只讀）*/
for
select productname from? product

open my_cursor
declare @pname sysname
fetch next from my_cursor into @pname
while(@@fetch_status=0)
? begin
??? print 'Product Name: ' + @pname
??? fetch next from my_cursor into @pname
? end
fetch first from my_cursor into @pname
print @pname
/**//*update product set productname='zzg' where current of my_cursor */
/**//*delete from product where current of my_cursor */
close my_cursor
deallocate my_cursor

1.4 游標(biāo)的高級(jí)技巧

盡管目前基于SQL語(yǔ)句的后臺(tái)數(shù)據(jù)庫(kù)所支持的語(yǔ)言都大致相當(dāng),但對(duì)游標(biāo)的支持卻有著一些差異,例如對(duì)滾動(dòng)游標(biāo)支持。所謂滾動(dòng)游標(biāo),就是程序員可以指定游標(biāo)向前后任意一個(gè)方向滾動(dòng)。如在Informix中,您甚至還可以將游標(biāo)滾向結(jié)果集開(kāi)頭或末尾,使用的語(yǔ)句分別是FETCH FIRST,FETCH LAST、FETCH PRIOR和FETCH NEXT。當(dāng)程序員用FETCH語(yǔ)句,其缺省是指FETCH NEXT。由于滾動(dòng)是在數(shù)據(jù)庫(kù)后臺(tái)實(shí)現(xiàn)的,所以滾動(dòng)游標(biāo)為用戶編程提供了極大的方便。
對(duì)游標(biāo)支持的另一個(gè)不同是可修改游標(biāo)。上述游標(biāo)的使用都是指只讀游標(biāo),而象 Oracle 、Sybase等數(shù)據(jù)庫(kù)卻另外支持可作修改的游標(biāo)。使用這樣的數(shù)據(jù)庫(kù),您可以修改或刪除當(dāng)前游標(biāo)所在的行。例如修改當(dāng)前游標(biāo)所在行的用戶的余額,我們可以如下操作:
UPDATE customer
SET balance=1000
WHERE CURRENT of customerCursor;
刪除當(dāng)前行的操作如下:
DELETE FROM Customer
WHERE CURRENT OF CustomerCursor;
但是如果您當(dāng)前使用的數(shù)據(jù)庫(kù)是Sybase,您需要修改數(shù)據(jù)庫(kù)的參數(shù),將游標(biāo)可修改的值定為1,才能執(zhí)行上述操作。這一賦值在連接數(shù)據(jù)庫(kù)的前后進(jìn)行均可。
SQLCA.DBParm="Cursor Update=1"
另外一個(gè)內(nèi)容是動(dòng)態(tài)游標(biāo),也就是說(shuō)您可以運(yùn)行過(guò)程中動(dòng)態(tài)地形成游標(biāo)的SELECT語(yǔ)句。這同在PowerBuilder中動(dòng)態(tài)地使用嵌入式SQL一樣,需要用到DynamicStagin-gArea等數(shù)據(jù)類型,這已超出了本節(jié)的范圍。

?

?

TrackBack:http://www.knowsky.com/344071.html

SQL游標(biāo)原理和使用方法