Oracle 開發之 REF_CURSOR

Oracle 開發之 REF_CURSOR

《PL/SQL 語言開發參考手冊》
        前言
        關於 REF_CURSOR 我們已經不再陌生，在前邊的部落格我們已經接觸過了 REF_CURSOR：在部落格《怎樣讓 Oracle 的預存程序返回結果集》中我們在一個返回結果集的 Hello World 層級的預存程序執行個體中用到了 REF_CURSOR，在部落格《菸草局績效考核系統打分模組開發筆記》中我們在一個真實的項目中體會到 REF_CURSOR 給我們帶來的神奇效果。今天，我們將通過學習 Oracle 官方的這篇《Oracle 開發之 REF_CURSOR》進一步解開 REF_CURSOR 的神秘面紗，通過本文的學習，我們對 REF_CURSOR 的理解將會更加深入，而在以後的項目中對它的使用也必將更加規範。
        本文
        Oracle REF_CURSOR 簡介
        使用 REF_CURSOR 我們可以從預存程序中得到一個結果集對象。
        REF_CURSOR 分為兩種基本類型：強型別 REF_CURSOR 和弱類型 REF_CURSOR，強型別 REF_CURSOR 返回的資料類型和長度在編譯期就應該指明，而弱類型 REF_CURSOR 不需要。
        強型別 REF_CURSOR 和 Oracle 9i 之前的弱類型 REF_CURSOR 在包中應該這樣定義：

create or replace package REFCURSOR_PKG as<br /> TYPE WEAK8i_REF_CURSOR IS REF CURSOR;<br /> TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;<br />end REFCURSOR_PKG; 
        返回 REF_CURSOR 的 PL/SQL 預存程序的編寫樣本：

/** until Oracle 9 */<br />create or replace procedure test( p_deptno IN number<br /> , p_cursor OUT<br />REFCURSOR_PKG.WEAK8i_REF_CURSOR)<br />is<br />begin<br /> open p_cursor FOR<br /> select *<br /> from emp<br /> where deptno = p_deptno;<br />end test; 
        Oracle 9i 之後，我們可以使用 SYS_REFCURSOR 作為 REF_CURSOR 的傳回型別，例如：

/** From Oracle 9 */<br />create or replace procedure test( p_deptno IN number<br /> , p_cursor OUT SYS_REFCURSOR)<br />is<br />begin<br /> open p_cursor FOR<br /> select *<br /> from emp<br /> where deptno = p_deptno;<br />end test;</p><p>/* Strong type */</p><p>create or replace procedure test( p_deptno IN number<br /> , p_cursor OUT REFCURSOR_PKG.STRONG<br />REF_CURSOR)<br />is<br />begin<br /> open p_cursor FOR<br /> select *<br /> from emp<br /> where deptno = p_deptno;<br />end test; 
        JDBC 對 REF_CURSOR 的調用
        我們可以在 JDBC 中使用以下 Java 代碼來得到 REF_CURSOR：

public void method() throws SQLException{<br /> Connection conn = getConnection();<br /> CallableStatement cstmt = null;<br /> ResultSet rs = null;<br /> int deptno = 10;<br /> Object temp;<br /> try{<br /> cstmt = conn.prepareCall("begin test(?,?); end;");<br /> cstmt.setInt(1, deptno);<br /> cstmt.registerOutParameter(2, OracleTypes.CURSOR);<br /> cstmt.execute();<br /> rs = (ResultSet) cstmt.getObject(2);<br /> ResultSetMetaData rsm = rs.getMetaData();<br /> int columnCount = rsm.getColumnCount();<br /> while (rs.next()){<br /> for (int j=0;j< columnCount;j++){<br /> temp = rs.getObject(j+1);<br /> }<br /> }<br /> } finally {<br /> if (!rs==null){<br /> rs.close();<br /> }<br /> if (!stmt==null){<br /> stmt.close();<br /> }<br /> if (!conn==null){<br /> conn.close();<br /> }<br /> }<br />} 
        PL/SQL 中對 REF_CURSOR 的調用

create or replace procedure test_call is<br /> c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;<br /> r_emp c_emp%rowtype;<br />begin<br /> test(10,c_cursor);<br /> loop<br /> fetch c_cursor into r_emp;<br /> exit when c_cursor%notfound;<br /> dbms_output.put_line(r_emp.name);<br /> end loop;<br /> close c_cursor;<br />end test_call;
原文連結：http://www.oradev.com/ref_cursor.jsp。