oracle游标的简单学习

转自：http://hanchaohan.blog.51cto.com/2996417/1341071

/*游标的简单学习*/

一.概念：

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;