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;