ORACLE游标概念讲解.docx

ORACLE游标概念讲解.docx

《ORACLE游标概念讲解.docx》由会员分享，可在线阅读，更多相关《ORACLE游标概念讲解.docx（10页珍藏版）》请在冰豆网上搜索。

ORACLE游标概念讲解

ORACLE游标概念讲解

2012-03-08     0 个评论     

收藏    

我要投稿

1，什么是游标？

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

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

 由SELECT语句返回的行集合包括满足该语句的WHERE子句所列条件的所有行。

由该语句返回完整的行集合叫做结果集。

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

    这些应用程序需要一种机制来一次处理一行或连续的几行。

而游标是对提供这一机制的结果集的扩展。

    游标是通过游标库来实现的。

游标库是常常作为数据库系统或数据访问API的一部分而得以实现的软件，

    用来管理从数据源返回的数据的属性（结果集）。

这些属性包括并发管理、在结果集中的位置、返回的行数，

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

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

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

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

2，游标有什么作用？

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

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

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

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

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

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

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

”

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

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

4，Oracle游标的类型？

 ①静态游标：

结果集已经确实（静态定义）的游标。

分为隐式和显示游标。

 ⑴隐式游标：

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

 ⑵显示游标：

用户显示声明的游标，即指定结果集。

当查询返回结果超过一行时，就需要一个显式游标。

 ②REF游标：

动态关联结果集的临时对象。

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

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

 %Found：

Fetch语句（获取记录）执行情况TrueorFalse。

 %NotFound:

最后一条记录是否提取出TrueorFalse。

 %ISOpen：

游标是否打开TrueorFalse。

 %RowCount：

游标当前提取的行数。

 ②使用游标的属性。

 例子：

/*connscott/tiger*/

 Begin

  UpdateempSet SAL=SAL+0.1 WhereJOB='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

     数据处理语句；

    EndLoop；

  ⑵Loop循环游标

    。

。

。

   Loop

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

    Exit When  游标名%NotFound；

   End  Loop；

    。

。

。

 例子1：

 /*connscott/tiger*/

  Declare

    CursormyCurisselectempno,ename,salfromemp;

    vnavarchar2（10）;

    vnonumber（4）;

    vsalnumber（7,2）;

 Begin

    openmyCur;

    fetchmyCurintovno,vna,vsal;

    dbms_output.put_line（vno||'   '||vna||'   '||vsal）;

    closemyCur;

 End;

 /

 例子2：

使用loop遍历游标。

 /*connscott/tiger*/

 Declare

    CursormyCurisselectename,job,sal,empnofromemp;

    varEmyCur%rowType;

 Begin

    ifmyCur%isopen=falsethen

       openmyCur;

      dbms_output.put_line（'Opening...'）;

    endif;

    loop

       fetchmyCurintovarE;

       exitwhenmyCur%notfound;

       dbms_output.put_line（myCur%rowCount||'   '||vare.empno||'   '||vare.ename||'   '||vare.sal）;

    endloop;

    ifmyCur%isopenthen

       ClosemyCur;

       dbms_output.put_line（'Closing...'）;

    endif;

 End;

 /

 例子3：

使用For循环遍历游标,

 /*connscott/tiger*/

 Declare

    CursormyCurisselect*fromemp;

 Begin

    forvarAinmyCur

     loop

        dbms_output.put_line（myCur%rowCount||'   '||varA.empno||'   '||varA.ename||' '||varA.sal）;

     endloop;

 End;

 /

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

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

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

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

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

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

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

 ②使用更新或删除：

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

  Cursor游标名IS SELECT语句  ForUpdate[Of 更新列列名]；

  Cursor游标名IS SELECT语句  ForDelete[Of 更新列列名]；

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

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

  Delete From 表名  Where  Current Of  游标名；

  例子1:

更新显示游标记录

  /*connscott/tiger*/

  Declare

    CursormyCurisselectjobfromempforupdate;

      vjobempa.job%type;

      rsalempa.sal%type;

   Begin

      openmyCur;

      loop

         fetchmyCurintovjob;

         exitwhenmyCur%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;

         endcase;

       updateempsetsal=sal+rsalwherecurrentofmyCur;

      endloop;

   End;

   /

   例子2：

