动态SQL游标和函数返回游标举例（转载）

动态SQL游标的例子，来自：http://www.itpub.net/thread-257998-1-1.html

strSQL:='SELECT * FROM ' || tmp_tbl;

open c_sign_stat for strSQL;

 

函数返回游标的例子，来自：http://soft.zdnet.com.cn/software_zone/2009/1221/1568365.shtml

 

过程或函数能返回或out静态游标吗？

 

CREATE OR REPLACE PACKAGE Types AS 

  TYPE cursor_type IS REF CURSOR; 

END Types; 

/

 

CREATE OR REPLACE 

PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE, 

                    p_recordset OUT Types.cursor_type) AS 

BEGIN 

  OPEN p_recordset FOR 

    SELECT ename, 

          empno, 

          deptno 

    FROM  emp 

    WHERE  deptno = p_deptno 

    ORDER BY ename; 

END GetEmpRS; 

/

 

 

在pl/sql中使用： 

SET SERVEROUTPUT ON SIZE 1000000 

DECLARE 

  v_cursor  Types.cursor_type; 

  v_ename  emp.ename%TYPE; 

  v_empno  emp.empno%TYPE; 

  v_deptno  emp.deptno%TYPE; 

BEGIN 

  GetEmpRS (p_deptno    => 30, 

            p_recordset => v_cursor); 

            

  LOOP 

    FETCH v_cursor 

    INTO  v_ename, v_empno, v_deptno; 

    EXIT WHEN v_cursor%NOTFOUND; 

    DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno); 

  END LOOP; 

  CLOSE v_cursor; --记得close游标，add by valleylord

END; 

/

 

在java中使用： 

import java.sql.*; 

import Oracle.jdbc.*;

 

public class TestResultSet  { 

  public TestResultSet() { 

    try { 

      DriverManager.registerDriver (new Oracle.jdbc.OracleDriver()); 

      Connection conn = DriverManager.getConnection("jdbc:Oracle:oci:@w2k1", "scott", "tiger"); 

      CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); END;"); 

      stmt.setInt(1, 30); // DEPTNO 

      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR 

      stmt.execute(); 

      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2); 

      while (rs.next()) { 

        System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 

      } 

      rs.close(); 

      rs = null; 

      stmt.close(); 

      stmt = null; 

      conn.close(); 

      conn = null; 

    } 

    catch (SQLException e) { 

      System.out.println(e.getLocalizedMessage()); 

    } 

  }

 

  public static void main (String[] args) { 

    new TestResultSet(); 

  } 

}