oracle分页存贮过程有问题

1,问题:

当currentPage > 页面总数的时候,没有记录。

2.环境说明:oracle 10g。

3,使用oracle 自带的emp表做测试。

存贮过程如下:

create or replace procedure pagging_pro

(

p_cursor out testpackage.test_cursor,--结果集

v_tableName in varchar2,--表名

v_pageSize in number,--每页显示的记录数目

v_currentPage in number,--当前页

v_allRecords out number,--总记录数目

v_allPages out number--总页数

)

is

r_currentPage number := 0;

begin

--获取表的总的记录数目

execute   immediate 'select count(*) from ' ||  v_tableName into v_allRecords;



--获取总的页数

if mod(v_allRecords,v_pageSize) = 0 then

v_allPages := v_allRecords/v_pageSize;

else 

v_allPages := v_allRecords/v_pageSize+1;

end if;



--判断是否到到达结束页和开始页

if v_currentPage > v_allPages then

   r_currentPage := v_allPages;

elsif v_currentPage <= 0 then

   r_currentPage := 1;

else

   r_currentPage := v_currentPage;

end if;

--遍历结果

open p_cursor for ' 

select temp.* from (

select rownum rn,t.* from (select * from '||v_tableName||') t where rownum <= '||(r_currentPage*v_pageSize)||'

) temp

where temp.rn >'||((r_currentPage-1)*v_pageSize);

end;

4,java 中调用

private static void test3() throws Exception {

		Connection conn = null;

		try {

			conn = getConnection();

			CallableStatement stm = conn.prepareCall("{call pagging_pro(?,?,?,?,?,?)}");

			stm.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

			stm.setString(2, "EMP");//表名

			stm.setInt(3, 4);//每页显示的数量

			stm.setInt(4, 5);//当前页 

			stm.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);

			stm.registerOutParameter(6, oracle.jdbc.OracleTypes.NUMBER);

			//stm.registerOutParameter(7, oracle.jdbc.OracleTypes.NUMBER);

			stm.execute();

			ResultSet rs = (ResultSet)stm.getObject(1);

			System.out.println("总记录数目:"+stm.getInt(5));

			System.out.println("总页数:"+stm.getInt(6));

			//System.out.println("dddd:"+stm.getInt(7));

			while(rs.next()) {

				System.out.println(rs.getObject("rn")+"=="+rs.getObject("empno")+"=="+rs.getObject("ename"));

			}

			stm.close();

			conn.close();

		} catch (Exception e) {

			e.printStackTrace();

			conn.rollback();

		}

private static Connection getConnection() throws Exception {

		Class.forName("oracle.jdbc.driver.OracleDriver");

		Connection conn = DriverManager.getConnection(

				"jdbc:oracle:thin:@localhost:1521:ohome", "scott", "tiger");

		return conn;

	}