oracle游标的简单学习-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

oracle游标的简单学习

简介:

/*游标的简单学习*/

一.概念:


1.游标:游标是用于在存储过程中迭代SELECT查询出的数据。


2.什么是游标?


①游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。

每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理。



3.游标有什么作用?

①指定结果集中特定行的位置。

②基于当前的结果集位置检索一行或连续的几行。

③在结果集的当前位置修改行中的数据。

④对其他用户所做的数据更改定义不同的敏感性级别。

⑤可以以编程的方式访问数据库。



4.为什么避免使用游标?

①在创建游标时,最需要考虑的事情是,“是否有办法避免使用游标?”

 因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;

 如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。.




5.Oracle游标的类型?

静态游标:结果集已经确定(静态定义)的游标。分为隐式和显示游标。

   ⑴隐式游标:所有DML语句为隐式游标,通过隐式游标属性可以获取SQL语句信息。

   ⑵显示游标:用户显示声明的游标,即指定结果集。当查询返回结果超过一行时,就需要一个显式游标。

②REF游标:动态关联结果集的临时对象。  




6. Oracle游标的状态有哪些,怎么使用游标属性?

①游标的状态是通过属性来表示。

%Found :Fetch语句(获取记录)执行情况True or False。

 %NotFound : 最后一条记录是否提取出True or False。

 %ISOpen : 游标是否打开True or False。

 %RowCount :游标当前提取的行数 。


说明:

在打开一个游标之后,马上检查它的%found或%notfound属性,

它得到的结果即不是true也不是false.而是null.

必须执行一条fetch语句后,这些属性才有值。




②使用游标的属性。

eg1.

begin

 update emp set sal = sal + 0.1 where job = 'CLERK';

 if SQL%Found then

     dbms_output.put_line('update success');

 else

   dbms_output.put_line('update failed');

 end if;

end;    


7.如何使用游标,如何遍历游标?

 ①使用显示游标

  ⑴声明游标:划分存储区域,注意此时并没有执行Select 语句。

CURSOR 游标名( 参数 列表)   [返回值类型]   IS   Select 语句;

  ⑵打开游标:执行Select 语句,获得结果集存储到游标中,此时游标指向结果集头, 而不是第一条记录。

Open 游标名( 参数 列表);

  ⑶获取记录:移动游标取一条记录

Fetch  游标名InTo  临时记录或属性类型变量;

  ⑷关闭游标:将游标放入缓冲池中,没有完全释放资源。可重新打开。

Close  游标名;

 ②遍历循环游标

 ⑴For 循环游标

循环游标隐式打开游标,自动滚动获取一条记录,并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

    For  变量名  In  游标名

    Loop

     数据处理语句;

    End Loop;

  ⑵Loop循环游标

    。。。

   Loop

    Fatch  游标名InTo  临时记录或属性类型变量;

    Exit  When   游标名%NotFound;

   End   Loop;

    。。。

eg1.

declare cursor myCur

is

   select empno,ename,sal from emp;

   vname varchar2(10);

   vno number(4);

   vsal number(7,2);

begin

 open myCur;

fetch myCur into vno,vname,vsal;

 --只取了所有结果的第一行!

 dbms_output.put_line('vno :' || vno || 'ename: ' || vname || 'sal:' || vsal);

 close myCur;

end;

/      



eg2.使用loop遍历游标

declare cursor myCur

is

   select empno,ename,sal,job from emp;

   varObj myCur%rowType;

begin

 if myCur%isopen= false then

   open myCur;

   dbms_output.put_line('Opening ...');

 end if;  

 loop

     fetch myCur into varObj;

exit when myCur%notfound;

     dbms_output.put_line(myCur%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);

 end loop;

 if myCur%isopen then

   close myCur;

   dbms_output.put_line('closeing...');

 end if;

end;

/        



eg3.使用for循环遍历游标,

【循环游标隐式打开游标,自动滚动获取一条记录,

并自动创建临时记录类型变量存储记录。

处理完后自动关闭游标。】


declare cursor myCur

is

       select * from emp;

begin

 for varObj in myCur

   loop

     dbms_output.put_line(myCur%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);

   end loop;

end;

/          




8.怎样更新和删除显示游标中的记录?

①UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。

 要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,

 所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,

 不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

  在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。

  如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。

