分页存储过程，结果返回游标

1、创建一个游标变量，为返回值使用

create or replace package types
  as
  type cursorType is ref cursor;
  end;

2、创建分页存储过程，可供第三方接口调用

create or replace procedure PCX_FY(cur_cx out types.cursortype, --

                                   in_pagenumber in number,     --
                                   in_pagecount  in number,     --
                                   o_count       out number,    --
                                   in_colist     in varchar2,   --
                                   in_sql        in varchar2    --
)as
v_errmsg   varchar2(300);
v_rowcount_begin integer;
v_rowcount_end   integer;
v_tempsql        varchar2(32767);
begin
  --计算开始数和结尾数
  v_rowcount_begin := 1 +(in_pagenumber - 1) * in_pagecount;
  v_rowcount_end   := in_pagenumber * in_pagecount;

  --计算总行数
  if in_pagenumber < 99999 then
    v_tempsql:='select count(1) from ('||in_sql||')';
    execute immediate v_tempsql
            into o_count;
   end if;

   --获得结果集中第v_rowcount_begin 行到 v_rowcount_end行的数据
   v_tempsql:='select /*+FIRST_ROWS */'||in_colist||' from (select a.*, rownum rn from ('||in_sql||') a where rownum<='||v_rowcount_end||') where rn>='||v_rowcount_begin;

   open cur_cx for v_tempsql;

exception
  when others then
    begin
      v_errmsg := sqlerrm;
      open cur_cx for
      select '查询出错，错误：'||v_errmsg from dual;
    end;
end pcx_fy;