show parameter open_cursors; --单个session可并存sessioncursor数
select count(*) from v$open_cursor where sid in (select sidfrom v$mystat where rownum<2);--当前session的sessioncursor总数
select name,value from v$sysstat where name ='opened cursorscurrent'; --open 状态的session cursor总数
show parameter session_cached_cursors;
alter session set events 'immediate trace name ERRORSTACKlevel 3'; --session cursor dump
11gR2后,一个session cursor能够缓存在PGA中的必要条件是该cursor所对应的SQL解析和执行的次数超过3次。
Pin这个动作是通过先持有与库相关的Latch,再持有Library cache pin这个enqueue来实现。11gR1之前的版本,把CURSOR_SPACE_FOR_TIME的值设成TRUE,可以减少库缓存相关的LATCH争用,但会给Sharedpool空间带来大的压力。
隐式游标:
1. SQL%FOUND: SQL执行之后记录的改变数是否大于1.(INSERT,UPDATE,DELETE,SELECTINTO)有TRUE,FALSE,NULL
2. SQL%NOTFOUND
3. SQL%ISOPEN 对隐式,总是FALSE
4. SQL%ROWCUNT 记录的改变数量
显示游标:
1. CURSORNAME%FOUND
2. CURSORNAME%NOTFOUND
3. CURSORNAME%ISOPEN
4. CURSORNAME%ROWCOUNT
参考游标:
定义灵活,open方式灵活(不和固定的SQL绑定在一起),可作为参数传入。
select count(*) from v$open_cursor where sid in (select sid from v$mystat where rownum<2);
select name,value from v$sysstat where name ='opened cursors current';
show parameter session_cached_cursors;
alter session set events 'immediate trace name ERRORSTACK level 3';
select count(*) from t52;
select sql_text,cursor_type from v$open_cursor where user_name='TESTER' and sid = 387 and sql_text like 'select count(*) from t52';
select * from dept;
declare
dept_no number(4) :=1;
begin
delete from dept where deptno = dept_no;
if sql%found then
insert into dept values(4,'DATABASE','bejing',current_date);
end if;
commit;
end;
/
select * from dept;
set serveroutput on size 1000000
declare
dept_name varchar2(14);
vc_message varchar2(4000);
begin
select dname into dept_name from dept where dname ='DATABASE';
exception
when no_data_found then
dbms_output.put_line('NO data found!');
return;
when too_many_rows then
dbms_output.put_line('Too many data');
return;
when others then
vc_message := 'E' ||'_'||sqlcode ||'_'||sqlerrm;
dbms_output.put_line(vc_message);
return;
end;
/
declare
dept_no number(4) := 3;
begin
delete from dept where deptno =dept_no;
dbms_output.put_line('Number of departments deleted:'||to_char(sql%rowcount));
commit;
end;
/
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%found then
dbms_output.put_line('name=' || my_ename||',salary'||my_salary);
else
exit;
end if;
end loop;
close c1;
end;
/
exception
when others then
if c1%isopen = true then
close c1;
end if;
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;
/
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%found then
dbms_output.put_line('name=' || my_ename||'_'||c1%rowcount);
else
exit;
end if;
end loop;
close c1;
end;
/
create or replace procedure DEMO_EXPLICIT_CURSOR is
--declare
cursor c1 is select ename,sal 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;
-- write log
return;
end DEMO_EXPLICIT_CURSOR;
/