Oracle过程分页

 /*create or replace package my_cursor as
       type my_type is ref CURSOR;
end my_cursor;*/
create or replace procedure pager(
       tableName in varchar2,
       condition in varchar2:=' WHERE 1=1 ',
       orders in varchar2,
       pageIndex in integer:=1,
       pageSize in integer:=5,
       pageCount out integer,
       recordCount out integer,
       lresult out my_cursor.my_type
) as
 count_sql varchar2(2000) :='select count(*) from {TABLE_NAME} {CONDITION}';
 pager_sql varchar2(2000):='select * from(select rownum rn,models.* from(SELECT * from {TABLE_NAME} {CONDITION} {ORDERS})models where rownum<={TOP_COUNT} ) where rn>{PASS_COUNT}';
begin
             --替换查询总条数语句
             count_sql:=replace(count_sql,'{TABLE_NAME}',tableName);
             count_sql:=replace(count_sql,'{CONDITION}',condition);
             --执行查询总条数语句
             EXECUTE IMMEDIATE count_sql into recordCount;
             dbms_output.put_line(count_sql);
             --输出总页数
             if recordCount mod pageSize=0 then
                pageCount:=recordCount/pageSize;
             else
                pageCount:=recordCount/pageSize+1;
             end if;
             --替换分页语句
             pager_sql:=replace(pager_sql,'{TABLE_NAME}',tableName);
             if condition<>'' then
                pager_sql:=replace(pager_sql,'{CONDITION}',' where '|| LTRIM(LTRIM(UPPER(condition)),'WHERE'));
             else 
                 pager_sql:=replace(pager_sql,'{CONDITION}',condition);
             end if;
           
             pager_sql:=replace(pager_sql,'{ORDERS}',orders);
             pager_sql:=replace(pager_sql,'{TOP_COUNT}',pageIndex*pageSize);
             pager_sql:=replace(pager_sql,'{PASS_COUNT}',(pageIndex-1)*pageSize);
             dbms_output.put_line(pager_sql);
             --执行分页语句
             open lresult for pager_sql;
            
            /* for hou in lresult loop
                 dbms_output.put_line(hou.name);
             end loop;*/
end pager;

 

 

测试代码：

package one;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class pager{
 public static void main(String[] args) {  
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "house", "house");
   CallableStatement cas=con.prepareCall("{call pager('house','','',1,5,?,?,?)}");
   
   cas.registerOutParameter(1, oracle.jdbc.OracleTypes.INTEGER);
   cas.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
   cas.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
   cas.execute();
   ResultSet rs=(ResultSet)cas.getObject(3);
   while (rs.next()) {
    System.out.println(rs.getString("title"));
   }
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

可以查jdk文档上面 CallableStatement