共计 7459 个字符,预计需要花费 19 分钟才能阅读完成。
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 :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
: