oracle返回结果集
CREATE OR REPLACE PACKAGE pkg_test AS TYPE OEPSMWS_CURSOR IS REF CURSOR;PROCEDURE get_data(p_id NUMBER,p_rc OUT OEPSMWS_CURSOR);function funcGetdatas(p_id number) return OEPSMWS_CURSOR;END pkg_test;

分别定义了返回的游标，和过程及函数的定义；

?

CREATE OR REPLACE PACKAGE BODY pkg_test ASprocedure get_datas(p_id number,o_cursor out OEPSMWS_CURSOR )  is     result_sql varchar2(200); begin      if p_id is null or p_id=0 thenopen o_cursor for  select * from area;else  result_sql:='select * from area where id =:v_id';open o_cursor for   result_sql using p_id;   end if; end  get_datas;  -----function funcGetdatas(p_id number) return o_cursor is rc OEPSMWS_CURSOR ; --定义ref cursor变量     sqlstr2  varchar2(500);      beginif p_id =0 then--静态测试，直接用select语句直接返回结果open o_cursor  for SELECT ID,name,code  FROM area;else--动态sql赋值，用:w_id来申明该变量从外部获得sqlstr2 := 'select id,name,code from area  where id =:w_id';--动态测试，用sqlstr字符串返回结果，用using关键词传递参数open o_cursor for sqlstr2 using p_id;end if;return o_cursor ;end funcGetdatas;end pkg_test;

??这样就通过存储过程或函数分别获取返回结果集的游标了；下面是java调用方式：

public static void testOrclcursor(){String driver = "oracle.jdbc.driver.OracleDriver";String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE ";String user = "";String pwd = "";Connection conn = null;CallableStatement cs = null;ResultSet rs = null;try {Class.forName(driver);conn = DriverManager.getConnection(url, user, pwd);cs = conn.prepareCall("{ call   pkg_test.funcGetdatas(?,?) }");cs.setInt(1, 290);cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);cs.execute();rs = ((OracleCallableStatement) cs).getCursor(2);// 或者下一种方式// rs=(ResultSet) cs.getObject(2);if (rs != null) {while (rs.next()) {System.out.println(rs.getString("code"));}}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

?

?