最好有例子源码 谢谢
解决方案 »
- OracleServiceORCL服务和OracleServiceORA服务
- Oracle中有没有类似With nolock的 语法
- 那位知道用sql语句如何取到下月1号这个日期
- 谁知道怎么把Delete的数据恢复(急)
- 怎样实现ORACLE中带有数组参数的JAVA存储过程?
- 几道关于oracle的题目,高手请进,急急急。。。在线等。。。
- 紧急求救,两数据数据改变问题
- 在线急救,关于用ADO的STREAM对象向ORACLE存储和读取图象的问题。
- about Error : ORA-01031 insufficicient privileges !
- 请教:这个过程错在哪里?
- 求一个转移数据的存储过程(要求用游标)
- The Network Adapter could not establish the connection
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;
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();
}
}
我猜你是否想问函数或过程能否返回cursor类型,答案是不可以。