Oracle中游标Cursor使用实例

Oracle数据库中的cursor分为2中类型：shared cursor，session cursor

Shared cursor：库缓存，sga中一块内存区域

会缓存存储目标sql的sql文本、解析树、该sql所涉及的对象定义、该sql所使用的绑定变量类型和长度，以及改sql的执行计划等信息。

Shared cursor又分为：parent cursor，child cursor

  分别在V$SQLAREA,V$SQL，V$SQLAREA用于查看parent cursor,V$SQL用于查看child cursor。

在Oracle数据库里，任意一个目标sql一定会同时对应两个shared cursor。Parent cursor会存储该sql的文本，sql真正的可以被重用的解析树和执行计划则存储在child cursor。

SQL> select empno,ename from emp;

SQL>  select sql_text,sql_id,version_count from v$sqlarea where sql_text like'select empno,ename from emp%';

SQL>  select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';

PLAN_HASH_VALUE CHILD_NUMBER

--------------- ------------

    3956160932            0

针对不同的sql，都有不同的parent，child cursor

Oracle里的session cursor

 Session cursor：当前session解析和执行sql的载体，缓存在pga中

 Session cursor与session是一一对应的，不同的session之间的session cursor无法共享

 Session cursor是有生命周期，至少会经历一次open，parse，bind，execute，fetch和close

1 Oracle在解析和执行目标sql时，始终会先去当前session的pga中寻找是否有匹配的缓存session cursor

2 在当前session的pga中找不到匹配的缓存，Oracle就去缓存中寻找是否存在匹配的parent cursor，如果找不到，

Oracle就会生新生成一个session cursor和一对shared cursor。如果找到了匹配的parent cursor，Oracle会生成一个新的session cursor和child cursor（child cursor会被挂在之前找到的parent cursor上）。

3 如果session中没有找到匹配的session cursor，而找到了匹配的parent cursor和child cursor，Oracle会新生成一个session cursor，（软解析）

4 如果在session 中找到了匹配的session cursor，Oracle可以以重用找到匹配的session cursor，通过此可以直接访问到该sql的parent cursor（软软解析）

Session cursor的相关参数：

Open_cursors：用于设定单个session中同时能够以open状态并存的session cursor的个数

SQL> show parameter open_cursors;

NAME                                TYPE                  VALUE ------------------------------------ ---------------------- ------------------------------ open_cursors                        integer                300 SQL> select sid from v$mystat where rownum<2;

      SID ----------         88 SQL> select count(*) from v$open_cursor where sid=88;

  COUNT(*) ----------         3 SQL> select name,value from v$sysstat where name='opened cursors current';

NAME                                                                                                                VALUE opened cursors current                                                                                        47 session_cached_cursor：用于设定单个session中能够以soft closed状态并存的session cursors的总数 SQL> show parameter session_cached_cursors;

NAME                                TYPE                  VALUE ------------------------------------ ---------------------- ------------------------------ session_cached_cursors              integer                20

在Oracle 11gr2中，对应的sql解析和执行的次数要超过3次 session cursor才能够被缓存在pga中

Session cursor的种类和用法

1 隐式游标

SQL%NOTFOUND,SQL%FOUND,SQL%ISOPEN,SQL%ROWCOUNT

SQL%FOUND:每一条dml执行前，值为null，改变一条以上的记录，其值为true，否则为false

SQL%NOTFOUND:每一条sql语句被执行成功后受其影响而改变的记录数是否为0，执行前为null，没有返回或没有改变任何记录，其值为TRUE，否则为FALSE

SQL%ISOPEN:表示隐式游标是否处于open状态，对于隐式游标，其值永远是FALSE

SQL%ROWCOUNT:表示一条sql语句成功执行后受其影响而改变的记录的数量，代表最近一次执行的sql的sql%rowcount，没有任何记录的值0

2 显式游标

在plsql中，显式的打开，关闭

Cursorname%found,cursorname%notfound,isopen,rowcount

当游标一次都还没有fetch，%found的值为null，没有数据是false，否则ture

当显式游标还没有打开，%found会报错invaild coursor

