Oracle 分页存储过程

wxyfj

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

• --分页存储过程
  
• create or replace procedure paging
  
• (baseSql     in  varchar2,     --基本的sql语句
  
• pageSize    in  number,       --分页大小
  
• pageCurrent in  number,       --当前页
  
• rowCount    out number,       --总记录数
  
• pageCount   out number,       --总页数
  
• p_cursor    out p_paging.paging_cur --返回的结果集
  
• ) is
  
• --定义部分
  
• --定义sql语句，字符串变量
  
• v_sql varchar2(1000);
  
• --查询起始值
  
• v_begin number := (pageCurrent - 1) * pageSize + 1;
  
• --查询结束值
  
• v_end number   :=  pageCurrent * pageSize;
  
• begin
  
• --执行部分
  
• v_sql := 'select * from
  
•   (select t.*,rownum rn from ('||baseSql||')t where rownum = '||v_begin;
  
• --把游标和sql关联
  
• open p_cursor for v_sql;
  
• --重新赋值v_sql,计算总记录数
  
• v_sql := 'select count(1) from ('||baseSql||')';
  
• --执行sql语句，把返回值赋给rowCount
  
• execute immediate v_sql into rowCount;
  
• --计算总页数
  
• if mod(rowCount,pageSize) = 0 then
  
•     pageCount := rowCount/pageSize;
  
• else
  
•     pageCount := rowCount/pageSize + 1;
  
• end if;
  
• --close p_cursor;
  
• end;
  

　　

　　辅助的包package：
　　

• --创建一个包，定义一个游标类型
  
• create or replace package p_paging as
  
•   --游标参照变量
  
•   type paging_cur is ref cursor;
  
• end p_paging;
  

　　

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

• public class TestPagingProcedure
  
• {
  
• 
  
•     /**
  
•      * Discription:[测试分页存储过程]
  
•      * @param args
  
•      * @author:[LJ]
  
•      * @update:[2012-4-4] [LJ][创建]
  
•      */
  
• 
  
•     public static void main(String[] args)
  
•     {
  
•         Connection conn = null;
  
•         CallableStatement cs = null;
  
•         ResultSet rs = null;
  
•         String sql = "select * from emp";//查询sql语句
  
•         int pageSize = 5;//分页大小
  
•         int pageCurrent = 1;//当前页
  
•         try
  
•         {
  
•             Class.forName("oracle.jdbc.driver.OracleDriver");
  
•             conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
  
•             //调用存储过程
  
•             cs = conn.prepareCall("{call paging(?,?,?,?,?,?)}");
  
•             //设置sql语句
  
•             cs.setString(1, sql);
  
•             //设置分页大小
  
•             cs.setInt(2, pageSize);
  
•             //设置当前页
  
•             cs.setInt(3, pageCurrent);
  
•             //注册总记录数
  
•             cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
  
•             //注册总页数
  
•             cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
  
•             //注册返回的结果集
  
•             cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
  
•             //执行查询
  
•             cs.execute();
  
•             //取出总记录数和总页数，注意：getInt(4)中的4是由该参数的位置决定的
  
•             int rowNum = cs.getInt(4);
  
•             int pageCount = cs.getInt(5);
  
• 
  
•             //取出结果集
  
•             rs = (ResultSet)cs.getObject(6);
  
• 
  
•             //显示总记录数和总页数
  
•             System.out.println("rowNum = " + rowNum);
  
•             System.out.println("pageCount = " + pageCount);
  
•             //遍历结果集
  
•             while(rs.next())
  
•             {
  
•                 System.out.println("员工编号："+rs.getInt("empno")+" 名字："+rs.getString("ename"));
  
•             }
  
•         }
  
•         catch (Exception e)
  
•         {
  
•             System.out.println("查询失败！");
  
•         }
  
•         finally
  
•         {
  
•             try
  
•             {
  
•                 if(rs!=null)
  
•                 {
  
•                     rs.close();
  
•                 }
  
•                 if(cs!=null)
  
•                 {
  
•                     cs.close();
  
•                 }
  
•                 if(conn!=null)
  
•                 {
  
•                     conn.close();
  
•                 }
  
•             }
  
•             catch (SQLException e)
  
•             {
  
•                 System.out.println("关闭失败！");
  
•             }
  
•         }
  
•     }
  
• }