关于ORACLE游标的使用讲解

1，什么是游标？

 ①从表中检索出结果集，从中每次指向一条记录进行交互的机制。

 ②关系中的操作是在完整的行集合上执行的。

  由select 语句返回的行集合包括满足该语句的where 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。

     应用程序，尤其是互动和在线应用程序，把完整的结果集作为一个单元处理并不总是有效的。

     这些应用程序需要一种机制来一次处理一行或连续的几行。而游标是对提供这一机制的结果集的扩展。

     游标是通过游标库来实现的。游标库是常常作为数据库或数据访问api 的一部分而得以实现的软件，

     用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，

     以及是否能够在结果集中向前和/或向后移动（可滚动性）。

     游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。

     换句话说，游标从概念上讲基于数据库的表返回结果集。

     由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。

2，游标有什么作用？

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

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

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

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

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

3，为什么避免使用游标？

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

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

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

4，oracle游标的类型？

 ①静态游标：结果集已经确实（静态定义）的游标。分为隐式和显示游标。

  ⑴隐式游标：所有dml语句为隐式游标，通过隐式游标属性可以获取sql语句信息。

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

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

5，oracle游标的状态有哪些，怎么使用游标属性？

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

  %found ：fetch语句（获取记录）执行情况true or false。

  %notfound : 最后一条记录是否提取出true or false。

  %isopen ： 游标是否打开true or false。

  %rowcount ：游标当前提取的行数 。

 ②使用游标的属性。

  例子：/* conn scott/tiger */

  begin

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

   if  sql%found  then

    dbms_output.put_line('已经更新！');

   else

    dbms_output.put_line('更新失败！');

   end  if；

  end；

6，如何使用显示游标,？如何遍历循环游标？

  ①使用显示游标

   ⑴声明游标：划分存储区域，注意此时并没有执行select 语句。

    cursor 游标名( 参数 列表)   [返回值类型]   is   select 语句；

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

    open 游标名( 参数 列表);

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

    fetch  游标名into  临时记录或属性类型变量；

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

    close  游标名；

 ②遍历循环游标

  ⑴for 循环游标

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

     for  变量名  in  游标名 

     loop

      数据处理语句；

     end loop；

   ⑵loop循环游标

    loop

     fetch  游标名into  临时记录或属性类型变量；

     exit  when   游标名%notfound；

    end loop；

  例子1：

  /* conn scott/tiger */

   declare

     cursor mycur is select empno,ename,sal from emp;

     vna varchar2(10);

     vno number(4);

     vsal number(7,2);

  begin

     open mycur;

     fetch mycur into vno,vna,vsal;

     dbms_output.put_line(vno||'    '||vna||'    '||vsal);

     close mycur;

  end;

  /

 例子2：使用loop遍历游标。

 /* conn scott/tiger */

  declare

     cursor mycur is select ename,job,sal,empno from emp;

     vare mycur%rowtype;

  begin

     if mycur%isopen = false then

        open mycur;

       dbms_output.put_line('opening...');

     end if;

     loop

        fetch mycur into vare;

        exit when mycur%notfound;

        dbms_output.put_line(mycur%rowcount||'    '||vare.empno||'    '||vare.ename||'    '||vare.sal);

     end loop;

     if mycur%isopen then

        close mycur;

        dbms_output.put_line('closing...');

     end if;

  end;

  /

  例子3：使用for循环遍历游标,

  /* conn scott/tiger */

  declare

     cursor mycur is select * from emp;

  begin

     for vara in mycur

      loop

         dbms_output.put_line(mycur%rowcount||'    '||vara.empno||'    '||vara.ename||'  '||vara.sal);

      end loop;

  end;

  /

7，怎样更新和删除显示游标中的记录？

 ①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   游标名；

   例子1:更新显示游标记录

   /*conn scott/tiger*/

   declare

     cursor mycur is select job from emp for update;

       vjob empa.job%type;

       rsal empa.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;

    /

    例子2：删除显示游标记录

    /*conn scott/tiger

    crate table  empa  select * from scott.emp;

    */

    declare

      cursor mycursor  select   job  from  empa  for  update;

      vsal   emp.sal%type;

    begin

      loop

       fetch  mycursor  into  vsal;

       exit  when  mycursor%notfound;

       if   vsal < 800 then

        delete  from empa  where  current of   mycursor;

       end  if;  

      end    loop;

    end;/

8，什么是带参数的显示游标？

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

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

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

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

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

 ②使用带参数的显示游标

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

   cursor 游标名  [(parameter[,parameter],...)]    is   select语句；

   参数形式：1，参数名   数据类型  

       2，参数名   数据类型  default  默认值

      

   例子：

    /*conn scott/tiger

    crate table  empa  select * from scott.emp;

    */

    declare

      cursor mycursor(psal  number  default  800)  select job  from  empa  where  sal >  psal ;

      vara  mycursor%rowtype;

    begin

      loop

       fetch  mycursor  into  vara;

       exit  when  mycursor%notfound;

       dbms_output.put_line(mycursor%rowcount||'    '||vara.empno||'    '||vara.ename||'  '||vara.sal); 

      end    loop;

    end;/

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   查询语句返回结果集；

 ④获取记录，操作记录；

  语法：fatch    ref游标名into   临时记录类型变量或属性类型变量列表；

 ⑤关闭游标，完全释放资源；

  语法：close   ref游标名；

 例子：强类型ref游标

 /*conn scott/tiger*/

 declare

  type myrefcura is  ref cursor return emp%rowtype;

  type myrefcurb is  ref cursor return emp.ename%type;

  vrefcura  myrefcura;

  vrefcurb  myrefcurb;

  vtempa  vrefcura%rowtype;

  vtempb  vrefcurb.ename%type;

 begin

  open  vrefcura  for select  *  from   emp   where  sal > 2000;

  loop

   fetch  vrefcura into  vtempa;

   exit  when  vrefcura%notfound;

   dbms_output.put_line(vrefcura%rowcount||'  '|| vtempa.eno||'  '||vtempa.ename ||'  '||vtempa.sal)

  end loop;

  close vrefcura;

  dbms_output.put_line('-------------------------------------------------------------------------------------------------------');

  open  vrefcurb  for select  ename  from   emp   where  sal > 2000;

  loop

   fatch  vrefcurb into  vtempb;

   exit  when  vrefcurb%notfound;

   dbms_output.put_line(vrefcurb%rowcount||'  '||vtempb)

  end loop;

  close vrefcurb;

  dbms_output.put_line('-------------------------------------------------------------------------------------------------------');  

  open  vrefcura  for select  *  from   emp   where  job = 'clerk';

  loop

   fatch  vrefcura into  vtempa;

   exit  when  vrefcura%notfound;

   dbms_output.put_line(vrefcura%rowcount||'  '|| vtempa.eno||'  '||vtempa.ename ||'  '||vtempa.sal)

  end loop;

  close vrefcura;

 end;

 例子：弱类型ref游标

 /*conn scott/tiger*/

 declare

  type myrefcur  is  ref  cursor;

  vrefcur myrefcur;

  vtemp  vrefcur%rowtype;

 begin

  case(&n)

   when  1 then open vrefcur  for select   *   from emp;

   when  2 then open vrefcur  for select   *   from dept;

   else

    open vrefcur  for select   eno,  ename  from emp where job = 'clerk';

  end case;

  close  vrefcur;

 end;