Oracle分页查询脚本

create or replace package jssb.pck_page is
     
type T_Page is ref cursor; --定义游标变量用于返回记录集
     
procedure getpagerecord(
  pindex in number, --分页索引
  psql in varchar2, --产生dataset的sql语句
  psize in number, --页面大小
  pcount out number, --返回分页总数
  v_cur out T_Page --返回当前页数据记录
);
     
end pck_page;
[sql] view plaincopy
create or replace procedure jssb.p_app_GetCount(
  p_sql in varchar2,
  p_count out number
)
as
     
  v_sql varchar2(1000);
  v_prcount number;
     
begin
     
  v_sql := 'select count(*) from (' || p_sql || ')';
  execute immediate v_sql into v_prcount;
  p_count := v_prcount; --返回记录总数
     
end p_app_GetCount;
[sql] view plaincopy
create or replace procedure jssb.p_app_page(
  p_pagesql in varchar2,--产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
  p_pagesize in number, --每页显示的条数
  p_pageindex in number,--显示页的索引 从0开始
  p_totalcount out number,--总条数
  p_pagecount out number,--总页数
  p_currentpagedata out pck_page.T_Page --返回当前页的数据
)
as
begin
  pck_page.getpagerecord(p_pageindex,p_pagesql,p_pagesize,p_pagecount,p_currentpagedata);
  p_app_getcount(p_pagesql,p_totalcount);
end;
[sql] view plaincopy
create or replace package body jssb.pck_page is
  procedure getpagerecord(
    pindex in number,
    psql in varchar2,
    psize in number,
    pcount out number,
    v_cur out T_Page
  )
  as
     
    v_sql varchar2(1000);
    v_count number;
    v_plow number;
    v_phei number;
  begin
     
    v_sql := 'select count(*)  from (' || psql || ')';
    execute immediate v_sql into v_count;
    pcount := ceil(v_count/psize);
     
    v_phei := pindex * psize + psize;
    v_plow := v_phei - psize + 1;
    --psql := select rownum rn,t.* from test t ; --要求必须包含rownum字段
    v_sql := 'select * from (' || psql || ') where rn between ' || v_plow || ' and ' || v_phei ;
     
    open v_cur for v_sql;
     
  end getpagerecord;
     
end pck_page;