create or replace package test_mypackage1 as
--声明类型,并引用游标
type cursorType is ref cursor;
--声明存储过程,两个输入参数,一个输出参数,输出游标类型数据
procedure prcGetGlobalAddress(pos1 integer,
--分页查询的下限
pos2 integer,
--分页查询的上限
cur in out test_mypackage1.cursorType
--输出参数,数据类型为引用游标的类型
);
end test_mypackage1;
create or replace package body test_mypackage1 as
--定义存储过程
procedure prcGetGlobalAddress(pos1 integer, pos2 integer, cur in out test_mypackage1.cursorType) as
begin
--返回得到分页查询结果集的游标句柄
open cur for select * from(select a.*, rownum rn from(select * from mes_appointment_machine) a where rownum <= pos2) where rn > = pos1;
end prcGetGlobalAddress;
end test_mypackage1;
// 调用oracle的存储过程 ---> 查询数据得到游标
public static void invokeProcedureResutl() {
Connection conn = null;
CallableStatement callableStatement = null;
try {
conn = DBUtils.getConnection();
// 第一步:获得CallableStatemen的实例;
callableStatement = conn
.prepareCall("{call test_mypackage1.prcGetGlobalAddress(?,?,?)}");
// 第二步:为输出参数注册数据类型,为输入参数赋值;
callableStatement.registerOutParameter(3,
oracle.jdbc.driver.OracleTypes.CURSOR);
callableStatement.setInt(1, 1);
callableStatement.setInt(2, 50);
// 第三步:执行存贮过程和获得返回值
callableStatement.execute();
ResultSet result = (ResultSet) callableStatement.getObject(3);
if (result != null) {
while (result.next()) {
System.out.println(result.getString(1) + "---"
+ result.getString(2) + "---" + result.getString(3)
+ "---" + result.getString(4) + "---"
+ result.getString(5) + "---" + result.getString(6)
+ "---" + result.getString(7) + "---"
+ result.getString(8));
}
}
// System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
}
}