Orcale创建分页存储过程

create or replace package mypage
as
 type page_cursor is ref cursor;
 procedure get(page_col varchar2,  --要查询的列
   page_table varchar2, --表名(可有多张）
   page_where varchar2,--条件
   page_order varchar2, --排序
   page_size number, --一页显示的条数
   page_currpage number, --当前第几条
   page_count out number, --返回总条数
   page_result out page_cursor); -- 返回结果集
end mypage;
/

create or replace package body mypage
as
procedure get(page_col varchar2,
   page_table varchar2,
   page_where varchar2,
   page_order varchar2,
   page_size number,
   page_currpage number,
   page_count out number,
   page_result out page_cursor)
is
   t_sql varchar2(1000);
   t_result varchar2(1000);
   t_count number;
begin
if page_col is not null then
   t_result := 'select '|| page_col || ' from ' || page_table ;
else
   t_result := 'select * from ' || page_table ;
end if;
   t_sql := 'select count(*) from ' || page_table || ' where 1=1 '|| page_where;
   execute immediate t_sql into t_count;
   page_count := t_count;
   t_result := t_result ||' where rownum between '|| (page_currpage-1) * page_size|| ' and '|| page_currpage * page_size || page_where || page_order ;
   open page_result for t_result;
end get;
end mypage;
/

 

========================调用处=============================

创建连接对象 

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    String url = "jdbc:oracle:thin:@localhost:1521:Test"; //Test为你的数据库的SID
    String user = "cowry";
    String password = "cowry";
    Connection conn = DriverManager.getConnection(url, user, password);

调用
  CallableStatement proc = conn.prepareCall("{ call mypage.get(?,?,?,?,?,?,?,?)}");
  proc.setString(1, "*"); //要查询的列
  proc.setString(2, "resume, RecruitMessage"); //要查询的表名
  proc.setString(3, where.toString());                //where条件
  proc.setString(4, "order by resume.id desc");      //排序语句
  proc.setInt(5, 10);           // 每显示多少条
  proc.setInt(6, currPage);          //当前第几页
  proc.registerOutParameter(7, OracleTypes.NUMBER);  // 总条数

  proc.registerOutParameter(8, OracleTypes.CURSOR);  // 查询结果集
  proc.execute();
  int r = ((OracleCallableStatement) proc).getInt(7); //显示的总条数
  ResultSet set = ((OracleCallableStatement) proc).getCursor(8);

  while(set.next){

    .....

  }