Oracle Cursor 游标

Oracle中Cursor介绍：
http://www.iteye.com/topic/649874
游标小记(收集汇总)：
http://www.itpub.net/viewthread.php?tid=897079&highlight=%D3%CE%B1%EA






REF CURSOR：
Oracle REF CURSOR:
http://www.oradev.com/ref_cursor.jsp

引用

With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

Using Ref Cursors To Return Recordsets:
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

引用

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages.

REF CURSOR 小结：
http://www.itpub.net/viewthread.php?tid=443352&highlight=ref%2Bcursor
IS CURSOR DIFFERENT FROM REF CURSOR？
http://forums.oracle.com/forums/thread.jspa?threadID=1086826
REF CURSOR需要显式(explicitly)关闭吗？
http://forums.oracle.com/forums/thread.jspa?threadID=627238

引用

Ref cursors otoh are special - as they have no local scope. Only session scope. So as long as that Oracle session exist, that ref cursor created in that session will exist, until explicitly closed. Why? Because ref cursors are intended to be use by the client of that session - which means that irrespective of what happens scope wise on the PL/SQL engine side, that ref cursor handle must "survive" as it can (and often is) used by the client.
This is also the primary cause for cursor leakage. Clients (especially Java apps in my experience) use ref cursors - but forget to close them after use. The open cursor handle count quickly runs up and an ORA error results.. with the Java developers then thinking there is something wrong with Oracle and that the max number of cursor handles per session should be increased.

The oracle's garbage collector does not clean up ref cursor handles - those are only closed either explicitly (you need to code the close) or when the session terminates.
Also, ref cursors are intended for clients to use - not for PL/SQL. So whenever you use a ref cursor in PL/SQL only, you need to ask why. The java/Delphi/C client's ref cursor equivalent in PL/SQL is a DBMS_SQL cursor - that supports a proper call interface, including a describe interface.

Ask The Oracle Expert: Questions & Answers->Closing ref cursor after it's used in a Java program:
http://searchoracle.techtarget.com/answer/Closing-ref-cursor-after-it-s-used-in-a-Java-program

引用

Q:I am sending a variable of ref cursor type to a Java program. Now, how do I close the ref cursor? Does the conn.close in Java close the ref cursors? Is there any way I can explicitly close the same after it is used in Java?
A:With the ref cursor, you're doing everything you need to. That is the way it is done. The caller (your Java program) is responsible for the "close" call -- you don't do that in PL/SQL when the client fetches from the cursor. There's nothing for you to explicitly do any different than you mention.

使用oracle REF CURSOR的例子(scott用户)：

CREATE OR REPLACE PROCEDURE p_refcursor_test (
       in_field IN VARCHAR2,
       in_value IN VARCHAR2,
       rslt_cur OUT SYS_REFCURSOR
) IS
  str_sql VARCHAR2(300);
  TYPE t_EmpRecord IS RECORD(
                ename emp.ename%TYPE,
                job emp.job%TYPE,
                sal emp.sal%TYPE
                );
  
  v_EmpRecord t_EmpRecord;
BEGIN
  str_sql := 'SELECT ename,job,sal FROM emp ';
  
  IF(length(in_field)>0) and (length(in_value)>0)
   THEN
       str_sql := str_sql || 'WHERE ' || in_field || ' like ''%' || in_value || '%''';
  END IF;
  
  dbms_output.put_line(str_sql);
  --execute immediate str_sql; --动态执行
  OPEN rslt_cur FOR str_sql;


--如果这个procedure是被java程序调用的（如ibatis中的<procedure>）,则这里不可以fetch该rslt_cur，要将下面这个loop循环给注掉！！！否则返回给java ResultSet的结果集将会是空的 
  LOOP
       FETCH rslt_cur INTO v_EmpRecord;
       EXIT WHEN rslt_cur%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('名字: ' || v_EmpRecord.ename ||
                                        ' 工作: ' || v_EmpRecord.job ||
                                        ' 薪水: ' || v_EmpRecord.sal
                                        );
     
     END LOOP;

--With the ref cursor, you're doing everything you need to. That is the way it is done. The caller (your Java program) is responsible for the "close" call -- you don't do that in PL/SQL when the client fetches from the cursor

  EXCEPTION
    when others then
    DBMS_OUTPUT.PUT_LINE('------>' ||SQLERRM);
END p_refcursor_test;

关于上面代码中的SYS_REFCURSOR：

引用

sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。 9i之前想要使用ref cursor是要自己定义REF CURSOR类型游标变量的

查看数据库中打开的游标数：http://www.iteye.com/topic/337796

引用

查看游标使用情况：

select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where user_name = 'user ' and o.sid=s.sid
group by o.sid, osuser, machine
order by  num_curs desc;

查看游标执行的sql情况：

select q.sql_text
from v$open_cursor o, v$sql q
where q.hashvalue=o.hash_value and o.sid = 123;

将 %ROWTYPE 与游标配合使用：
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.plsql.doc/doc/c0053880.html


Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor：
http://www.techonthenet.com/oracle/questions/cursor1.php