分页存储过程

--版本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;

转载于:https://www.cnblogs.com/iyoume2008/p/6100698.html

相关资源：SqlServer高效万能分页存储过程