关于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;

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