Java获取Oracle存储过程返回的Cursor

yuilugynro

　　

存储过程：
Package Head:
CREATE OR REPLACE PACKAGE TEST IS
--返回cursor
TYPE CURSOR_RESULT IS REF CURSOR;
PROCEDURE GET_CURSOR_RESULT(P_STARTINDEX IN INT,
P_LIMIT      IN INT,
P_DEAL_TYPE  IN VARCHAR2,
CR           OUT CURSOR_RESULT);
END TEST;
Package Body:
CREATE OR REPLACE PACKAGE BODY TEST IS
PROCEDURE GET_CURSOR_RESULT(P_STARTINDEX IN INT,
P_LIMIT      IN INT,
P_DEAL_TYPE  IN VARCHAR2,
CR           OUT CURSOR_RESULT) IS
BEGIN
OPEN CR FOR
SELECT T.*
FROM (SELECT SDD.ID, SDD.FCU, SDD.FCD, ROWNUM RN
FROM SDDTEST SDD
WHERE DEAL_TYPE = P_DEAL_TYPE) T
WHERE T.RN >= P_STARTINDEX
AND T.RN < P_STARTINDEX + P_LIMIT;
END GET_CURSOR_RESULT;
END TEST;

　　

Java代码：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static String DB_CONNECT_STR = "jdbc:oracle:thin:@10.25.18.29:1593:test";
private static String DB_USER = "scott";
private static String DB_PASS = "tiger";
public static DBUtil dbUtil = new DBUtil();
private Connection con = null;
private DBUtil() {
try {
// 推荐使用这个驱动，不推荐oracle.jdbc.driver.OracleDriver
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
if (con == null)
con = DriverManager.getConnection(DB_CONNECT_STR,DB_USER,DB_PASS);
return con;
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import oracle.jdbc.OracleCallableStatement;
/**
* 1.java中获取Oracle存储过程返回的Cursor。   
* @author QCD
*
*/
public class OracleCursorResultTest {
/**
* @param args
*/
public static void main(String[] args) {
Connection con = null;
OracleCallableStatement ocs = null;
ResultSet rs = null;
String sql = "{call test.GET_CURSOR_RESULT(?,?,?,?)}";
try {
con = DBUtil.dbUtil.getConnection();
ocs = (OracleCallableStatement) con.prepareCall(sql);
ocs.setInt(1, 10);
ocs.setInt(2, 20);
ocs.setString(3, "01");
ocs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);
ocs.execute();
rs = ocs.getCursor(4);
while (rs.next()) {
String tradeno = rs.getString("id");
String fcu = rs.getString("fcu");
Date fcd = rs.getDate("fcd");
System.out.println("tradeno:" + tradeno + ",fcu:" + fcu + ",fcd:" + fcd);
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(rs!=null)rs.close();
if(ocs!=null)ocs.close();
if(con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
注：使用ojdbc6.jar,此版本支持JDK6，支持JDBC 4.0。