oracle中游标（Cursor）的详解

概述

也许大家对数据库中游标都不陌生，但对于其概念可能有时又会有些模糊，游标到底是什么？
为了使大家对游标有个清晰的认识，本文将介绍Oracle中游标（curosr）相关的知识。

游标的概念

一般来讲，游标包含着两种不同的概念：
程序中的游标（Program Cursor）和Oracle中的游标（Oracle Curosr）。

程序中的游标（Program Cursor）：

在最终用户程序（Client Application）中，游标（curosr）通常指和SQL语句关联的一个数据结构,用于关联本地数据和存储存在数据库库缓存中的可执行语句等信息。如在PL/SQL或Precompilers产品中对SQL语句的声明等。

例如：以下SQL语句，在程序中都会生成一个程序游标（Program Cursor）。

显式的游标：（显式定义一个游标）
EXEC SQL DECLARE SEL_CURS CURSOR FOR...

隐式的游标：（没有明确定义游标，但会由内部自动生成一个游标）
EXEC SQL INSERT...
Oracle数据库中游标：

包含了【解析过的语句】以及【在处理该语句时需要使用的其它信息】的内存区域的句柄（handle）或者名称。即:游标指SQL文在执行时使用的区域的本身或其名称。
一般Oracle在执行SQL时会自动地分配和释放游标。
Oracle中的游标从大类别上还可以分为私有游标（private cursor）和共享游标（shared cursor）。

私有游标（private cursor）:保存在SQL语句执行的用户会话进程（UGA）中，包含着指向相关SQL语句的共享游标（shared cursor）地址的数据。（Client端）

共享游标（shared cursor） :保存在库缓存（Library cache）中的，包含SQL语句的文本以及执行计划等信息的数据集合。（Server端）
对于游标的总体概况，可以参考下图。

本文主要介绍Oracle数据库中游标相关内容。

SQL文执行和游标

以下是SQL语句的大概处理流程：

客户端应用程序：

0. 查看是否有通过PL/SQL或Precompilers产品中的功能保存着共享游标的地址，如果有则使用（程序端的游标缓存）。
服务器进程：

1. 查看是否有打开着的私有游标（private cursor），如果有则使用。（Client端）

2. 如果1.中无打开着的私有游标，并且设定了SESSION_CACHED_CURSORS*参数的话，查看会话中是否有缓存着的私有游标（private cursor），如果有则使用（Client端的游标缓存）（软软解析）

3. 如果2.中没有找到缓存着的私有游标，通过把SQL文转换为Hash值，来查看在库缓存（Library cache）中是否有相同SQL文的共享游标，如果有则使用以前的解析结果并再打开该游标。（软解析）

4. 如果3.中找不到相同SQL文的共享游标，则装载SQL文到库缓存（Library cache），重新进行解析。（硬解析）

关于SESSION_CACHED_CURSORS的设定
参数SESSION_CACHED_CURSORS用来控制在每个会话中能够缓存的游标个数。

你可以通过指定vsesstat或V SYSSTAT视图的name = ‘session cursor cache count’条件
来查看会话缓存的使用状况。
还可以通过v$open_cursor 来查看会话缓存的具体SQL_ID。

例如：

---监视会话缓存状况
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count';

---监视会话缓存的SQL

select c.user_name, c.sid, sql.sql_text
from   v$open_cursor c, v$sql sql
where  c.sql_id=sql.sql_id ；
共享游标（shared cursor）
共享游标（shared cursor）又可以分为父游标（Parent cursor）和子游标（Child cursor）。

父游标（Parent cursor）：主要包SQL 或PL/SQL 语句的文本内容等和特定的SCHEMA无关的信息。
子游标（Child cursor） ：主要包括SCHEMA、执行计划等信息。
所以对于一条SQL文可能会存在多个子游标。
下面我们介绍一下关于游标相关的一些常见问题和知识。

v$sql和sqlarea 视图
v$sql和sqlarea视图在某种意义上，可以看成父子关系。

v$sqlarea ：保存的是父游标的sql信息；列VERSION_COUNT，表示该父游标下的子游标个数。
v$sql     ：保存的是子游标的sql的信息；列CHILD_NUMBER，表示子游标的编号。
Database Reference
V$SQLAREA
v$sql

