Oracle Cursor

节选自《基于Oracle的SQL优化》第三章

Library cache：主要作用是缓存刚刚执行过的sql语句和PL/SQL语句(如存储过程、函数、包、触发器)锁对应的执行计划、解析树、Pcode/Mcode等对象，当同样的SQL语句和PL/SQL语句再次被执行时，就可以例如已缓存再Library Cache中的那些相关对象而无需再次从头开始解析，这样就提高了这些SQL、PL/SQL语句重复执行的效率.

v$sqlarea查看父游标、v$sql查看子游标

任意一个目标SQL一定会同时对应两个Shared Cursor,其中一个Parent Cursor,另一个是Child Cursor,父游标存储sql文本，sql中真正可以被重用的解析树和执行计划则存储在子游标中。

--测试,同一对象SQL大小写不同，父游标不同；不同用户同一对象语句一样，父游标相同，子游标不同(用户不同，解析不一样)
SQL_TEXT                 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp         78bd3uh4a08av           1
SQL> 1
  1* select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%'
SQL> select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932        0
SQL> /
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932        0
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%';
SQL_TEXT                 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp         78bd3uh4a08av           1
select empno,ename from  EMP         7k63aycdqsfr1           1
SQL> select plan_hash_value,child_number from v$sql where sql_id='7k63aycdqsfr1';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932        0
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select emp%';
SQL_TEXT                 SQL_ID        VERSION_COUNT
---------------------------------------- ------------- -------------
select empno,ename from emp         78bd3uh4a08av           2
select empno,ename from  EMP         7k63aycdqsfr1           1
SQL> select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932        0
     3956160932        1

硬解析是指Oracle在执行SQL时，在库缓存中找不到可以重用的解析树和执行计划，而不得不重新开始解析并生成父游标和子游标.

硬解析可能会导致Shared Pool
Latch争用，导致库缓存相关latch.
理想情况下，oltp硬解析每秒控制在20以下

软解析是Library cache找到了匹配的父游标和子游标，并将子游标中的解析树和执行计划之家拿过来重用。

Oracle里的session Cursor

主要用于当前Session中解析和执行sql。缓存在PGA中。注意事项：

Session Cursor与Session是一一对应，不同的session之间没法共享，这是与Shared Cursor本质的区别。
SC是有生命周期的，每个SC在使用过程中都至少会经历一次Open Parse Bing Execute Fetch 和Close中的一个或多个阶段。用过的SC不一定会缓存在对于的Session 的PGA中，这取决于参数SESSION_CACHED_CURSORS的值是否大于0
SC也是以哈希表方式缓存再PGA中，访问机制与Shared Cursor一样。

Oracle11g 之前，在缓存Session Cursor的哈希表的对应Hash Bucket中，Oracle会存储目标SQL对应的Parent Cursor的库缓存对象句柄地址，这意味着Oracle已经建立了目标SQL的Session Cursor与其Parent Cursor之间的联系，即Oracle可以通过Session Cursor找到对应的Parent Cursor，金额日可以找到对应的子游标中目标sql的解析树和执行计划。

--dump session Cursor
alter session set events 'immediate trace name ERRORSTACK level 3';
--Bucket 的Hash Bucket中hdl句柄的值就是 目标sql父游标的库缓存对象句柄地址.
select sql_text,sql_id,version_count,executions,address from v$sqlarea where sql_text like 'select count(*) %';

一个Session Cursor只能对应一个Shared Cursor，而一个Shared Cursor却可以同时对应多个Session Cursor。

如果参数SESSION_CACHED_CURSORS等于0，那么session Cursor正常关闭，相同语句再次执行时，可以找到父游标，但无法找到Session Cursor，Oracle必须重新生成一个SC，sc会重新经历“硬解析”。

如果参数大于0，SC不会执行关闭操作，会将其标记为soft closed，同时将其缓存在当前session 的PGA中。这样当再次执行时，不用重新解析。这个过程叫“软软解析”。

不管是硬解析还是软解析，Oracle在解析和执行目标sql时，都会先去当前session的pga中查找匹配的SC,如果没有，就去库缓存找匹配的父游标，如果找不到，生成全新的，如果找到，生成SC并去匹配父游标.

