Oracle中的游标（转）

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

显式游标

当声明了显式游标后，可以通过以下三条命令控制显式游标的操作：打开游标、推进游标、关闭游标。

声明显式游标

Ø 无参游标

cursor c_auths is select * from auths

Ø 有参游标

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

Ø 绑定变量参数的游标

v_code auths.author_code%type;

cursor c_auths is select * from auths where author_code=v_code;

打开显式游标

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

v_code:='A00001' ;

open c_auths;

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

推进显式游标

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

关闭显式游标

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

隐式游标

在PL/SQL中为所有的SQL数据操纵语句（包括返回一行的select）隐式声明游标称为隐式游标。主要原因是用户不能直接命名和控制此类游标。当用户在PL/SQL 中使用数据操纵语句（DML）和select into时，oracle预先定义一个名称为SQL的隐式游标，通过检查隐式游标的属性获取与最近执行的SQL语句相关信息。

　　在PL/SQL中向标准的select语句增加单独的into子句，就可以将从表或视图中查询记录赋予变量或行变量。需要注意的是select ..into 语句结果必须有且只能有一行。如果查询没有返回行，PL/SQL将抛出no_data_found异常。如果查询返回多行，则抛出 too_many_rows 异常。如果抛出异常，则停止执行，控制权转移到异常处理部分（没有异常处理，则程序中断）。在引发异常时，将不使用属性%found,%notfound,%rowcount来查明DML语句是否已影响了行数。

begin

update auths set entry_date_time=sysdate where author_code='A00017' ;

-- 如果update语句中修改的行不存在(SQL %notfound返回值为true ),则向auths表中插入一行。

if sql %nofound then

insert into auths 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;

游标属性

Ø %found 只有DML语句影响一行或多行时，%found属性才返回true

Ø %notfound正好跟%found属性相反。如果DML语句没有影响任何行数，则%notfound属性返回true.

Ø %rowcount返回DML语句影响的行数。如果DML语句没有影响任何行数，则%rowcount属性将返回0。

Ø %isopen 判断SQL游标是否已经打开。在执行SQL语句之后，oracle自动关闭SQL 游标，所以隐式游标的%isopen属性始终为false.

游标循环

FETCH循环

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;

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子句作为条件

declare

cursor cur_emp is

elect empno , ename , job from emp where empno = 7369 for update of ename ;

begin

for return_cur in cur_emp

loop

update emp set ename = 'LHG' where current of cur_emp ;

end loop ;

end ;

其中的for update of ename 意思是给表中的行加锁，经过测试，后面的ename可以写成该表中的任何字段，因为oracle中锁的最低级别是行锁，不存在给字段加锁。

下面是行锁的作用：

1.行锁开始于一个CURSOR的OPEN,结束于一个提交COMMIT或ROLLBACK，而并不是结束于一个CURSOR的结束(CLOSE)。

2.当在一个CURSOR中对某个表的行加锁，那么如果在这个SESSION中用另一个CURSOR操作该行记录时候会等待第一个cursor的完成提交，直到第一个cursor提交完成之后，第二个cursor中对改行的操作才会开始执行。

3.当第一个cursor中的由于操作错误而不能提交时候，第二个cursor将会一直等待下去，这样就形成了死锁。预防这种情况发生的方法是在for update of ename 之后指定NOWAIT选项，这样的话，当第二个cursor不会一直等待下去，而是出现 ORA-00054 [resource busy and acquire with NOWAIT specified] 的讯息。

4.既然for update of后面的字段是随便那个字段都可以，那么可不可以不写呢？如果你没必要指定NOWAIT选项，那么后面的字段就可以不写；如果你必须指定NOWAIT选项，则必须指定至少一个字段。

5.还有一种方法是用ROWID 替代WHERE CURRENT OF YOUR_CURSOR_NAME 语句。

如下代码：

declare
cursor cur_emp is select a . deptno ,a . dname , a . rowid , b . rowid rowid_1

from dept a , emp b where empno = 7369 and a . deptno = b . deptno for update nowait ;

     v_deptno dept . deptno % type ;
     v_dname dept . dname % type ;
     v_rowid rowid ;
     v_rowid_1 rowid ;
begin
     open cur_emp ;
     loop
     fetch cur_emp into v_deptno , v_dname , v_rowid , v_rowid_1 ;
     exit when cur_emp % notfound ;
        update dept set dname = 'abc' where rowid = v_rowid ;
        update emp set ename = 'frank' where rowid = v_rowid_1 ;
     end loop ;
     close cur_emp ;
     commit ;
exception
     when others then
        rollback ;
        raise ;
end ;

由此，推荐的for update的习惯是：

Ø NOWAIT 定然跟FOR UPDATE 之后。

Ø 直接用ROWID 替代WHERE CURRENT OF YOUR_CURSOR_NAME 语句，尤其在相对繁习的程序里头。

Ø COMMIT 必需存在程序结尾。以防死锁成形。

Ø EXCEPTION 里的ROLLBACK 是最基本的需要。

游标变量

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

游标变量的声明

-- 使用%rowtype定义一个游标变量类型。

type t_authsref is ref cursor return auths%rowtype;

v_authcv t_authsref;

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

type t_authsref is ref cursor;

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

打开游标变量

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

open v_authscv for select * from auths;

关闭游标操作

关闭游标操作用来释放查询所占用的资源。但没有释放游标变量占用的存储空间。当变量超出作用域时，它所占用的空间才被释放掉。下面的块中定义了一个没有指定结果集的游标变量，这样我们就可以使用这个游标变量指向不同的查询，并能够返回不同的记录类型：

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属性。

（转自 http://blog.csdn.net/bupt_zoucq/article/details/6771585)

posted @ 2015-10-31 16:54 WhyToHow 阅读(644) 评论(0) 编辑收藏举报

刷新页面返回顶部

WhyToHow

Oracle中的游标（转）

显式游标

隐式游标

游标属性

游标循环

使用current of cursor子句作为条件

游标变量

公告