declare  cursor c1 is select ename,sal from emp where rownum<11;  my_ename emp.ename%type;  my_sal emp.sal%type;  begin  open c1;  loop   fetch c1 into my_ename,my_sal;   if c1%found then   dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);   else   exit;   end if;   end loop;   close c1; end; --------------------------- declare  cursor c1 is select ename,sal from emp where rownum<11;  my_ename emp.ename%type;  my_sal emp.sal%type;  vc_message varchar2(4000);  begin  open c1;  loop   fetch c1 into my_ename,my_sal;   if c1%found then   dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);   else   exit;   end if;   end loop;   close c1; exception  when invalid_cursor then  dbms_output.put_Line('invaild_cursor');  return;  when others then   vc_message:=sqlcode||'_'||sqlerrm;   return; end; cursorname%isipen exception  when others then  if c1%isopen =true then   close c1;  end if;  return; end; cursorname%rowcount if c1%found then   dbms_output.put_LIne('name = '||my_ename|| ', sal ='||my_sal);     dbms_output.put_LIne(c1%rowcount ||'name = '||my_ename);   else   exit;   end if; name = SMITH, sal =800 1name = SMITH name = ALLEN, sal =1600 2name = ALLEN name = WARD, sal =1250 3name = WARD 当一个显式游标还没有被打开时，使用found，notfound，rowcount都会报错 当首次fecth为null时，found为false，notfount为true，rowcount=0

参考游标 ref cursor  可以作为procedure的输入参数和function的输出参数 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 typ_result; cur_emp type_cur_strong;

type typ_cur_weak is ref cursor cur_emp typ_cur_weak;

cur_emp sys_refcursor; 四种方式 分别定义同你一个参考游戏cur_emp

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; 不能直接在一个package或者package body的定义部分定义一个参考游标类型的cursor变量 create package pck_refcursor_open_dmep as type gentype is ref cursor; genri_cv gentype;---defalut 1  不正确 procedure open_cv(genri_cv in out gentype ----defult 3 ,choice int); and pck_refcursor_open_dmep;

create package body pck_refcursor_open_dmep as genri_cv gentype;--defalut 2  不正确 procedure open_cv(genri_cv in out gentype ,choice int); genri_cv gentype; ---default 4 begin null end ; end pck_refcursor_open_dmep;

批量fetch数据 declare type empcurtype is ref cursor return emp%rowtype; emp_cv empcurtype; emp_rec emp%rowtype; begin open emp_cv for select * from emp where rownum<11; loop fetch emp_cv into emp_rec; exit when emp_cv%notfound ; dbms_output.put_Line('name = '||emp_rec.ename); end loop; close emp_cv; end;

----- declare type empcurtype is ref cursor; type namelist is table of emp.ename%type; emp_cv empcurtype; 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.put_Line('name = '||names(i)); end loop; end; Oracle里的绑定变量 占位符 绑定变量的典型用法 SQL> var x number; SQL> var 1 number; SP2-0553: Illegal variable name "1". SQL> var xyz number; SQL> exec 😡 :=7369; PL/SQL procedure successfully completed. SQL> select ename from emp where empno=:x; ENAME -------------------- SMITH SQL> select ename from emp where empno=:xyz; ENAME -------------------- SMITH 1 在plsql中select语句的绑定变量的典型用法 declare vc_name varchar2(20); begin  execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;   DBMS_OUTPUT.PUT_LINE('name = '||vc_name);   end; 2 plsql中dml语句 declare v_sql1 varchar2(4000); v_sql2 varchar2(4000); v_temp1 number; v_temp2 number; begin v_sql1:='insert into emp(empno,ename) values(:1,:2)';  execute immediate v_sql1  using 8000,'hongquan';  v_temp1:=sql%rowcount;  v_sql2:='insert into emp(empno,ename) values(:1,:1)';   execute immediate v_sql2  using 8001,'hongquan2';   v_temp2:=sql%rowcount;  DBMS_OUTPUT.PUT_LINE(to_char(v_temp1+v_temp2));   end;

----不固定的条件 declare vc_column varchar2(10); v_sql1 varchar2(4000); v_temp1 number; vc_name varchar2(10); begin vc_name:= 'empno'; v_sql1:='delete from emp where ' ||vc_name || ' = :1 returning ename into :2';  execute immediate v_sql1  using 8000 returning into vc_name;  DBMS_OUTPUT.PUT_LINE(vc_name);  commit;   end; 批量绑定 declare cur_emp sys_refcursor ; v_sql varchar2(4000); type namelist is table of varchar2(10); names namelist; cn_batch_size constant pls_integer :=1000; begin v_sql :='select ename from emp where empno> :1'; open cur_emp for v_sql using 7900; loop  fetch cur_emp bulk collect into names limit cn_batch_size;  for i in 1 .. names.count loop   dbms_output.put_Line(names(i));   end loop;  exit when names.count <cn_batch_size; end loop; close cur_emp; end;

Oracle里的共享游标

Shared cursor之间的共享，就是重用存储在child cursor中的解析树和执行计划，避免不用从头开始硬解析

常用游标共享，参数cursor_sharing

select * from v$parameter where name='cursor_sharing';

839 cursor_sharing 2 EXACT

Exact 默认值，Oracle不会用系统产生的绑定变量来替换目标sql的sql文本中where条件或者values字句中的具体输入值。

自适应游标共享 11g 引入