Session Cursor相关参数说明

OPEN_CURSORS :单个session可以同时open状态并存SC的总数

show parameter open_cursors
--查看sid
select sid from v$mystat where rownum<=2;
--查看已经被缓存的SC总数
SELECT COUNT(*) FROM v$open_cursor where sid=;
--当前所有以open状态存在的SC总数
select name,value from v$sysstat where name='opened cursors current';

SESSION_CACHED_CURSORS :用于设定单个session中能够以Soft Closed状态并存的SC总数。即设定单个session能够缓存在PGA的SC总数

show parameter session_cached_cursors

注意：被缓存有一定条件，例如oracle11gR2,该SC所对应的SQL解析和执行的次数要超过3次。

--可以通过以下语句查看
select sql_text,cursor_type from v$open_cursor where user_name='' and sid= and sql_text like '';
--当执行3次时，游标类型是"DICTIONARY LOOKUP CURSOR CACHED",当执行4次后显示"SESSION CURSOR CACHED"

CURSOR_SPACE_FOR_TIME：11g之后已过时

当SC状态为执行时，Oracle会把该SQL所对应的Child Cursor给pin住，保证这个正在执行的sql的解析树和执行计划保留在库缓存中而不被age out出缓存。当上述参数为FALSE时，sql执行完毕，此时子游标可以不pin在库缓存中，释放了，所对应的解析树和执行计划被age out出缓存。如果有大量并发，可能会造成与库缓存相关的Latch争用。可以将上述参数设置为TRUE，但会造成Shred Pool空间紧张。

Session Cursor的种类和写法

隐式游标

常用的四个属性

SQL%FOUND属性

表示一条SQL语句被执行成功后受其影响而改变的记录数是否大于或等于1.适用于DML操作。在DML执行前，SQL%FOUND值是NULL,当这条DML语句被执行并且成功改变了一条或者一条以上记录时，又或者SELECT INFO 语句成功返回一条或多条记录时，SQL%FOUND值时TRUE,否则是FALSE。

declare
  dept_no number(4) := 50;
begin
  delete from dept_temp where deptno = dept_no;
  if sql%found then
   insert into dept_temp values (50,'Database','BEIJING');
  end if;
  commit;
 end;
/

在PL/SQL中使用SELECT INFO，当且仅当对于的select返回结果只有一条时Oracle才不会报错，返回0，报错”NO_DATA_FOUND”,返回多行报错”TOO_MANY_ROWS”

--eg
declare
  dept_name varchar2(14);
  vc_message varchar2(4000);
begin
  select dname info dept_name from dept_temp where deptno=50;
exception
  when no_data_found then
    dbms_output.put_line('No data found!');
    return;
  when too_many_rows then
    dbms_output.out_line('Too many rows!');
    return;
  when others then
    vc_message := 'E' ||'_'||sqlcode ||'_' || sqlerrm;
    dbms_output.put_line(vc_message);
    return;
end;
/
-- SQLCODE返回Oracle错误的序列号，SQLERRM返回相应的错误信息

SQL%NOTFOUND

表示一条SQL语句被执行成功后受其影响而改变的记录数是否为0.

在一条DML语句被执行前，该值时NULL，没有任何记录时是TRUE,否则为FALSE

SQL%ISOPEN

表示隐士游标是否处于open状态。对于隐式游标而言，永远是FALSE.

SQL%ROWCOUNT

表示一条SQL语句成功执行后受其影响而改变的记录的数量。

当DML语句联用时，表示该DML语句被执行后受其影响而改变的记录数。当一条语句执行后没有改变任何记录或者SELECT INFO没有任何返回记录时，值为0.

注意：当SELECT INFO返回超过1行时，报”TOO_MANY_ROWS”，在这种情况下，SQL%ROWCOUNT返回值是1，而不是实际查询到的记录数。

--表示被成功删除的记录数，再次执行会覆盖，不是叠加
declare 
  dept_no number(4) := 50;
