欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

关于ORACLE游标的使用讲解

程序员文章站 2022-05-02 23:19:06
1,什么是游标?  ①从表中检索出结果集,从中每次指向一条记录进行交互的机制。  ②关系中的操作是在完整的行集合上执行的。   由select 语句返回的行集合包括满足...

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;