oracle 分页存储过程

--创建包规范
create or replace package package_page as
type cursor_page is ref cursor;
Procedure proc_page(
p_curpage Number, --当前页
p_pagesize Number, --每页大小
p_tablename varchar2, --表名emp e
p_where varchar2, --查询条件e.ename like '%S%'
p_tablecolumn varchar2, --查询列e.id,e.ename,e.job
p_order varchar2, --排序e.ename desc
p_rowcount out Number, --总条数,输出参数
p_pagecount out number, --总页数
p_cursor out cursor_page); --结果集
end package_page;

--创建包主休
Create Or Replace Package Body package_page
Is
--存储过程
Procedure proc_page(
p_curpage Number,
p_pagesize Number,
p_tablename varchar2,
p_where varchar2,
p_tablecolumn varchar2,
p_order varchar2,
p_rowcount out Number,
p_pagecount out number,
p_cursor out cursor_page
)
is
v_count_sql varchar2(2000);
v_select_sql varchar2(2000);
begin
--查询总条数
v_count_sql:='select count(*) from '||p_tablename;
--连接查询条件(''也属于is null)
if p_where is not null then
v_count_sql:=v_count_sql||' where '||p_where;
end if;
--执行查询,查询总条数
execute immediate v_count_sql into p_rowcount;

--dbms_output.put_line('查询总条数SQL=>'||v_count_sql);
--dbms_output.put_line('查询总条数Count='||p_rowcount);

--得到总页数
if mod(p_rowcount,p_pagesize)=0 then
p_pagecount:=p_rowcount/p_pagesize;
else
p_pagecount:=p_rowcount/p_pagesize+1;
end if;

--如果查询记录大于0则查询结果集
if p_rowcount>0 and p_curpage>=1 and p_curpage<=p_pagecount then

--查询所有(只有一页)
if p_rowcount<=p_pagesize then
v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
if p_where is not null then
v_select_sql:=v_select_sql||' where '||p_where;
end if;
if p_order is not null then
v_select_sql:=v_select_sql||' order by '||p_order;
end if;
elsif p_curpage=1 then --查询第一页
v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
if p_where is not null then
v_select_sql:=v_select_sql||' where '||p_where||' and rownum<='||p_pagesize;
else
v_select_sql:=v_select_sql||' where rownum<='||p_pagesize;
end if;
if p_order is not null then
v_select_sql:=v_select_sql||' order by '||p_order;
end if;
else --查询指定页
v_select_sql:='select * from (select '|| p_tablecolumn ||',rownum row_num from '|| p_tablename;
if p_where is not null then
v_select_sql:=v_select_sql||' where '||p_where;
end if;
if p_order is not null then
v_select_sql:=v_select_sql||' order by '||p_order;
end if;
v_select_sql:=v_select_sql||') where row_num>'||((p_curpage-1)*p_pagesize)||' and row_num<='||(p_curpage*p_pagesize);
end if;
--执行查询
dbms_output.put_line('查询语句=>'||v_select_sql);
open p_cursor for v_select_sql;
end if;

end proc_page;
end package_page;

----------------测试----------------
declare
v_rowcount number(5,0);
v_pagecount number;
v_cursor package_page.cursor_page;
begin
package_page.proc_page(2,2,'emp e','ename like ''%S%''','e.*','ename desc',v_rowcount,v_pagecount,v_cursor);
dbms_output.put_line(v_rowcount);
dbms_output.put_line(v_pagecount);
end;