分页存储过程:入参为基本的sql语句、分页大小、当前页,出参为总记录数、总页数、查询结果集。

  1. --分页存储过程  
  2. create or replace procedure paging  
  3. (baseSql     in  varchar2,     --基本的sql语句  
  4.  pageSize    in  number,       --分页大小  
  5.  pageCurrent in  number,       --当前页  
  6.  rowCount    out number,       --总记录数  
  7.  pageCount   out number,       --总页数  
  8.  p_cursor    out p_paging.paging_cur --返回的结果集  
  9.  ) is 
  10.  --定义部分  
  11.  --定义sql语句,字符串变量  
  12.  v_sql varchar2(1000);  
  13.  --查询起始值  
  14.  v_begin number := (pageCurrent - 1) * pageSize + 1;  
  15.  --查询结束值  
  16.  v_end number   :=  pageCurrent * pageSize;  
  17.  begin 
  18.  --执行部分   
  19.  v_sql := 'select * from   
  20.   (select t.*,rownum rn from ('||baseSql||')t where rownum <= '||v_end||')  
  21.   where rn >= '||v_begin;  
  22.  --把游标和sql关联  
  23.  open p_cursor for v_sql;  
  24.  --重新赋值v_sql,计算总记录数  
  25.  v_sql := 'select count(1) from ('||baseSql||')';  
  26.  --执行sql语句,把返回值赋给rowCount  
  27.  execute immediate v_sql into rowCount;  
  28.  --计算总页数  
  29.  if mod(rowCount,pageSize) = 0 then 
  30.     pageCount := rowCount/pageSize;  
  31.  else 
  32.     pageCount := rowCount/pageSize + 1;  
  33.  end if;  
  34.  --close p_cursor;  
  35.  end

辅助的包package:

  1. --创建一个包,定义一个游标类型  
  2. create or replace package p_paging as   
  3.   --游标参照变量  
  4.   type paging_cur is ref cursor;  
  5. end p_paging; 

测试分页存储过程的java类:

  1. public class TestPagingProcedure  
  2. {  
  3.  
  4.     /**  
  5.      * <p>Discription:[测试分页存储过程]</p>  
  6.      * @param args  
  7.      * @author:[LJ]  
  8.      * @update:[2012-4-4] [LJ][创建]  
  9.      */ 
  10.  
  11.     public static void main(String[] args)  
  12.     {  
  13.         Connection conn = null;  
  14.         CallableStatement cs = null;  
  15.         ResultSet rs = null;  
  16.         String sql = "select * from emp";//查询sql语句  
  17.         int pageSize = 5;//分页大小  
  18.         int pageCurrent = 1;//当前页  
  19.         try 
  20.         {  
  21.             Class.forName("oracle.jdbc.driver.OracleDriver");  
  22.             conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");  
  23.             //调用存储过程  
  24.             cs = conn.prepareCall("{call paging(?,?,?,?,?,?)}");  
  25.             //设置sql语句  
  26.             cs.setString(1, sql);  
  27.             //设置分页大小  
  28.             cs.setInt(2, pageSize);  
  29.             //设置当前页  
  30.             cs.setInt(3, pageCurrent);  
  31.             //注册总记录数  
  32.             cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);  
  33.             //注册总页数  
  34.             cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);  
  35.             //注册返回的结果集  
  36.             cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);  
  37.             //执行查询  
  38.             cs.execute();  
  39.             //取出总记录数和总页数,注意:getInt(4)中的4是由该参数的位置决定的  
  40.             int rowNum = cs.getInt(4);  
  41.             int pageCount = cs.getInt(5);  
  42.               
  43.             //取出结果集  
  44.             rs = (ResultSet)cs.getObject(6);  
  45.               
  46.             //显示总记录数和总页数  
  47.             System.out.println("rowNum = " + rowNum);  
  48.             System.out.println("pageCount = " + pageCount);  
  49.             //遍历结果集  
  50.             while(rs.next())  
  51.             {  
  52.                 System.out.println("员工编号:"+rs.getInt("empno")+" 名字:"+rs.getString("ename"));  
  53.             }  
  54.         }  
  55.         catch (Exception e)  
  56.         {  
  57.             System.out.println("查询失败!");  
  58.         }  
  59.         finally 
  60.         {  
  61.             try 
  62.             {  
  63.                 if(rs!=null)  
  64.                 {  
  65.                     rs.close();  
  66.                 }  
  67.                 if(cs!=null)  
  68.                 {  
  69.                     cs.close();  
  70.                 }  
  71.                 if(conn!=null)  
  72.                 {  
  73.                     conn.close();  
  74.                 }  
  75.             }  
  76.             catch (SQLException e)  
  77.             {  
  78.                 System.out.println("关闭失败!");  
  79.             }  
  80.         }  
  81.     }