Oracle SQL的cursor理解

      在 PL/SQL 开发中，会涉及到很多 cursor 的处理操作，这个 cursor 通常被翻译成游标。游标又分成显式和隐式两类。使用 update delete  insert 操作，都是隐式游标；而使用 select 操作，可能是隐式游标也可能是显示游标。

隐式游标中大写的" SQL" 标识，如 SQL%ROWCOUNT 表示隐式游标有几行记录；显式游标由开发者自定义，也可以使用如 C1%ROWCOUNT 表示游标 C1 有几行记录。通常显式游标都是使用 OPEN 方式打开的。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1723165 )

 

使用显式定义的游标使用 OPEN 方式打开，它的使用方法如下例所示：

 

set serveroutput on

declare

  cursor c1 is

    select rownum ,table_name from user_tables where rownum < 10 ;

  v_rownum number ;

  v_tablename varchar2 ( 256 );

begin

  open c1;

  dbms_output.put_line( ' 游标当前行数 1:' || c1% rowcount );

  loop

     fetch c1 into v_rownum,v_tablename;

    exit when c1% notfound ;

    dbms_output.put_line( ' 游标当前行 :' || c1% rowcount || ' table_name:' ||v_tablename);

  end loop ;

  dbms_output.put_line( ' 游标当前行数 2:' || c1% rowcount );

  close c1;

end ; 

 

 

在 SQLPUS 中执行，得到如下结果。

 

游标当前行数 1:0

游标当前行 :1 table_name:T_SS

游标当前行 :2 table_name:T_TARGET

游标当前行 :3 table_name:T_SS_NORMAL

游标当前行 :4 table_name:T_MYSTAT

游标当前行 :5 table_name:T_WXL_GETDATA_LOG

游标当前行 :6 table_name:ALL_OBJ

游标当前行 :7 table_name:DBA_OBJ

游标当前行 :8 table_name:T_RPT_XXX

游标当前行 :9 table_name:CMS_DOC_BODY

游标当前行数 2:9 

 

 

分析一下，游标 C1 是显式定义，可以看作是一个查询语句的变量。其实它是指向这个查询语句得到的内存区域的指针。

在打开游标时，就是定位指针在查询结果集内存区域的位置，开始还是空值。使用 fetch into 操作依次移动指针定义结果集每一行记录，直到 C1%NOTFOUND 属性为 true 而退出循环。 C1%NOTFOUND 表示游标 C1 已经到最后一行了，不能再向下移动，无法找到更多的记录了。游标最后关闭，所有的显式游标再打开后都要关闭。

另外再深入说明一下，这个结果集不是真正意义上的结果集内存缓存，它还是从各个相关数据块中查询出来的。每一次执行，就遍历一次各个数据块。所以你会在执行统计结果中看到稳定的内存读数字。所以，这个结果集也是一个抽象的逻辑概念。（注，这段是个人理解，未必准确。）

 

使用隐式游标多数发生在 update,delete ,insert 操作中，它操作的也是一个结果集。如我们经常需要知道 update 某个表的记录数的结果。使用隐式游标的 SQL%ROWCOUNT 功能可能非常快捷地知道它。

 

 

set serveroutput on

declare

begin

  update all_obj set created = sysdate where object_type = 'TABLE' ;

  dbms_output.put_line( ' 更新的记录数 :' || sql % rowcount );

  commit ;

  delete from   all_obj where object_type = 'CLUSTER' ;

  dbms_output.put_line( ' 删除的记录数 :' || sql % rowcount );

  commit ;

  insert into all_obj  select * from all_obj where object_type = 'TABLE' ;

  dbms_output.put_line( ' 插入的记录数 :' || sql % rowcount );

  commit ;

end ;

/

  

 

   执行结果如下：

 

更新的记录数 :11940

删除的记录数 :11

插入的记录数 :11940

 
PL/SQL procedure successfully completed 

 

 

使用 SQL 隐式游标可以得到要 update,delete,insert 这些操作过程中结果集的记录数。隐式游标不需要开发打开和关闭。这些操作也是在一个结果集上进行操作，使用游标遍历结果集中的每一行记录。这样，游标就可以为一个指针。

 

 

 

       看一下别人对游标的定义吧。

 

 

 

Oracle 官方文档 Oracle® Database Concepts 11g Release 2 中这么说的：

A cursor is a name or  handle to a specific private SQL area.

游标是一个指向私有 SQL 区域的名字或者句柄。因为游标和私有 SQL 区域紧密相关，这两个名词有时候互换使用。

在开发过程中看到的游标指向的结果集，其实是私有 SQL 区域中语句执行的结果集。游标仅仅是这个结果集生成的方法。

私有 SQL 区域在专用服务模式下位于 PGA 中。 PGA 的组成分别为 SQL work areas 和 session memory 及 private SQL area 。

A work area is a private allocation of PGA memory used for memory-intensive operations. For example, a sort operator uses the sort area to sort a set of rows. Similarly, a  hash join  operator uses a hash area to build a  hash table  from its left input, whereas a  bitmap merge  uses the bitmap merge area to merge data retrieves from scans of multiple bitmap indexes.

The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. 

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store  bind variable  values, query execution state information, and query execution work areas.

 

私有 SQL 区域持有一个分析过的 SQL 语句和其他用于进程处理的会话指定的信息。当一个服务器进程执行一个 SQL 或 PL/SQL 代码事，这个进程使用私有 SQL 区域去保存绑定变量值，查询状态信息，和查询工作区域。

私有 SQL 区域分成下列两个区域：

运行时区域（ run-time area ）

This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a  full table scan .

Oracle Database creates the run-time area as the first step of an execute request. For  DML  statements, the run-time area is freed when the SQL statement is closed.