v$sql_shared_cursor视图
子游标增加的原因有很多，可能是一些正常的动作，也可能是由于Oracle的Bug导致的不必要的子游标增加。

通常情况下我们可以通过v$sql_shared_cursor视图来查看子游标增加(游标不能共享)的原因。

在v$sql_shared_cursor视图中，针对SQL文子游标产生的各种原因都有一列与子对应,详细可以参考下表：

Database Reference
V$SQL_SHARED_CURSOR

Column                     Description
SQL_ID                     SQL identifier
ADDRESS                    Address of the parent cursor
                           （Join to v$sqlarea.ADDRESS Version >=10g）  
KGLHDPAR                   （Join to v$sqlarea.ADDRESS Version <=9.2）  
CHILD_ADDRESS              Address of the child cursor
UNBOUND_CURSOR             (Y|N) The existing child cursor was not fully built
                           (in other words, it was not optimized)
CHILD_NUMBER               Child number
SQL_TYPE_MISMATCH          (Y|N) The SQL type does not match the existing child cursor
OPTIMIZER_MISMATCH         (Y|N) The optimizer environment does not match the existing child cursor
OUTLINE_MISMATCH           (Y|N) The outlines do not match the existing child cursor
STATS_ROW_MISMATCH         (Y|N) The existing statistics do not match the existing child cursor
LITERAL_MISMATCH           (Y|N) Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH 
EXPLAIN_PLAN_CURSOR        (Y|N) The child cursor is an explain plan cursor and should not be shared
FORCE_HARD_PARSE           (Y|N) For internal use
BUFFERED_DML_MISMATCH      (Y|N) Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH          (Y|N) PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH        (Y|N) Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH          (Y|N) The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)
TYPECHECK_MISMATCH         (Y|N) The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH         (Y|N) Authorization/translation check failed for the existing child cursor
BIND_MISMATCH               (Y|N) The bind metadata does not match the existing child cursor
DESCRIBE_MISMATCH          (Y|N) The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH          (Y|N) The language handle does not match the existing child cursor
TRANSLATION_MISMATCH       (Y|N) The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH 
INSUFF_PRIVS              (Y|N) Insufficient privileges on objects referenced by the existing child cursor
BIND_EQUIV_FAILURE        (Y|N) The bind value's selectivity does not match that used to optimize the existing child cursor
INSUFF_PRIVS_REM          (Y|N) Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH     (Y|N) The remote base objects of the existing child cursor do not match
LOGMINER_SESSION_MISMATCH   (Y|N) LogMiner Session parameters mismatch
INCOMP_LTRL_MISMATCH      (Y|N) Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.
OVERLAP_TIME_MISMATCH     (Y|N) Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME
SQL_REDIRECT_MISMATCH  
MV_QUERY_GEN_MISMATCH      (Y|N) Internal, used to force a hard-parse when analyzing materialized view queries
EDITION_MISMATCH           (Y|N) Cursor edition mismatch
USER_BIND_PEEK_MISMATCH    (Y|N) Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan
TYPCHK_DEP_MISMATCH        (Y|N) Cursor has typecheck dependencies
NO_TRIGGER_MISMATCH        (Y|N) Cursor and child have no trigger mismatch
FLASHBACK_CURSOR           (Y|N) Cursor non-shareability due to flashback
LITREP_COMP_MISMATCH       (Y|N) Mismatch in use of literal replacement
ANYDATA_TRANSFORMATION     (Y|N) Is criteria for opaque type transformation and does not match
PDDL_ENV_MISMATCH          (Y|N) Environment setting mismatch for parallel DDL cursor (that is, one or more of the following parameter values have changed: PARALLEL_EXECUTION_ENABLED, PARALLEL_DDL_MODE, PARALLEL_DDL_FORCED_DEGREE, or PARALLEL_DDL_FORCED_INSTANCES)
INCOMPLETE_CURSOR          (Y|N) Cursor is incomplete: typecheck heap came from call memory
TOP_LEVEL_RPI_CURSOR       (Y|N) Is top level RPI cursor
DIFFERENT_LONG_LENGTH      (Y|N) Value of LONG does not match
LOGICAL_STANDBY_APPLY      (Y|N) Logical standby apply context does not match
DIFF_CALL_DURN             (Y|N) If Slave SQL cursor/single call
BIND_UACS_DIFF             (Y|N) One cursor has bind UACs and one does not
PLSQL_CMP_SWITCHS_DIFF     (Y|N) PL/SQL anonymous block compiled with different PL/SQL compiler switches
CURSOR_PARTS_MISMATCH      (Y|N) Cursor was compiled with subexecution (cursor parts were executed)
STB_OBJECT_MISMATCH        (Y|N) STB has come into existence since cursor was compiled
ROW_SHIP_MISMATCH  
CROSSEDITION_TRIGGER_MISMATCH   (Y|N) The set of crossedition triggers to execute might differ
PQ_SLAVE_MISMATCH         (Y|N) Top-level slave decides not to share cursor
TOP_LEVEL_DDL_MISMATCH    (Y|N) Is top-level DDL cursor
MULTI_PX_MISMATCH         (Y|N) Cursor has multiple parallelizers and is slave-compiled
BIND_PEEKED_PQ_MISMATCH   (Y|N) Cursor based around bind peeked values
MV_REWRITE_MISMATCH       (Y|N) Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view
ROLL_INVALID_MISMATCH     (Y|N) Marked for rolling invalidation and invalidation window exceeded
OPTIMIZER_MODE_MISMATCH   (Y|N) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)
PX_MISMATCH (Y|N) Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.
MV_STALEOBJ_MISMATCH      (Y|N) Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built
FLASHBACK_TABLE_MISMATCH    (Y|N) Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred
PLSQL_DEBUG               (Y|N) Value of the PLSQL_DEBUG parameter for the current session does not match the value used to build the cursor
LOAD_OPTIMIZER_STATS      (Y|N) A hard parse is forced in order to initialize extended cursor sharing
ACL_MISMATCH              (Y|N) Cached ACL evaluation result stored in the child cursor is not valid for the current session or user
FLASHBACK_ARCHIVE_MISMATCH  (Y|N) Value of the FLASHBACK_DATA_ARCHIVE_INTERNAL_CURSOR parameter for the current session does not match the value used to build the cursor
LOCK_USER_SCHEMA_FAILED    (Y|N) User or schema used to build the cursor no longer exists. Note: This sharing criterion is deprecated
REMOTE_MAPPING_MISMATCH    (Y|N) Reloaded cursor was previously remote-mapped and is currently not remote-mapped. Therefore, the cursor needs to be reparsed.
LOAD_RUNTIME_HEAP_FAILED    (Y|N) Loading of runtime heap for the new cursor (or reload of aged out cursor) failed
HASH_MATCH_FAILED           (Y|N) No existing child cursors have the unsafe literal bind hash values required by the current cursor
PURGED_CURSOR               (Y|N) Child cursor is marked for purging
BIND_LENGTH_UPGRADEABLE     (Y|N) Bind length(s) required for the current cursor are longer than the bind length(s) used to build the child cursor
USE_FEEDBACK_STATS          (Y|N) A hard parse is forced so that the optimizer can reoptimize the query with improved cardinality estimates
REASON                      Child number, id, and reason the cursor is not shared. The content of this column is structured using XML.
父游标管理子游标的最大数
在以前的版本中一个父游标能够管理的最大的子游标是32768 个，当子游标的个数大于这个数时，数据库会报ORA-600[17059] 错误。

