pl/sql 编写 Oracle分页的存储过程 Java调用该过程

首先创建 Oracle 分页的存储过程

--分页，输入：tableName、page_size、pageNow，输出：myrows、myPageCount、my_cursor
--1、定义一个游标类型
create or replace package pagePackage as
type page_cursor is ref cursor;
end pagePackage;
/
--2、
create or replace procedure fenye
(tableName in varchar2,  --表名
 page_size in number,   --每页记录数
 pageNow  in number, --当前页
 myrows  out number, -- 总记录数
 myPageCount out number, --总页数
 my_cursor out pagePackage.page_cursor --返回的结果集
)is
v_sql varchar2(1000);
v_begin number := (pageNow-1)*page_size+1;
v_end number := pageNow*page_size;
begin
  v_sql :=  'select * from (select t1.*,rownum rn from (select * from '||tableName
  ||')t1 where rownum<='||v_end||' )where rn>='||v_begin;
  open my_cursor for v_sql;
  v_sql :='select count(*) from '|| tableName;
  execute immediate v_sql into myrows;
  if mod(myrows,page_size) =0 then
  myPageCount := myrows/page_size;
  else myPageCount := myrows/page_size+1;
  end if;
end;
/

Java调用该过程

@Test
	public void testWithReturn_page() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.202.129:1521:orcl", "scott",
					"tiger");
			CallableStatement cs = conn
					.prepareCall("{call fenye(?,?,?,?,?,?)}");
			cs.setString(1, "emp");
			cs.setInt(2, 3);
			cs.setInt(3, 2);
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
			cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
			cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			cs.execute();

			// 得到结果集
			ResultSet rs = (ResultSet) cs.getObject(6);
			while (rs.next()) {
				System.out.println(rs.getInt("deptno") + " --- "
						+ rs.getInt("empno") + " --- " + rs.getString("ename")
						+ " --- " + rs.getString("job") + " --- "
						+ rs.getInt("sal"));
			}

			int rs2 = cs.getInt(4);
			int rs3 = cs.getInt(5);
			System.out.println("allRecord:" + rs2 + "  ,allPage:" + rs3);

			cs.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

结果：