--创建分页过程
create or replace procedure fenye(
v_tableName in varchar2,--表名
v_pageSize in number,--每页显示的记录数
v_pageNum in number,--当前页码
v_countRows out number,--总记录数
v_countPages out number,--总页数
p_cursor out test_package.test_cursor --返回的结果集
) is
v_sql varchar2(1000);
v_begin number:=(v_pageNum-1) * v_pageSize + 1;
v_end number:=v_pageNum*v_pageSize;
begin
v_sql:='select * from (select rownum rn,t1.* from '||v_tableName||' t1 where rownum <='||v_begin||') where rn>='||v_end;
--把游标与select语句关联起来
open p_cursor for v_sql;
--计算总记录数,总页数
v_sql:='select count(*) from '||v_tableName;
execute immediate v_sql into v_countRows; --执行sql
if mod(v_countRows,v_pageSize)=0 then
v_countPages:=v_countRows / v_pageSize;
else
v_countPages:=v_countRows / v_pageSize + 1;
end if;
--关闭游标
close p_cursor;
end;
create or replace procedure fenye(
v_tableName in varchar2,--表名
v_pageSize in number,--每页显示的记录数
v_pageNum in number,--当前页码
v_countRows out number,--总记录数
v_countPages out number,--总页数
p_cursor out test_package.test_cursor --返回的结果集
) is
v_sql varchar2(1000);
v_begin number:=(v_pageNum-1) * v_pageSize + 1;
v_end number:=v_pageNum*v_pageSize;
begin
v_sql:='select * from (select rownum rn,t1.* from '||v_tableName||' t1 where rownum <='||v_begin||') where rn>='||v_end;
--把游标与select语句关联起来
open p_cursor for v_sql;
--计算总记录数,总页数
v_sql:='select count(*) from '||v_tableName;
execute immediate v_sql into v_countRows; --执行sql
if mod(v_countRows,v_pageSize)=0 then
v_countPages:=v_countRows / v_pageSize;
else
v_countPages:=v_countRows / v_pageSize + 1;
end if;
--关闭游标
close p_cursor;
end;