oracle 分页存储过程和java代码

 create or replace procedure contfenyePro(
 v_in_tablename in varchar2,
 v_in_pagesize in number,
 v_in_pagenow in number,
 v_in_order in varchar2,
 v_out_rows out number,
 v_out_counts out number,
 v_out_result out pack1.my_cursor ) is
 --定义变量
 v_sql varchar2(2000);
 v_start number;
 v_end number;
 begin
   --execute 
  v_start:=v_in_pagesize*(v_in_pagenow-1)+1;
  v_end:=v_in_pagesize*v_in_pagenow;
  v_sql:='select t2.* from (select t1.*,rownum rn from (select * from '||v_in_tablename||' order by '||v_in_Order||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
  open v_out_result for v_sql;
  --计算v_out_rows和v_out_counts
--组织一个sql 语句
v_sql:='select count(*) from '||v_in_tablename;
--执行sql,并把返回的值，赋给myrows；
execute immediate v_sql into v_out_rows;
--计算myPageCount
  if mod(v_out_rows,v_in_pagesize)=0 then
  v_out_counts:=v_out_rows/v_in_pagesize;
  else
  v_out_counts:=v_out_rows/v_in_pagesize+1;
  end if;
 end;

package com.cc.utils;
import java.sql.*;
public class TestProcedure3 {
      public TestProcedure3() {
      }
      public static void main(String[] args ){
        String driver = "oracle.jdbc.driver.OracleDriver";
        String strUrl = "jdbc:oracle:thin:@172.16.0.136:1521:ccz";
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        CallableStatement ps = null;
        try {
          Class.forName(driver);
          conn =  DriverManager.getConnection(strUrl, "scott", "tiger");   
          ps = conn.prepareCall("{ call contfenyePro(?,?,?,?,?,?,?) }");
          ps.setString(1, "emp");
          ps.setInt(2,3);
          ps.setInt(3, 3);
          ps.setString(4,"sal desc");
          ps.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
          ps.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
          ps.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
          ps.execute();
          rs =  (ResultSet) ps.getObject(7);
          while(rs.next()){
          System.out.println("用户名是："+rs.getString("ename")+"薪水是："+rs.getString("sal"));
          }
          
          //取出记数
          int rowCount=ps.getInt(5);
          int pageCount=ps.getInt(6);
          System.out.println("总记录："+rowCount);
          System.out.println("总页数："+pageCount);
        }
        catch (Exception ex2) {
          ex2.printStackTrace();
        }
        finally{
          try {
            if(rs != null){
              rs.close();
              if(stmt!=null){
                stmt.close();
              }
              if(conn!=null){
                conn.close();
              }
            }
          }
          catch (Exception ex1) {
              ex1.printStackTrace();
          }
        }
      }
    }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26495863/viewspace-1324011/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26495863/viewspace-1324011/