传入表或SQL语句的分页查询存储过程

create or replace procedure 分页查询(pvc_input    varchar2,
                                 pi_backcode  out integer,
                                 pvc_message  out varchar2,
                                 pvc_output   out varchar2,
                                 cur_entrusts  out sys_refcursor) is
  pvar_i_startnum   number; --开始行数
  pvar_i_onepagenum number; --每页数据量
  pvar_i_endnum     number; --结束行数
  pvar_i_pageindex  number; --第几页
  pvar_s_tablename  varchar2(100) := ' ';
  pvar_s_sql        varchar2(1024) := ' ';
begin
  pvar_s_tablename  := nvl(strfieldbyname(pvc_input, 'tablename', ';', '='), ' ');
  pvar_s_sql        := nvl(strfieldbyname(pvc_input, 'sql', ';', '='), ' ');
  pvar_i_onepagenum := to_number(strfieldbyname(pvc_input, 'onepagenum', ';', '='));
  pvar_i_pageindex  := to_number(strfieldbyname(pvc_input, 'pageindex', ';', '='));
  pvar_i_startnum   := pvar_i_onepagenum * pvar_i_pageindex + 1;
  pvar_i_endnum     := pvar_i_startnum + pvar_i_onepagenum;

  if cur_entrusts%isopen then

    close cur_entrusts;

  end if;
  pi_backcode := 0;
  pvc_message := 'OK';
  --显示全部
  if pvar_s_tablename <> ' 'then
    open cur_entrusts for
      'select * from (select a.*, rownum rn from (select * from ' || pvar_s_tablename || ') a where rownum <= ' || to_char(pvar_i_endnum) || ') where rn >=' || to_char(pvar_i_startnum + 1);
  elsif pvar_s_sql <> ' ' then
    open cur_entrusts for
      'select * from (select a.*, rownum rn from (' || pvar_s_sql || ') a where rownum <= ' || to_char(pvar_i_endnum) || ') where rn >=' || to_char(pvar_i_startnum + 1);
  end if;
end 分页查询;