linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

Oracle中游标Cursor使用实例

时间:2019-03-28  来源:未知  作者:admin666

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

      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
 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
 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
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
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

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 :x :=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 引入

更多Oracle相关信息见Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

友情链接