oracle存储过程分页代码

[html] 
/*******存储过程分页代码**********/  
--包头  
create or replace package pck_my is  
type c_my is ref cursor;  
procedure page_moed(  
  v_table in varchar2, --表名  
  current_page in out number,--当前页  
  pageSize in out number,--页行数  
  total out number,--总行数  
  countPage out number,--总页数  
  c_cursor out pck_my.c_my--游标  
  );  
end pck_my;  
  
--body  
create or replace package body pck_my as  
procedure page_moed(  
  v_table in varchar2,  
  current_page in out number,  
  pageSize in out number,  
  total out number,  
  countPage out number,  
  c_cursor out pck_my.c_my  
  )is  
   
v_sql varchar2(1000);  
v_max number;  
v_min number;  
e_table exception;  
  
begin    
--判断参数  
if v_table is null then  
  raise e_table;  
  --return;  
  end if;  
if current_page is null then  
current_page:=1;  
end if;  
if pageSize<=0 then  
pageSize:=5;  
end if;   
--计算 最大行 最小行  
v_max:=(current_page+1)*pageSize;  
v_min:=current_page*pageSize;  
  
--获取数据  
v_sql:= 'select *  
  from (select filminfo.*, rownum as t from '|| v_table ||' where rownum <='|| v_max||')  
 where t > ' ||v_min;  
open c_cursor for v_sql;  
  
--计算总行数  
v_sql:='select count(*)  from '|| v_table;  
execute immediate v_sql into total;  
  
--计算总页数  
if mod(total,pageSize)=0 then  
  countPage:=total/pageSize;  
else  
  countPage:=total/pageSize+1;  
end if;  
  
--exception  
exception  
  when e_table then  
   dbms_output.put_line('表名不能为空');  
  
end;  
end pck_my;  
  
--  exet  
select * from filminfo  

java测试代码

[html] 
package com.rui;  
  
import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
public class Pckage {  
  
    /**  
     * @param args  
     */  
    public static void main(String[] args) {  
        Connection con;  
        ResultSet rs;  
        CallableStatement cs;  
          
        try {  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ABC","tenement","rui");  
  
        String sql="{call pck_my.page_moed(?,?,?,?,?,?)}";  
        cs=con.prepareCall(sql);  
        //指定类型  
          
        /* v_table in varchar2,  
          current_page in out number,  
          pageSize in out number,  
          total out number,  
          countPage out number,  
          c_cursor out pck_my.c_my  
        */  
        //cs.setString(1, null);  
        cs.setString(1, "filminfo");  
        cs.setInt(2, 3);  
        cs.setInt(3,5);  
        cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);  
        cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);  
        cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);  
        cs.execute();  
          
        int total=cs.getInt(4);//总行数  
        int countPage=cs.getInt(5);//总页数  
        rs=(ResultSet)cs.getObject(6);//result  
        System.out.println("总行数:"+total+"\t总页数"+countPage);  
        System.out.println("------------------------------------");  
        while(rs.next()){  
       System.out.println("FILMNAME:"+rs.getString("FILMNAME")+"\tFILMID:"+rs.getInt("FILMID"));  
              
        }  
          
        } catch (ClassNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
          
    }  
  
}