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();
??}
?}

?