環(huán)境區(qū)域是用來(lái)處理SQL語(yǔ)句的一個(gè)oracle存儲(chǔ)區(qū)域。游標(biāo)是指向它的指針或句柄。通過(guò)游 標(biāo)，PL/SQL程序可以控制這個(gè)環(huán)境區(qū)域中被處理的語(yǔ)句。

Oracle 中的游標(biāo)有兩種：顯式游標(biāo)、隱式游標(biāo)。

顯示游標(biāo)是用 cursor...is命令定義的游標(biāo)，它可以對(duì)查詢語(yǔ)句(select)返回的多條記錄進(jìn)行處理，而隱式游標(biāo)是在執(zhí)行插入(insert)、刪除 (delete)、修改(update)和返回單條記錄的查詢(select)語(yǔ)句時(shí)由PL/SQL自動(dòng)定義的。

1、顯式游標(biāo)操作

顯式游標(biāo)在塊定義部 分、包或子程序中聲明。當(dāng)聲明了顯式游標(biāo)后，可以通過(guò)以下三條命令控制顯式游標(biāo)的操作：打開(kāi)游標(biāo)、推進(jìn)游標(biāo)、關(guān)閉游標(biāo)。

(1)聲明顯式游標(biāo)

--例1

declare   
v_auths auths%rowtype;   
v_code auths.author_code%type;   
cursor c_auths is    
    select * from auths where author_code=v_code;

上例是將PL/SQL變量綁定在WHERE子句中，下面將游標(biāo)參數(shù)綁定在游標(biāo)的WHERE子句中：

--例2

delcare   
cursor c_auths(p_code auths.author_code%type) is   
    select * from auths where author_code=p_code; 

(2)打開(kāi)顯式游標(biāo)

游標(biāo)操作的第一步是打開(kāi)游標(biāo)。

例1，下面的語(yǔ)句是打開(kāi)上節(jié)例1中聲明的顯式游標(biāo)c_auths;

begin   
--在打開(kāi)游標(biāo)前為綁定變量賦值。   
v_code:='A00001';   
--打開(kāi)游標(biāo)。   
open c_auths; 

例2，如果對(duì)于一個(gè)帶參數(shù)的游標(biāo)

begin   
--打開(kāi)游標(biāo)時(shí)將參數(shù)傳入。   
open c_auths('A00001'); 

打開(kāi)一個(gè)已打開(kāi)的游標(biāo)也是合法的。當(dāng)?shù)诙未蜷_(kāi)游標(biāo)時(shí)，PL/SQL先自動(dòng)關(guān)閉游標(biāo)，然后再打開(kāi)。一次打開(kāi)多個(gè)游標(biāo)也 是PL/SQL所允許的。

(3)推進(jìn)顯式游標(biāo)

當(dāng)打開(kāi)顯式游標(biāo)后，就可以使用FETCH語(yǔ)句來(lái)推進(jìn)游標(biāo)，返回查詢結(jié)果集中的一行。每執(zhí)行完一條FETCH語(yǔ)句后，顯 式游標(biāo)會(huì)自動(dòng)指向查詢結(jié)果集的下一行。

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

當(dāng)整個(gè)結(jié)果集都檢索完以后，應(yīng)當(dāng)關(guān)閉游標(biāo)。關(guān)閉游標(biāo)用來(lái)通知PL/SQL游標(biāo)操作已經(jīng)結(jié)束，并且釋 放游標(biāo)所占用的資源(結(jié)果集所使用的資源空間)。

2、游標(biāo)的屬性

游標(biāo)有四個(gè)屬性：%found、%notfound、%isopen和%rowcount。要注意 這些屬性只能使用在過(guò)程性語(yǔ)句中，而不能使用在SQL語(yǔ)句中。

表 tableattribute，表中有兩列column1(number類型)和column2(varchar2類型)，現(xiàn)在向表中插入兩條記錄：

insert into tableattribute values(10,'first');

insert into tableattribute values(20,'second');

...

3、顯式游標(biāo)的推進(jìn)循環(huán)

delcare   
--聲明一個(gè)變量，這個(gè)變量用來(lái)接收游標(biāo)返回的結(jié)果集。   
v_salary auths.salary%type;   
v_code auths.author_code%type;   
/* 聲明游標(biāo)，該游標(biāo)的查詢結(jié)果集是作家代碼為"A00001"到"A00006"的工資值。*/  
cursor c_salary is select salary,author_code from auths where author_code<='A00006';   
begin   
--打開(kāi)游標(biāo)，并初始化結(jié)果集   
open c_salary;   
loop   
    --推進(jìn)游標(biāo)，將游標(biāo)的查詢結(jié)果集中的一行存到變量v_salary中。   
    fetch c_salary into v_salary,v_code;   
    --當(dāng)結(jié)果集中沒(méi)有行時(shí)退出循環(huán)。   
    exit when c_salary%notfound;   
    --如果查詢到的作家工資小于或等于200，則增加該作家的工資值。   
    if v_salary<=200 then   
      update auths set salary=salary+50 where author_code=v_code;   
    end if;   
end loop;   
--關(guān)閉游標(biāo)，釋放游標(biāo)占用資源。   
close c_salary;   
--提交所做的修 改。   
commit;   
end; 

PL/SQL 還提供了一種簡(jiǎn)單類型的循環(huán)，可以自動(dòng)控制游標(biāo)的打開(kāi)、推進(jìn)和關(guān)閉，叫做游標(biāo)的FOR循環(huán)。

delcare   
cursor c_salary is   
    select salary form auths where author_code<='A00006';   
begin   
-- 開(kāi)始游標(biāo)FOR循環(huán)，隱含地打開(kāi)c_salary游標(biāo)。   
for v_salary in c_salary loop   
    --一個(gè)隱含的fetch語(yǔ)句在這里被執(zhí)行。   
　　if v_salary.salary<=200 then   
      update auths set salary=salary+50 where salary=v_salary.salary;   
    end if;   
    --在循環(huán)繼續(xù)前，一個(gè)隱含的c_auths%notfound被檢測(cè)。   
　end loop;   
--現(xiàn)在循環(huán)已經(jīng)結(jié)束，c_auths游標(biāo)的一個(gè)隱含的close操作被執(zhí)行。   
commit;   
end; 

使用current of cursor子句作為條件

delcare   
--聲明游標(biāo)時(shí)在select語(yǔ)句中必須加for update of子句。   
cursor c_salary is   
    select salary form auths where author_code<'A00006' for update of salary;   
begin   
for v_salary in c_salary loop   
    if v_salary.salary<=200 then   
      --下面的update語(yǔ)句中的current of子句用來(lái)表明結(jié)果集的當(dāng)前行。
    update auths set salary=salary+50 where current of c_salary;  
    end if;   
end loop;   
commit;   
end; 

如果在游標(biāo)的FOR循環(huán)中使用子查 詢，則不用在塊定義部分聲明顯式游標(biāo)，在FOR循環(huán)中子查詢隱含聲明了一個(gè)顯式游標(biāo)。

begin   
--在下面的FOR循環(huán)中隱含地聲明了一個(gè)游標(biāo)c_salary。   
　for c_salary in   
(select salary form auths where author_code<='A00006') loop   
    if c_salary.salary<=200 then   
      update auths set salary=salary+50 where salary=c_salary.salary;   
    end if;   
end loop;   
commit;   
end; 

4、隱式游標(biāo)處理

PL/SQL隱式地 打開(kāi)SQL游標(biāo)，并在它內(nèi)部處理SQL語(yǔ)句，然后關(guān)閉它。SQL游標(biāo)用來(lái)處理insert、update、delete以及返回一行的 select...into語(yǔ)句。

一個(gè)SQL游標(biāo)不管是打開(kāi)還是關(guān) 閉，open、fetch和close命令都不能操作它。SQL游標(biāo)與顯式游標(biāo)類似，也有四個(gè)一樣的屬性。當(dāng)打開(kāi)SQL游標(biāo)之前，SQL游標(biāo)的屬性都為 NULL。

begin   
update auths set entry_date_time=sysdate where author_code='A00017';   
--如 果update語(yǔ)句中修改的行不存在(SQL%notfound返回值為true),則向auths表中插入一行。   
if sql%nofound then   
    insert into auths(author_code,name,sex,birthdate,salary,entry_date_time)   
      values('A000017','qiuys',1,'30-apr-40',88.5,sysdate);   
end if;   
end; 

--如果update語(yǔ)句中 修改的行不存在(sql%rowcount=0)

declare   
v_birthdate date;   
begin   
select birthdate into v_birthdate from auths where name='qiuys';   
--如果查詢到一條記錄，則刪除該記錄。   
if sql%found then   
    delete from auths where name='qiuys';   
end if;   
exception   
when no_data_found then   
    dbms_output.put_line('該記錄不存在');   
when too_many_rows then   
    dbms_output_line('存在同名的作家');   
end; 

5、游標(biāo)變量

到目前為止前面所有顯式游標(biāo)的例子都是靜態(tài)游標(biāo)－即游標(biāo)與一個(gè)SQL語(yǔ)句關(guān)聯(lián)，并且該SQL語(yǔ)句在編譯時(shí)已經(jīng)確定。

而游標(biāo)變量是一個(gè)引用類型(REF)的變量。

(1)游標(biāo)變量的聲明

declare   
--使用%rowtype定義一個(gè)游標(biāo)變量類型。   
type t_authsref is ref cursor return auths%rowtype;   
--定義一個(gè)記錄類型。   
type t_coderecord is record(   
    author_code article.author_code%type,   
    article_code article.article_code%type);   
