先創建包和包體
CREATE OR REPLACE PACKAGE PKG_TEST
IS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE pro_test(cur OUT T_CURSOR);
FUNCTION fun_test return T_CURSOR;
END;
包體
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
PROCEDURE pro_test
(cur OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
insert into tmp_table values(111);
insert into tmp_table values(222);
OPEN V_CURSOR FOR
select * from tmp_table;
cur := V_CURSOR;
commit;
END;
function fun_test return T_CURSOR
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select * from tmp_table;
return V_CURSOR;
END;
END;
Java代碼片段
Connection conn = ....
conn.setAutoCommit(false);
CallableStatement proc = conn.prepareCall("{call ? := pkg_test.fun_test}");
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(1);
while(rs.next())
{
//取得結