begin
  delete from dept_temp where deptno = deptno=dept_no;
  dbms_output.put_line('Number of departments deleted: '|| to_char(sql%rowcount));
  commit;
 end;
/

显示游标

常用于PL/SQL中。四种属性如下，CURSORNAME 游标名

CURSORNAME%FOUND

当游标被open后，如果没有被fetch，值是NULL,被Fetch后，值是TRUE,直到全部Fetch。然后再执行fetch，该值变成FALSE.

--如果没被open，试图使用该属性，报错"INVALID_CURSOR"
declare
  cursor c1 select ename,sal from emp where rownum<=10;
  my_ename emp.ename%type;
  my_salary emp.sal%type; --继承emp.sal类型
begin
  open c1; --不打开，使用C1%FOUND报错
  loop
    fetch c1 into my_ename,my_salary;
    if c1%found then
      dbms_output.put_line('name= ' ||my_ename || ', salary = ' || my_salary);
    else
      exit;
    end if;
  end loop;
 close c1;
end;
/

CURSORNAME%NOTFOUND

表示指定的显示游标是否已经Fetch完毕了。当一个显示游标被open后，如果还一次都没被fetch，该值为null，被fetch后，该值是false，全部fetch后，为true。当一个显示游标未被open，使用该属性，会报错”INVALID_CURSOR”

--eg
declare
  cursor c1 is select ename,sal from emp where rownum<=11;
  my_ename emp.ename%type;
  my_salary emp.sal%type;
begin
  open c1;
  loop
  fetch c1 into my_ename,my_salary;
  if c1%notfound then
    exit;
  else
    dbms_output.put_line('name= ' || my_ename || ',salary=' ||my_salary);
   end if;
  end loop;
    close c1;
 end;
/

CURSORNAME%ISOPEN

表示指定的显示游标是否被open。主要用于close那些由于发生了Exception而导致有显示游标没有被正常关闭的情形。

--eg
declare
  cursor c1 is select ename,sal from emp where rownum<=11;
  my_ename emp.ename%type;
  my_salary emp.sal%type;
begin
  open c1;
  fetch c1 into my_ename,my_salary;
  dbms_output.put_line('name= ' || my_ename || ',salary=' ||my_salary);
  close c1;
 exception
   when others then
    if c1%open = true then
      close c1;
    end if;
    return;
end;
/

CURSORNAME%ROWCOUNT

表示指定显示游标到目前一共fetch了多少行记录.当没fetch，为0，当返回空，也是0，随着每一次fetch，值会递增。

--eg
declare
  cursor c1 is select ename from emp where rownum<=11;
  my_ename emp.ename%type;
  begin
  open c1;
  fetch c1 into my_ename;
  if c1%found then
    dbms_output.put_line(c1%rowcount ||' :' || 'name = '|| my_ename);
  else
    exit;
  end if;
 end loop;
 close c1;
end;
/
--显示游标标准用法是先open，在Fetch，然后用一个while循环处理数据，最后close。while内部循环，记得执行fetch操作跳到下一条记录，否则就是死循环.
create or replace procedure P_DEMO_EXPLICIT_CURSOR_STD is
  cursor c1 is select * from emp where rownum <= 11;
  emp_rec emp%rowtype; --跟所有表类型一致
begin
  open c1;
  fetch c1 into emp_rec;
  while (c1%found) loop
    dbms_output.put_line('name =' || emp_rec.ename || ', salary = ' || emp_rec.sal);
    fetch c1 into emp_rec;
   end loop;
   close c1;
exception
  when others then
    --o_parm:='E'||sqlcode || sqlerrm;
    rollback;
    returen;
end P_DEMO_EXPLICIT_CURSOR_STD;

参考游标

是Oracle数据库中最灵活的一种Session Cursor，它的灵活表现在如下三个方面。

参考游标的定义方式非常灵活，它可以有多种定义方式。
参考游标的open方式也非常灵活，它可以不喝某个固定的sql绑定在一起，并且每次open锁对应的sql语句都可以是不一样的（在Oracle11gR2,中，即便是同一条SQL语句，它所对应的参考游标也是可以随时重复open)
参考游标可以作为存储过程的输入参数和函数的输出参数

