oracle 存储过程分页

 

/*select * from house;
select * from users;
select * from street;*/

/* SELECT * FROM
                      (
                        SELECT rowid R,MODEL.* FROM
                             (
                               SELECT * FROM USERS ORDER BY ID     
                             ) MODEL
                             WHERE ROWNUM<=5
                      ) WHERE R>0
*/
/*select * from (
              select rownum r,m.* from (
                                     select * from users order by id
                                     ) m where rownum<=10
               ) where r>5*/
      
       
              
create or replace procedure proc_pager(
       tableName in varchar2,
       pageIndex in integer:=1,
       pageSize in integer:=10,
       condition in varchar2:=' WHERE 1=1 ',
       orderlist in varchar2:='',
       recordCount out integer,--
       pageCount out integer,
       lresult out sys_refcursor
)

as
       lcount integer:=0;
       sql_recordCount  varchar2(2000):='SELECT COUNT(1) FROM {TABLE_NAME} {CONDITION}';     
       sql_page varchar2(2000):='
               SELECT * FROM
                      (
                        SELECT ROWNUM R,MODEL.* FROM
                             (
                               SELECT * FROM {TABLE_NAME} {CONDITION} {ORDER_LIST}      
                             ) MODEL
                             WHERE ROWNUM<={TOP_COUNT}
                      ) WHERE R>{PASS_COUNT}
       ';      
BEGIN
     --总条数
     sql_recordCount:=replace(sql_recordCount,'{TABLE_NAME}',tableName);--替换表名
     IF LTRIM(condition) IS NOT NULL THEN --''
       sql_recordCount:=replace(sql_recordCount,'{CONDITION}',' WHERE '||LTRIM(LTRIM(UPPER(condition),'WHERE')));--替换条件
     ELSE --
       sql_recordCount:=replace(sql_recordCount,'{CONDITION}',' WHERE 1=1 '||LTRIM(LTRIM(UPPER(condition),'WHERE')));--替换条件
     END IF;
     dbms_output.put_line(sql_recordCount);
     EXECUTE IMMEDIATE sql_recordCount INTO lcount;
     recordCount:=lcount; --总条数
    
     IF recordCount mod pageSize = 0 THEN --余数为0页数为他们的商，否则是商加1
        pageCount:=recordCount/pageSize; --总页数
     ELSE
        pageCount:=FLOOR(recordCount/pageSize)+1;--总页数
     END IF;
    
     --分页
     sql_page:=REPLACE(sql_page,'{TABLE_NAME}',tableName);
     IF LTRIM(condition) IS NOT NULL THEN --''
        sql_page:=REPLACE(sql_page,'{CONDITION}',' WHERE '||LTRIM(LTRIM(UPPER(condition),'WHERE')));--替换条件
     ELSE --
        sql_page:=REPLACE(sql_page,'{CONDITION}',' WHERE 1=1 '||LTRIM(LTRIM(UPPER(condition),'WHERE')));--替换条件
     END IF;
   
     IF LTRIM(orderlist) IS NOT NULL THEN
         sql_page:=REPLACE(sql_page,'{ORDER_LIST}',' ORDER BY '||orderlist);
     ELSE
         sql_page:=REPLACE(sql_page,'{ORDER_LIST}','');   
     END IF;
    
     sql_page:=REPLACE(sql_page,'{TOP_COUNT}',pageIndex*pageSize);
     sql_page:=REPLACE(sql_page,'{PASS_COUNT}',(pageIndex-1)*pageSize);
     DBMS_OUTPUT.put_line(sql_page);
     OPEN lresult FOR sql_page;
END proc_pager;   
              

declare
     rCount  number;
     pCount  number;
     presult  sys_refcursor;
begin
     proc_pager(tableName =>'users',pageIndex =>1, pageSize => 10,condition => 'id<=41',orderlist => '', recordCount => rCount,pageCount => pCount,lresult => presult );
     dbms_output.put_line('总条数：'||rCount || '  总页数' || pCount);
   /*  for pi in presult loop
       dbms_output.put_line(pi.id);
     end loop;*/
end ;

 

 

--游标在这里我还不知道怎么使用，在java程序中的使用如下：

package org.accp.house.utest;

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

public class TextOracleProce {
   public static void main(String[] args) {
            textSearchStreet();  
   }

 private static void textSearchStreet() {
       ResultSet rs = null;
      Connection con = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "house", "house");
   
   CallableStatement cas=con.prepareCall("{call pack_street.proc_select_street(1,10,'','',?,?,?)}");
         /* pageIndex in integer:=1,
           pageSize in integer:=10,
           condition in varchar2:=' WHERE 1=1 ',
           orderlist in varchar2:='',
           recordCount out integer,--
           pageCount out integer,
           lresult out sys_refcursor  */
   //处理 问号
   cas.registerOutParameter(1, oracle.jdbc.OracleTypes.INTEGER);
   cas.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
   cas.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);

   //执行
   cas.execute();
   System.out.println("总条数"+cas.getInt(1));
   System.out.println("总页数"+cas.getInt(2));

  //循环游标，将其强制转换为结果集
   rs=(ResultSet)cas.getObject(3);
   while (rs.next()) {
    System.out.println(rs.getString("name"));
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    con.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }

}

//未进行封装