51,396
社区成员




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;
}
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 := floor(v_allRecords/v_pageSize)+1;--这里的floor,解决当currentPage > 页面总数的时候,没有记录。
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;