CREATE OR REPLACE PACKAGE SCOTT.pkg_test as
/**//* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor;
--函数申明
function get(intID number) return myrctype;
Procedure getstudent(
intID number,
return_list out myrctype
);
end pkg_test;
==============================================
CREATE OR REPLACE PACKAGE BODY SCOTT.pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试，直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr :='select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
Procedure getstudent(
intID number,
return_list out myrctype
)as
begin
open return_list
for
select id,name,sex,address,postcode,birthday from student;
end getstudent;
end pkg_test;
/
try 
{
conn.setAutoCommit(false);
CallableStatement proc = conn
.prepareCall("{call pkg_test.getstudent(?,?)}");
proc.setInt(1, 1);
proc.registerOutParameter(2, OracleTypes.CURSOR);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(2);
while (rs.next()) {
System.out.println(rs.getString(2));
}
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
try {
conn.setAutoCommit(false);
CallableStatement proc = conn
.prepareCall("{call ? := pkg_test.get(?)}");
proc.setInt(2, 0);
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(2));
}
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}