--开始编写分页的过程
create or replace procedure fenye(tableName in varchar2,
pageSize in number, --每页显示记录数
pageNow in number,
myRows out number, --总记录数
myPageCount out number, --总页数
p_cursor out sys_refcursor) is
--定义sql语句 字符串
v_sql varchar2(1000);
--定义2个整数
v_begin number := (pageNow - 1) * pageSize + 1;
v_end number := pageNow * pageSize;
begin
v_sql := 'select * from (select t1.*,rownum rn from (select * from ' ||
tableName || ' order by corpid) t1 where rownum<=' || v_end ||
') where rn>=' || v_begin || '';
--把游标和sql关联
open p_cursor for v_sql;
--计算myRows和myPageCount
--组织一个sql
v_sql := 'select count(*) from ' || tableName || '';
--执行sql,并把返回的值,赋给myRows
execute immediate v_sql
into myRows;
--计算myPageCount
if mod(myRows, pageSize) = 0 then
myPageCount := myRows / pageSize;
else
myPageCount := FLOOR(myRows / pageSize) + 1;
end if;
--关闭游标
--close p_cursor;
end;
--使用java测试
create or replace procedure fenye(tableName in varchar2,
pageSize in number, --每页显示记录数
pageNow in number,
myRows out number, --总记录数
myPageCount out number, --总页数
p_cursor out sys_refcursor) is
--定义sql语句 字符串
v_sql varchar2(1000);
--定义2个整数
v_begin number := (pageNow - 1) * pageSize + 1;
v_end number := pageNow * pageSize;
begin
v_sql := 'select * from (select t1.*,rownum rn from (select * from ' ||
tableName || ' order by corpid) t1 where rownum<=' || v_end ||
') where rn>=' || v_begin || '';
--把游标和sql关联
open p_cursor for v_sql;
--计算myRows和myPageCount
--组织一个sql
v_sql := 'select count(*) from ' || tableName || '';
--执行sql,并把返回的值,赋给myRows
execute immediate v_sql
into myRows;
--计算myPageCount
if mod(myRows, pageSize) = 0 then
myPageCount := myRows / pageSize;
else
myPageCount := FLOOR(myRows / pageSize) + 1;
end if;
--关闭游标
--close p_cursor;
end;
--使用java测试