nbsp; 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的定义部分定义一个参考游标类型的curso