--聲明一個(gè)記錄類型的變 量。   
v_code t_coderecord;   
--使用t_coderecord作為一個(gè)游標(biāo)變量類型的結(jié)果 集類型。   
type t_coderef is ref cursor return t_codeRecord;   
--使 用v_code作為一個(gè)游標(biāo)變量類型的結(jié)果集類型。   
type t_coderef2 is ref cursor return v_code%type;   
--使用上面的類型聲明的兩個(gè)游標(biāo)變量。   
v_authcv t_authsref;   
v_codecv t_coderef; 

PL/SQL2.8以上版本中，可以使用一個(gè)沒(méi)有指定結(jié)果集類型的游標(biāo)變量(沒(méi)有RETURN)來(lái)指定多個(gè)不同類型的查 詢。

type t_authsref if ref cursor;

v_cursorvar t_authsref;--聲明一個(gè)該類型的變量。

(2)打開(kāi)游標(biāo)變量

為 了將一個(gè)游標(biāo)變更與一個(gè)具體的select語(yǔ)句聯(lián)系起來(lái)，open的語(yǔ)法中增加了一個(gè)select語(yǔ)句。

open cursor_variable for select_statement;   
declare   
type t_authorsref is ref cursor return auths%rowtype;   
v_authscv t_authorsref;   
--然后打開(kāi)   
open v_authscv for select * from auths; 

3)推進(jìn)游標(biāo)變更

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

該操作用來(lái)釋放查 詢所占用的資源。但沒(méi)有釋放游標(biāo)變量占用的存儲(chǔ)空間。當(dāng)變量超出作用域時(shí)，它所占用的空間才被釋放掉。

下面的塊中定義了一個(gè)沒(méi)有指定結(jié)果集的游標(biāo)變量，這樣我們就可以使用這個(gè)游標(biāo)變量指向不同的查詢，并能夠返回不同的記錄 類型：

set serveroutput on size 100000 --設(shè)置存儲(chǔ)緩沖區(qū)大小。   
declare   
/*定義游標(biāo)變更類型t_curref，該游標(biāo)變量類型沒(méi)有指定 結(jié)果集類型，所以該游標(biāo)變量類型的變量可以返回不同的PL/SQL記錄類型。*/  
type t_curref is ref cursor;   
--聲明一個(gè)游標(biāo)變量類型的變量   
c_cursorref t_curref;   
--定義 PL/SQL記錄類型t_authorrec，該類型的變量用來(lái)接收游標(biāo)變量的返回值。   
type t_authorrec is record(   
    authorcode auths.author_code%type,   
    name auths.name%type);   
--定義PL/SQL記錄類型t_articlerec,該類型的變量也用來(lái)接收游標(biāo)變量 的返回值。   
type t_articlerec is record(   
    authorcode article.author_code%type,   
    title artitle.title%type);   
-- 聲明兩個(gè)記錄類型變量。   
v_author t_authorrec;   
v_article t_articlerec;   
begin   
--打開(kāi)游標(biāo)變量c_cursorref，返回t_authorrec類型的記 錄。   
open c_cursorref for    
    select author_code,name from auths where author_code in('A00001','A00002','A00003','A00004','A00005');   
--推進(jìn)游標(biāo)變量   
fetch c_cursorref into v_author;   
--游標(biāo)變量的推進(jìn)循環(huán)。   
while c_cursorref%found loop   
    --將作家代碼和相應(yīng)的作家名字輸出到屏幕上。   
    dbms_output.put(v_author.authorcode||':'||v_author.name||' ');   
    fetch c_cursorref into v_author;   
end loop;   
dbms_output.new_line;-- 向屏幕上輸出一個(gè)回車行。   
--關(guān)閉游標(biāo)變量，僅僅將游標(biāo)變量指定的資源釋放掉，游標(biāo)變量本身的存儲(chǔ)空間沒(méi)有釋放掉。   
close c_cursorref;   
--再次打開(kāi)游標(biāo)變量，返回t_articlerec類型的記錄。   
open c_cursorref for    
    select author_code,title from article   
    where author_code in('A00001','A00002','A00003','A00004','A00005');   
fetch c_cursorref into v_article;   
while c_cursorref%found loop   
    ...   
end loop;   
close c_cursorref;   
end; 

注意，在上例中，第一次關(guān)閉游標(biāo)變量是可省略的，因?yàn)樵诘诙未蜷_(kāi)游標(biāo)變量時(shí)，就將第一次 的查詢丟失掉了。而且游標(biāo)變量也有游標(biāo)屬性，通常在推進(jìn)游標(biāo)變量時(shí)使用這些游標(biāo)屬性，例如上例使用了%found屬性。