删除显示游标记录

   /*connscott/tiger

   Cratetable empa Select*fromscott.emp;

   */

   Declare

     CursorMyCursor Select  JOB From empa For Update;

     vSal  emp.Sal%TYPE;

   Begin

     Loop

      Fetch MyCursor InTo vSal;

      Exit When MyCursor%NotFound;

      If  vSal<800Then

       Delete Fromempa Where Cursor Of  MyCursor;

      End If;  

     End   Loop;

   End;/

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

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

 参数只定义数据类型，没有大小（所有Oracle中的形参只定义数据类型，不指定大小）。

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

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

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

 ②使用带参数的显示游标

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

  CURSOR游标名 [（parameter[,parameter],...）]   IS  Select语句；;

  参数形式：

1，参数名  数据类型  

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

  例子：

   /*connscott/tiger

   Cratetable empa Select*fromscott.emp;

   */

   Declare

     CursorMyCursor（pSal Number Default  800） Select  JOB From empaWhere 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;/

REFCURSOR

1，什么是REF游标？

 动态关联结果集的临时对象。

即在运行的时候动态决定执行查询。

2，REF游标有什么作用？

 实现在程序间传递结果集的功能，利用REFCURSOR也可以实现BULKSQL，从而提高SQL性能。

3，静态游标和REF游标的区别是什么？

 ①静态游标是静态定义，REF游标是动态关联；

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

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

4，什么是REF游标变量？

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

5，怎么使用 REF游标？

 ①声明REF游标类型，确定REF游标类型；

 ⑴强类型REF游标：

指定retruntype，REF游标变量的类型必须和returntype一致。

  语法：

Type  REF游标名  IS  RefCursorReturn 结果集返回记录类型；

 ⑵弱类型REF游标：

不指定returntype，能和任何类型的CURSOR变量匹配，用于获取任何结果集。

  语法：

Type  REF游标名  IS  RefCursor；

 ②声明Ref游标类型变量；

 语法：

变量名 已声明Ref游标类型；

 ③打开REF游标，关联结果集；

 语法：

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

 ④获取记录，操作记录；

 语法：

Fatch   REF游标名InTo  临时记录类型变量或属性类型变量列表；

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

 语法：

Close  REF游标名；

 例子：

强类型REF游标

 /*connscott/tiger*/

 Declare

 TypeMyRefCurAIS REFCURSORRETURNemp%RowType;

 TypeMyRefCurBIS REFCURSORRETURNemp.ename%Type;

 vRefCurA MyRefCurA;

 vRefCurB MyRefCurB;

 vTempA vRefCurA%RowType;

 vTempB vRefCurB.ename%Type;

 Begin

 Open vRefCurA ForSelect * from  emp  Where SAL>2000;

 Loop

  Fatch vRefCurAInTo vTempA;

  Exit When vRefCurA%NotFound;

  DBMS_OUTPUT.PUT_LINE（vRefCurA%RowCount||' '||vTempA.eno||' '||vTempA.ename||' '||vTempA.sal）

 EndLoop;

 ClosevRefCurA;

 DBMS_OUTPUT.PUT_LINE（'-------------------------------------------------------------------------------------------------------'）;

 Open vRefCurB ForSelect ename from  emp  Where SAL>2000;

 Loop

  Fatch vRefCurBInTo vTempB;

  Exit When vRefCurB%NotFound;

  DBMS_OUTPUT.PUT_LINE（vRefCurB%RowCount||' '||vTempB）

 EndLoop;

 ClosevRefCurB;

 DBMS_OUTPUT.PUT_LINE（'-------------------------------------------------------------------------------------------------------'）;  

 Open vRefCurA ForSelect * from  emp  Where JOB='CLERK';

 Loop

  Fatch vRefCurAInTo vTempA;

  Exit When vRefCurA%NotFound;

  DBMS_OUTPUT.PUT_LINE（vRefCurA%RowCount||' '||vTempA.eno||' '||vTempA.ename||' '||vTempA.sal）

 EndLoop;

 ClosevRefCurA;

 End;

 例子：

弱类型REF游标

 /*connscott/tiger*/

 Declare

 TypeMyRefCur IS Ref Cursor;

 vRefCurMyRefCur;

 vtemp vRefCur%RowType;

 Begin

 Case（&n）

  When 1ThenOpenvRefCur ForSelect  *  fromemp;

  When 2ThenOpenvRefCur ForSelect  *  fromdept;

  Else

   OpenvRefCur ForSelect  eno, ename fromempWhereJOB='CLERK';

 EndCase;

 Close vRefCur;

 End;