但是通过Bug 8946311，Oracle把一个父游标能够管理的最大的子游标增加为65535个。Bug 8946311在以下的版本中得到了修正。

Bug 8946311 Fixed:

12.1.0.1 (Base Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 12 for Exadata Database
11.1.0.7.10 Database Patch Set Update
11.2.0.1 Patch 9 on Windows Platforms
11.1.0.7 Patch 43 on Windows Platforms
即版本大于这些版本的数据库，一个父游标能够管理的最大的子游标增加为65535个。

限制子游标的个数
在11.1.0.7 和 11.2以后的版本中，为了防止产生过多的子游标，增加了子游标个数限定的功能。即：当子游标个数超过限定数，该功能会把父游标无效话，重新生成一个父游标。
这个子游标个数限定的功能可以通过以下的方法进行设置：

- 11.1.0.7
_cursor_features_enabled=18
event =  "106001 trace name context forever, level XXX  "

- 11.2.0.1
_cursor_features_enabled=34
event = "106001 trace name context forever, level XXX "

- 11.2.0.2
_cursor_features_enabled=1026
event =  "106001 trace name context forever, level XXX "

- 11.2.0.3以后
_cursor_obsolete_threshold=XXX

* 其中XXX 为限定的子游标个数
11.2.0.3以后的版本限定功能默认有效并且默认值如下：

11.2.0.3: 100
11.2.0.4以后: 1024
游标个数限定功能引起的Dump文件生成
在12.1.0.2的环境中，当由于游标个数限定的功能导致游标无效时，会根据设置在父游标第N次无效时，产生[Cursor Obsoletion Dump]的日志文件，以便以后查找游标不能共享的原因。

----- Cursor Obsoletion Dump sql_id=123456789 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FEB8AD45CB0 phd=00007FEB8AD45CB0
对于这个功能增强，主要通过隐含参数_kks_obsolete_dump_threshold来控制在第几次无效时产生Dump文件。

SQL> select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value"
from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_kks_obsolete_dump_threshold%';
  2 
Parameter                      Description                                                                      Value
------------------------------ -------------------------------------------------------------------------------- ----------------------------------------
_kks_obsolete_dump_threshold   Number of parent cursor obsoletions before dumping cursor                        1
_kks_obsolete_dump_threshold的设定范围为0~8；
设为0时即该功能无效。
设为N时即：会在父游标第N次无效时，产生一个[Cursor Obsoletion Dump]的日志文件，以便以后查找游标不能共享的原因。

实际操作和例子：
   (1)FOR循环游标 (常用的一种游标)

 --<1>定义游标
 --<2>定义游标变量
 --<3>使用for循环来使用这个游标

  --前向游标 只能往一个方向走
  --效率很高
      declare
        --类型定义
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定义一个游标变量
        ccrec cc%rowtype;
      begin
         --for循环
         for ccrec in cc loop
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
         end loop;      
      end;
   (2) fetch游标
     --使用的时候 必须要明确的打开和关闭
      declare
        --类型定义
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定义一个游标变量
        ccrec cc%rowtype;
      begin
        --打开游标
         open cc;
        --loop循环
         loop
            --提取一行数据到ccrec中
            fetch cc into ccrec;        
            --判断是否提取到值，没取到值就退出
            --取到值cc%notfound 是false
            --取不到值cc%notfound 是true
            exit when cc%notfound;
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);           
         end loop;
        --关闭游标
         close cc; 
      end;
  游标的属性4种
       %notfound  fetch是否提到数据 没有true 提到false
       %found      fetch是否提到数据 有true 没提到false
       %rowcount  已经取出的记录的条数
       %isopen    布尔值 游标是否打开
   (3)参数游标
 按部门编号的顺序输出部门经理的名字
     declare
       --部门
       cursor c1 is select deptno from dept;
       --参数游标c2,定义参数的时候
       --只能指定类型,不能指定长度 
       --参数只能出现在select语句=号的右侧
       cursor c2(no number,pjob varchar2) is select emp.* from emp
         where deptno = no and job=pjob;
       c1rec c1%rowtype;
       c2rec c2%rowtype;
       --定义变量的时候要指定长度
       v_job varchar2(20);
     begin
       --部门
        for c1rec in c1 loop
          --参数在游标中使用
          for c2rec in c2(c1rec.deptno,'MANAGER') loop
            dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);
          end loop;
        end loop;
     end;
  (4)引用游标/动态游标
       -- select语句是动态的
     declare
       --定义一个类型(ref cursor)弱类型   
       type cur is ref cursor;
         --强类型(返回的结果集有要求)
       type cur1 is ref cursor return emp%rowtype;
       --定义一个ref cursor类型的变量  
       cura cur;
       c1rec emp%rowtype;
       c2rec dept%rowtype;
     begin
  DBMS_output.put_line('输出员工')   ;      
       open cura for select * from emp;
       loop
         fetch cura into c1rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c1rec.ename)   ;
       end loop ;
  DBMS_output.put_line('输出部门')   ;
       open cura for select * from dept;
       loop
         fetch cura into c2rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c2rec.dname)   ;
       end loop; 
       close cura;
    end;