--参考游标的定义方式
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;
--第二种
type typ_result is record(ename emp.ename%type,sal emp_sal%type);
type typ_cur strong is ref cursor return type_result;
cur_emp typ_cur_stong;
--第三种
type typ_cur_weak is ref cursor;
cur_emp typ_cur_weak;
--第四种
cur_emp SYS_REFCURSOR;

参考游标可以作为存储过程参数

declare
  type typ_cur_emp is ref cursor return emp%rowtype;
  cur_emp typ_cur_emp;
  procedure process_emp_cv(emp_cv in typ_cur_emp) is 
    person emp%rowtype;
 begin
    dbms_output.put_line('----');
    loop
    fetch emp_cv into person;
    exit when emp_cv%notfound;
    dbms_output.put_line('name = '||person.ename);
 end loop;
end;
begin
  open cur_emp for select * from emp where rownum <11;
  process_emp_cv(cur_emp);
  close cur_emp;
  open cur_emp for select * from emp where ename like 'C%';
  process_emp_cv(cur_emp);
  close cur_emp;
 end;
 /
--参数游标被作为内嵌存储过程的输入 参数，游标两次open，和显示游标不同，这里多了一个"for"
--根据输入选择不同，执行不同sql
create package pck_refcursor_open_demo as
   type gencurtyp is ref cursor;
   procedure open_cv (generic_cv in out gencurtyp,choice int);
end pck_refcursor_open_demo;
/
create package body pck_refcursor_open_demo as
  procedure open_cv (generic_cv in out gencurtyp,choice int) is
begin
   if choice = 1 then
     open generic_cv for select * from emp;
   elsif choice = 2 then
     open generic_cv for select * from dept;
   elsif choice =3 then
     open generic_cv for select * from dept_tmp;
   end if;
 end;
end pck_refcursor_open_demo;
/

不能直接在一个包或包体的定义部分定义一个参考游标类型的Cursor变量，它只能在包或包体的存储过程中定义,或者作为该存储过程的输入、输出参数。

--eg:1/2 错误，3/4可以
create package pck_name as
   type gencurtyp is ref cursor;
   generic_cv gencurtyp;--1
   procedure open_cv(generic_cv in out gencurtyp --3,choice int);
end pck_name;
/
create package body pck_name as 
   generic_cv gencurtyp;--2
   procedure open_cv (generic_cv in out gencurtyp,choice int) is
   generic_cv gencurtyp; --4
begin
   ...
end;
/
--oracle11g 开始参考游标可以多次open，不用open一次关闭一次。后一次会覆盖前一次open 的参考游标。
--参考游标除了常规的一次fetch一条数据之外，哈可以次Fetch一批数据，批量获取，eg
declare
   type empcurtyp is ref cursor;
   type namelist is table of emp.ename%type;
   emp_cv empcurtyp;
   names namelist;
 begin
   open emp_cv for select ename from emp where rownum<=11;
   fetch emp_cv bulk collect into names;  --批量获取
   close emp_cv;
   for i in names.first .. names.last
   loop
      dbms_output.line('name = ' || names(i));
    end loop;
end;
/

参考游标和显示游标是可嵌套的

--eg:按照部门嵌套打印，参考游标emp_cur嵌套在显示游标c1中。
--显示输出结果
set serveroutput on
declare
    type emp_cur_typ is ref cursor;
    emp_cur emp_cur_typ;
    dept_name dept.dname%type;
    emp_name emp.ename%type;
    cursor c1 is select dname,cursor(select e.ename from emp e where e.deptno=d.deptno) employees
    from dept d;
begin
   open c1;
   loop
     fetch c1 into dept_name,emp_cur;
     exit when c1%notfound;
     dbms_output.put_line('department: '|| dept_name);
     loop
       fetch emp_cur into emp_name;
       exit when emp_cur%notfound;
       dbms_output.put_line('-- employee: '|| emp_name);
     end loop;
    end loop;
  close c1;
end;
/

【CURSOR】Oracle 游标 （cursor）知识梳理