动态SQL游标的例子，来自：
strSQL:='SELECT * FROM ' || tmp_tbl;
open c_sign_stat for strSQL;
函数返回游标的例子，来自：
过程或函数能返回或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();
}
}