oracle 游标以及游标变量(二)

宿芳蕙

环境区域是用来处理SQL语句的一个oracle存储区域。游标是指向它的指针或句柄。通过游标，PL/SQL程序可以控制这个环境区域中被处理的语句。

Oracle中的游标有两种：显式游标、隐式游标。

显示游标是用cursor...is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理，而隐式游标是在执行插入(insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。

1、显式游标操作

显式游标在块定义部分、包或子程序中声明。当声明了显式游标后，可以通过以下三条命令控制显式游标的操作：打开游标、推进游标、关闭游标。

(1)声明显式游标

--例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子句中，下面将游标参数绑定在游标的WHERE子句中：

--例2


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



(2)打开显式游标

游标操作的第一步是打开游标。

例1，下面的语句是打开上节例1中声明的显式游标c_auths;


begin   
--在打开游标前为绑定变量赋值。   
v_code:='A00001';   
--打开游标。   
open c_auths;  


例2，如果对于一个带参数的游标


begin   
--打开游标时将参数传入。   
open c_auths('A00001');  


打开一个已打开的游标也是合法的。当第二次打开游标时，PL/SQL先自动关闭游标，然后再打开。一次打开多个游标也是PL/SQL所允许的。

(3)推进显式游标

当打开显式游标后，就可以使用FETCH语句来推进游标，返回查询结果集中的一行。每执行完一条FETCH语句后，显式游标会自动指向查询结果集的下一行。

(4)关闭显式游标

当整个结果集都检索完以后，应当关闭游标。关闭游标用来通知PL/SQL游标操作已经结束，并且释放游标所占用的资源(结果集所使用的资源空间)。

2、游标的属性

游标有四个属性：%found、%notfound、%isopen和%rowcount。要注意这些属性只能使用在过程性语句中，而不能使用在SQL语句中。

表tableattribute，表中有两列column1(number类型)和column2(varchar2类型)，现在向表中插入两条记录：

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

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

...

3、显式游标的推进循环


delcare   
--声明一个变量，这个变量用来接收游标返回的结果集。   
v_salary auths.salary%type;   
v_code auths.author_code%type;   
/*声明游标，该游标的查询结果集是作家代码为"A00001"到"A00006"的工资值。*/   
cursor c_salary is select salary,author_code from auths where author_code<='A00006';   
begin   
--打开游标，并初始化结果集   
open c_salary;   
loop   
    --推进游标，将游标的查询结果集中的一行存到变量v_salary中。   
    fetch c_salary into v_salary,v_code;   
    --当结果集中没有行时退出循环。   
    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;   
--关闭游标，释放游标占用资源。   
close c_salary;   
--提交所做的修改。   
commit;   
end;  


PL/SQL还提供了一种简单类型的循环，可以自动控制游标的打开、推进和关闭，叫做游标的FOR循环。


delcare   
cursor c_salary is   
    select salary form auths where author_code<='A00006';   
begin   
--开始游标FOR循环，隐含地打开c_salary游标。   
for v_salary in c_salary loop   
    --一个隐含的fetch语句在这里被执行。   
　　if v_salary.salary<=200 then   
      update auths set salary=salary+50 where salary=v_salary.salary;   
    end if;   
    --在循环继续前，一个隐含的c_auths%notfound被检测。   
　end loop;   
--现在循环已经结束，c_auths游标的一个隐含的close操作被执行。   
commit;   
end;  


使用current of cursor子句作为条件


delcare   
--声明游标时在select语句中必须加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语句中的current of子句用来表明结果集的当前行。
    update auths set salary=salary+50 where current of c_salary;   
    end if;   
end loop;   
commit;   
end;  



如果在游标的FOR循环中使用子查询，则不用在块定义部分声明显式游标，在FOR循环中子查询隐含声明了一个显式游标。


begin   
--在下面的FOR循环中隐含地声明了一个游标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、隐式游标处理

PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它。SQL游标用来处理insert、update、delete以及返回一行的select...into语句。

一个SQL游标不管是打开还是关闭，open、fetch和close命令都不能操作它。SQL游标与显式游标类似，也有四个一样的属性。当打开SQL游标之前，SQL游标的属性都为NULL。


begin   
update auths set entry_date_time=sysdate where author_code='A00017';   
--如果update语句中修改的行不存在(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语句中修改的行不存在(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、游标变量

到目前为止前面所有显式游标的例子都是静态游标－即游标与一个SQL语句关联，并且该SQL语句在编译时已经确定。

而游标变量是一个引用类型(REF)的变量。

(1)游标变量的声明


declare   
--使用%rowtype定义一个游标变量类型。   
type t_authsref is ref cursor return auths%rowtype;   
--定义一个记录类型。   
type t_coderecord is record(   
    author_code article.author_code%type,   
    article_code article.article_code%type);   
--声明一个记录类型的变量。   
v_code t_coderecord;   
--使用t_coderecord作为一个游标变量类型的结果集类型。   
type t_coderef is ref cursor return t_codeRecord;   
--使用v_code作为一个游标变量类型的结果集类型。   
type t_coderef2 is ref cursor return v_code%type;   
--使用上面的类型声明的两个游标变量。   
v_authcv t_authsref;   
v_codecv t_coderef;  


PL/SQL2.8以上版本中，可以使用一个没有指定结果集类型的游标变量(没有RETURN)来指定多个不同类型的查询。

type t_authsref if ref cursor;

v_cursorvar t_authsref;--声明一个该类型的变量。

(2)打开游标变量

为了将一个游标变更与一个具体的select语句联系起来，open的语法中增加了一个select语句。


open cursor_variable for select_statement;   
declare   
type t_authorsref is ref cursor return auths%rowtype;   
v_authscv t_authorsref;   
--然后打开   
open v_authscv for select * from auths;  


3)推进游标变更

(4)关闭游标变更

该操作用来释放查询所占用的资源。但没有释放游标变量占用的存储空间。当变量超出作用域时，它所占用的空间才被释放掉。

下面的块中定义了一个没有指定结果集的游标变量，这样我们就可以使用这个游标变量指向不同的查询，并能够返回不同的记录类型：


set serveroutput on size 100000 --设置存储缓冲区大小。   
declare   
/*定义游标变更类型t_curref，该游标变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PL/SQL记录类型。*/   
type t_curref is ref cursor;   
--声明一个游标变量类型的变量   
c_cursorref t_curref;   
--定义PL/SQL记录类型t_authorrec，该类型的变量用来接收游标变量的返回值。   
type t_authorrec is record(   
    authorcode auths.author_code%type,   
    name auths.name%type);   
--定义PL/SQL记录类型t_articlerec,该类型的变量也用来接收游标变量的返回值。   
type t_articlerec is record(   
    authorcode article.author_code%type,   
    title artitle.title%type);   
--声明两个记录类型变量。   
v_author t_authorrec;   
v_article t_articlerec;   
begin   
--打开游标变量c_cursorref，返回t_authorrec类型的记录。   
open c_cursorref for     
    select author_code,name from auths where author_code in('A00001','A00002','A00003','A00004','A00005');   
--推进游标变量   
fetch c_cursorref into v_author;   
--游标变量的推进循环。   
while c_cursorref%found loop   
    --将作家代码和相应的作家名字输出到屏幕上。   
    dbms_output.put(v_author.authorcode||':'||v_author.name||' ');   
    fetch c_cursorref into v_author;   
end loop;   
dbms_output.new_line;--向屏幕上输出一个回车行。   
--关闭游标变量，仅仅将游标变量指定的资源释放掉，游标变量本身的存储空间没有释放掉。   
close c_cursorref;   
--再次打开游标变量，返回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;  


注意，在上例中，第一次关闭游标变量是可省略的，因为在第二次打开游标变量时，就将第一次的查询丢失掉了。而且游标变量也有游标属性，通常在推进游标变量时使用这些游标属性，例如上例使用了%found属性。