oracle分页存储过程

CREATE OR REPLACE PROCEDURE DivPageBySql(pstrSql       in varchar2,
                                         pnPageSize    in NUMERIC,
                                         pnPageIndex   in NUMERIC,
                                         pnRowCount    out NUMERIC,
                                         pageResultSet out SYS_REFCURSOR) IS

  v_pfrist    number;
  v_sql       varchar2(8000);
  v_PageCount number;
  v_min       number;
  v_max       number;
  v_sql1      varchar2(100);
  v_sql2      varchar2(100);
  v_sql3      varchar2(8000);
BEGIN

  --查询总记录的SQL
  v_sql := 'select count(*) from (' || pstrSql || ')';
  --执行SQL把总记录结果得到
  execute immediate v_sql
    into pnRowCount;
  --求出总页数
  v_PageCount := ceil(pnRowCount / pnPageSize);

  -- 如果传进来的页数大于最后一页，默认返回最后一页数据
  v_pfrist := pnPageIndex;
  IF (v_pfrist > v_PageCount) THEN
    v_pfrist := v_PageCount;
  end IF;

  --显示页的最大记录排行值
  v_max := v_pfrist * pnPageSize;
  --显示页的最小记录排行值
  v_min := v_max - pnPageSize + 1;

  --分页SQL
  v_sql1 := 'select * from (select rownum rn,t.* from ';
  v_sql2 := ' t ) where rn between ' || v_min || ' and ' || v_max;
  v_sql3 := v_sql1 || ' ( ' || pstrSql || ' ) ' || v_sql2;
  open pageResultSet for v_sql3;

END DivPageBySql;