create or replace procedure show_cursor_cnt(p_text IN VARCHAR2)

AS

lv_cnt NUMBER;

BEGIN

select a.value k

INTO lv_cnt

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic# and s.sid=a.sid

and b.name = 'opened cursors current' and a.sid=(select userenv('sid') from dual);

DBMS_OUTPUT.PUT_LINE(p_text||' cursor count='||lv_cnt);

END;

/

----- 测试之前

EXEC show_cursor_cnt('before test,');

before test, cursor count=11

PL/SQL procedure successfully completed.

----- 开始测试

declare

cursor c_s1 is select * from t;

begin

show_cursor_cnt('before open ');

open c_s1;

show_cursor_cnt('after open ');

RAISE_APPLICATION_ERROR(-20001,'TEST');

end;

/

----- 开始测试

before open  cursor count=11

----- 代码打开游标之后，计数增加了

after open  cursor count=12

----- 认为抛出异常

declare

*

ERROR at line 1:

ORA-20001: TEST

ORA-06512: at line 7

EXEC show_cursor_cnt('after test,');

----- 即使出了异常，刚才打开的游标还是被关闭了

after test, cursor count=11

PL/SQL procedure successfully completed.

----============== REF CURSOR 的测试 ==============

declare

c_s1 SYS_REFCURSOR;

begin

show_cursor_cnt('before open ');

open c_s1 FOR SELECT * FROM t;

show_cursor_cnt('after open ');

end;

/

before open  cursor count=11

after open  cursor count=12

PL/SQL procedure successfully completed.

EXEC show_cursor_cnt('after test,');

----- 代码结束后，刚才打开的游标被关闭了

after test, cursor count=11

PL/SQL procedure successfully completed.

----============== 打开REF CURSOR并返回给客户端的测试 ==============

--- 定义一个客户端游标，这里的客户为SQLPLUS

VAR v_cur REFCURSOR;

begin

show_cursor_cnt('before open ');

open :v_cur FOR SELECT * FROM t;

show_cursor_cnt('after open ');

end;

/

before open  cursor count=11

after open  cursor count=12

PL/SQL procedure successfully completed.

EXEC show_cursor_cnt('after test,');

-----和前面的测试不同，这里在PLSQL块之后测试的结果还是12,因为客户仍然持有这个游标

after test, cursor count=12

PL/SQL procedure successfully completed.

---- 把游标消费掉，SQLPLUS会关闭它

PRINT V_CUR;

----上述输出略去

EXEC show_cursor_cnt('after print,');

----- PRINT结束之后，游标被关闭了

after print, cursor count=11

PL/SQL procedure successfully completed.

----============== 隐性游标的测试 ==============

---- 打开另一个SESSION 用SYS登录并执行, SID=10是我做上述实验的SESSION ID：

SELECT COUNT(*) FROM V$OPEN_CURSOR WHERE SID=10;

COUNT(*)

----------

17

-----　回到刚才的SESSION

DECLARE

lv_id NUMBER;

begin

show_cursor_cnt('before open ');

SELECT MIN(id) INTO lv_id FROM T;  ----- SELECT INTO会打开隐式游标, 这里看不到任何OPEN, CLOSE语句

show_cursor_cnt('after open ');

end;

/

before open  cursor count=11

after open  cursor count=11

PL/SQL procedure successfully completed.

---- 可见隐性游标会自动关闭。但实际上在缓存里还有，这是ORACLE自动管理的：

---- 回到SYS的SESSION

SELECT COUNT(*) FROM V$OPEN_CURSOR WHERE SID=10;

COUNT(*)

----------

18