Oracle分页存储过程

create or replace procedure up_syspagingwithmulti(i_sql        in varchar2,
                                                    i_orderby    in varchar2,
                                                    i_pageindex  in number default 1,
                                                    i_pagesize   in number default 15,
                                                    o_rowcount   out number,
                                                    o_rc_dataset out sys_refcursor) as
  v_minrow    number(18); -- 起始行
  v_maxrow    number(18); -- 结束行
  v_orderby   varchar2(1000); -- 排序条件
  v_sql       varchar2(4000); -- 分页SQL
  v_sqlrowcnt varchar2(4000); -- 统计总记录数SQL
  e_exception exception;
  v_errmsg varchar2(200);
begin
  -- 1、校验传入sql参数
  if i_Sql is null or length(trim(i_Sql)) < 20 then
    v_errmsg := '传入的SQL参数有问题，请核对！';
    raise e_exception;
  end if;

  -- 4、统计总的记录数
  v_sqlrowcnt := 'select count(1) from (' || i_Sql || ')';

  execute immediate v_sqlrowcnt
    into o_rowcount;

  dbms_output.put_line('rowcntsql:' || v_sqlrowcnt);
  dbms_output.put_line('rowcnt:' || o_rowcount);

  -- 5、校验排序字段
  if i_orderby is null or trim(i_orderby) = '' then
    v_orderby := ' ';
  else
    v_orderby := ' order by ' || i_orderby;
  end if;

  -- 6、计算起止记录数
  v_minrow := (i_pageindex - 1) * i_pagesize + 1;
  v_maxrow := i_pageindex * i_pagesize;

  -- 7、拼接分页查询语句
  v_sql := 'select *
  from (select a.*, rownum rnum
          from (' || i_sql || v_orderby || ') a
         where rownum <= :1)
  where rnum >= :2';

  -- 8、打开游标
  open o_rc_dataset for v_sql
    using v_maxrow, v_minrow;

  dbms_output.put_line('sql:' || v_sql);
exception
  when e_exception then
    raise_application_error(-20001, v_errmsg);
  when others then
    raise_application_error(-20001, sqlerrm);

end;
/