先建个package,留着后面用 Create package x_ref_cursor as type t_RefCur is ref cursor; type charTab is table of varchar2(10000) index by binary_integer; type charField is table of varchar2(100) index by binary_integer; type T_DATE is table of DATE index by binary_integer; end x_ref_cursor;然后: CREATE PROCEDURE PROCEDURE_NAME ( test IN INT DEFAULT NULL p_cursor1 out x_ref_cursor.t_RefCur, p_cursor2 out x_ref_cursor.t_RefCur ) AS BEGIN open p_cursor1 for 'SELECT * FROM table1 '; open p_cursor2 for 'SELECT * FROM table2 where aaa=test '; END;
请问在java中怎么得到这个自定义类型的返回值呢?
返回记录集CREATE OR REPLACE PROCEDURE cg_p_GetRecordSet_Dist ( cur_out OUT sys_refcursor ) ISBEGIN --打开游标 OPEN cur_out FOR SELECT t.distid,t.distname,t.distcode FROM cg_s_t_district t; END cg_p_GetRecordSet_Dist;
Oracle Package返回游标和Java调用
创建测试oracle包: create or replace package pkg_test astype t_ref is ref cursor; procedure p_test(cur_ref out t_ref);end pkg_test; /create or replace package body pkg_test asprocedure p_test(cur_ref out t_ref) isbegin open cur_ref for 'select * from test'; end p_test;end pkg_test; / 下面是jdbc代码的调用。 当然如果采用jdbc直接获得Connection下面的代码是没问题的。 oracle.jdbc.OracleCallableStatement cs = null; String sqlStr = "{call firstpage.getSeniorHighSchool(?,?,?)}"; ResultSet rs = null; try{ cs = (oracle.jdbc.OracleCallableStatement)conn.prepareCall(sqlStr); cs.setLong(1,arg[0]); cs.setLong(2,arg[1]);cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR); cs.execute(); rs = cs.getCursor(3);while(rs.next()){ //........... } rs.close(); cs.close(); }catch(Exception e){ System.out.println("produce error "); e.printStackTrace(); }
2.返回一个游标变量 有具体的例子 再帮你看看
Create package x_ref_cursor as
type t_RefCur is ref cursor; type charTab is table of varchar2(10000)
index by binary_integer; type charField is table of varchar2(100)
index by binary_integer; type T_DATE is table of DATE
index by binary_integer;
end x_ref_cursor;然后:
CREATE PROCEDURE PROCEDURE_NAME
(
test IN INT DEFAULT NULL
p_cursor1 out x_ref_cursor.t_RefCur,
p_cursor2 out x_ref_cursor.t_RefCur
)
AS
BEGIN
open p_cursor1 for 'SELECT * FROM table1 ';
open p_cursor2 for 'SELECT * FROM table2 where aaa=test ';
END;
(
cur_out OUT sys_refcursor
)
ISBEGIN
--打开游标
OPEN cur_out FOR
SELECT t.distid,t.distname,t.distcode
FROM cg_s_t_district t;
END cg_p_GetRecordSet_Dist;
创建测试oracle包:
create or replace package pkg_test astype t_ref is ref cursor;
procedure p_test(cur_ref out t_ref);end pkg_test;
/create or replace package body pkg_test asprocedure p_test(cur_ref out t_ref) isbegin
open cur_ref for 'select * from test';
end p_test;end pkg_test;
/
下面是jdbc代码的调用。 当然如果采用jdbc直接获得Connection下面的代码是没问题的。
oracle.jdbc.OracleCallableStatement cs = null;
String sqlStr = "{call firstpage.getSeniorHighSchool(?,?,?)}";
ResultSet rs = null;
try{
cs = (oracle.jdbc.OracleCallableStatement)conn.prepareCall(sqlStr);
cs.setLong(1,arg[0]);
cs.setLong(2,arg[1]);cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs = cs.getCursor(3);while(rs.next()){
//...........
}
rs.close();
cs.close();
}catch(Exception e){
System.out.println("produce error ");
e.printStackTrace();
}