CREATE OR REPLACE PACKAGE SCOTT.pkg_test as
/**//* 定義ref cursor類型
不加return類型，為弱類型，允許動態(tài)sql查詢，
否則為強類型，無法使用動態(tài)sql查詢;
*/
type myrctype is ref cursor;
--函數(shù)申明
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
--函數(shù)體
function get(intID number) return myrctype is
rc myrctype; --定義ref cursor變量
sqlstr varchar2(500);
begin
if intID=0 then
--靜態(tài)測試，直接用select語句直接返回結果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--動態(tài)sql賦值，用:w_id來申明該變量從外部獲得
sqlstr :='select id,name,sex,address,postcode,birthday from student where id=:w_id';
--動態(tài)測試，用sqlstr字符串返回結果，用using關鍵詞傳遞參數(shù)
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();
}