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

 

存储过程：
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。