首页 > 代码库 > 分页存储过程
分页存储过程
--版本1 适用单表
create or replace procedure soa_getpager_v1(p_pagesize number, --每页记录数
p_pageno number, --当前页码,从 1 开始
p_sqlcols varchar2, --选择列
p_sqlfrom varchar2, --表名OR连接查询
p_sqlwhere nvarchar2, --查询子句
p_sqlorderby varchar2, --排序
p_outrecordcount out number, --返回总记录数
p_outpagecount out number, --返回总页数
p_counts out sys_refcursor) as
v_sql varchar2(3000);
p_sqlselect varchar2(3000);
v_count number;
v_heirownum number;
v_lowrownum number;
v_sqlerror varchar2(100);
begin
----拼接SQL查询语句
p_sqlselect = ‘select ‘ p_sqlcols ‘ from ‘ p_sqlfrom
‘ where ‘ p_sqlwhere;
----取记录总数
v_sql = ‘select count(1) from ‘ p_sqlfrom ‘ where ‘ p_sqlwhere;
execute immediate v_sql
into v_count;
p_outrecordcount = v_count;
----取总页数
p_outpagecount = p_outrecordcount p_pagesize;
if p_outpagecount = 0 then
p_outpagecount = 1;
end if;
p_sqlselect = p_sqlselect ‘ order by ‘ p_sqlorderby;
----执行分页查询
v_heirownum = p_pageno p_pagesize;
v_lowrownum = v_heirownum - p_pagesize + 1;
v_sql = ‘select from (
select a.,rownum rn
from ( ‘ p_sqlselect ‘) a
where rownum = ‘ to_char(v_heirownum) ‘
) b
where rn = ‘ to_char(v_lowrownum);
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
dbms_output.put_line(v_sql);
open p_counts for v_sql;
exception
when others then
v_sqlerror = sqlerrm;
p_outpagecount = -1;
end soa_getpager_v1;
--版本2 适用多表
create or replace procedure soa_getpager_v2(p_pagesize number, --每页记录数
p_pageno number, --当前页码,从 1 开始
p_sqlcols varchar2, --选择列
p_sqlfrom varchar2, --表名OR连接查询
p_sqlwhere nvarchar2, --查询子句
p_sqlorderby varchar2, --排序
p_outrecordcount out number, --返回总记录数
p_outpagecount out number, --返回总页数
p_counts out sys_refcursor) as
v_sql varchar2(3000);
p_sqlselect varchar2(3000);
v_count number;
v_heirownum number;
v_lowrownum number;
v_sqlerror varchar2(100);
begin
----拼接SQL查询语句
p_sqlselect := ‘select ‘ || p_sqlcols || ‘ from ‘ || p_sqlfrom ||
‘ where ‘ || p_sqlwhere;
----取记录总数
v_sql := ‘select count(1) from ‘ || p_sqlfrom || ‘ where ‘ || p_sqlwhere;
execute immediate v_sql
into v_count;
p_outrecordcount := v_count;
----取总页数
p_outpagecount := p_outrecordcount / p_pagesize;
if p_outpagecount = 0 then
p_outpagecount := 1;
end if;
p_sqlselect := p_sqlselect || ‘ order by ‘ || p_sqlorderby;
----执行分页查询
v_heirownum := p_pageno * p_pagesize;
v_lowrownum := v_heirownum - p_pagesize + 1;
v_sql := ‘select * from (
select a.*,rownum rn
from ( ‘ || p_sqlselect || ‘) a
where rownum <= ‘ || to_char(v_heirownum) || ‘
) b
where rn >= ‘ || to_char(v_lowrownum);
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
dbms_output.put_line(v_sql);
open p_counts for v_sql;
exception
when others then
v_sqlerror := sqlerrm;
p_outpagecount := -1;
end soa_getpager_v2;
分页存储过程