②使用更新或删除:

 ⑴声明更新或删除显示游标:

  Cursor 游标名IS  SELECT 语句   For Update [ Of  更新列列名];

  Cursor 游标名IS  SELECT 语句   For Delete [ Of  更新列列名];

 ⑵使用显示游标当前记录来更新或删除:

  Update  表名   SET   更新语句  Where   Current  Of   游标名;

  Delete  From  表名   Where   Current  Of   游标名;


eg1.更新显示游标记录

declare cursor myCur

is

   select job from emp for update;

   vjob emp.job%type;

   rsal emp.sal%type;

begin

 open myCur;

 loop

   fetch myCur into vjob;

   exit when myCur%Notfound;

case(vjob)

        when 'ANALYST' then rsal := 0.1;

        when 'CLERK' then rsal := 0.2;

        when 'MANAGER' then rsal := 0.3;

        else

          rsal := 0.5;

end case;

    update  emp set sal = sal + rsal where current of myCur;

    end loop;

end;

/  


eg2.删除显示游标记录

-- bak table of emp;

create table empa as select * from emp;


declare cursor myCur

is

   select sal from empa for update;

   rsal empa.sal%type;

begin

 open myCur;

 loop

   fetch myCur into rsal;

exit when myCur%Notfound;

    if rsal < 800 then

      delete from empa where current of myCur;

    end if;      

    end loop;

    close myCur;

end;

/  



9.什么是带参数的显示游标?

①与存储过程和函数相似,可以将参数传递给游标并在查询中使用。

 参数只定义数据类型,没有大小(所有Oracle中的形参只定义数据类型,不指定大小)。

 与过程不同的是,游标只能接受传递的值,而不能返回值。

   可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。

 游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。


②使用带参数的显示游标

 ⑴声明带参数的显示游标:

  CURSOR 游标名  [(parameter[,parameter],...)]    IS   Select语句;;


  参数形式:1,参数名   数据类型  

2,参数名   数据类型  DEFAULT  默认值

eg1.

declare cursor myCur(pSal Number default 800)

is

       select * from empa where sal > pSal;

varA myCur%rowtype;

begin

    open myCur;

    loop

      fetch myCur into varA;

      exit when myCur%notfound;

      DBMS_OUTPUT.PUT_LINE(MyCur%RowCount||'    '||varA.empno||'    '||varA.ename||'  '||varA.sal);  

    end loop;

    close myCur;

end;

/


参考地址: http://www.2cto.com/database/201203/122387.html

http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html

======================2013-12-16-REF-CURSOR===================================

1,什么是REF游标 ?

动态关联结果集的临时对象。即在运行的时候动态决定执行查询。


2,REF 游标 有什么作用?

实现在程序间传递结果集的功能,利用REF CURSOR也可以实现BULK SQL,从而提高SQL性能。



3,静态游标和REF 游标的区别是什么?

①静态游标是静态定义,REF 游标是动态关联;

②使用REF 游标需REF 游标变量。

③REF 游标能做为参数进行传递,而静态游标是不可能的。


4,什么是REF 游标变量?

REF游标变量是一种 引用REF游标类型  的变量,指向动态关联的结果集。



5,怎么使用  REF游标 ?


①声明REF 游标类型,确定REF 游标类型;

 ⑴强类型REF游标:指定retrun type,REF 游标变量的类型必须和return type一致。

  语法:Type   REF游标名   IS   Ref Cursor Return  结果集返回记录类型;

 ⑵弱类型REF游标:不指定return type,能和任何类型的CURSOR变量匹配,用于获取任何结果集。

  语法:Type   REF游标名   IS   Ref Cursor;



②声明Ref 游标类型变量;

 语法:变量名  已声明Ref 游标类型;


③打开REF游标,关联结果集 ;

 语法:Open   Ref 游标类型变量   For   查询语句返回结果集;


④获取记录,操作记录;

 语法:Fetch    REF游标名InTo   临时记录类型变量或属性类型变量列表;


⑤关闭游标,完全释放资源;

 语法:Close   REF游标名;


eg1.弱类型的例子:

Declare

 Type MyRefCur  IS  Ref  Cursor;

 vRefCur MyRefCur;

 vtemp  varchar2(20);

Begin

   Open vRefCur  For Select  ename  from emp Where empno = 7499;

   fetch vRefCur into vtemp;

   dbms_output.put_line(vtemp);

   Close  vRefCur;

End;



     本文转自韩立伟 51CTO博客,原文链接:http://blog.51cto.com/hanchaohan/1341071,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章