一个纠结的oracle数据库分页问题

Question

现在使用oracle数据库存储过程 实现分页功能，一般来说这种分页方式都是使用oracle数据表中的一个隐藏列ROWNUM，但是遇到下面这种问题，又该怎么办呢？
 procedure pro_getresource_bystartend(p_out   out myCur,
                                       f_id    in number,
                                       f_start in number,
                                       f_end   in number) as
    folder    number(20) := f_id;
    f_startid number(20) := f_start;
    f_endid   number(20) := f_end;
  begin
    if f_startid > 0 then
      open p_out for
        select t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select r.r_id,
                       r.r_path,
                       r.r_name,
                       r.r_type,
                       r.r_point,
                       r.r_synopsis                  
                  from resources r
                 where r.FOLDER_ID = folder
                 order by r.r_point) t
         where  rownum >= f_startid
           and rownum <= f_endid;
    else
      open p_out for
        select  t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select r.r_id,
                       r.r_path,
                       r.r_name,
                       r.r_type,
                       r.r_point,
                       r.r_synopsis
                  from resources r
                 where r.FOLDER_ID = folder
                 order by r.r_point desc) t
         where  rownum <= f_endid;
    end if;
  
  end pro_getresource_bystartend;

Answer 1

select t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select r.r_id,
                       r.r_path,
                       r.r_name,
                       r.r_type,
                       r.r_point,
                       r.r_synopsis,
                       rownum rn
                  from resources r
                 where r.FOLDER_ID = folder
                 order by r.r_point) t
         where  rn >= f_startid
           and rn <= f_endid;下同

Answer 2

有点问题，修改下：
select t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select t.*,
                       rownum rn
                from ( select r.r_id,
                             r.r_path,
                             r.r_name,
                             r.r_type,
                             r.r_point,
                             r.r_synopsis,
                              rownum rn
                       from resources r
                       where r.FOLDER_ID = folder
                       order by r.r_point) t
               )
         where  rn >= f_startid
           and rn <= f_endid;

Answer 3

。。这样是错误的，order by失去了意义
改成
procedure pro_getresource_bystartend(p_out   out myCur,
                                       f_id    in number,
                                       f_start in number,
                                       f_end   in number) as
    folder    number(20) := f_id;
    f_startid number(20) := f_start;
    f_endid   number(20) := f_end;
  begin
    if f_startid > 0 then
      open p_out for
        select t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select r.r_id,
                       r.r_path,
                       r.r_name,
                       r.r_type,
                       r.r_point,
                       r.r_synopsis,
                       row_number()over(order by r.r_point)rn               
                  from resources r
                 where r.FOLDER_ID = folder
                ) t
         where  rn>= f_startid
           and rn <= f_endid;
    else
      open p_out for
        select  t.r_id,
               t.r_path,
               t.r_name,
               t.r_type,
               t.r_point,
               t.r_synopsis
          from (select r.r_id,
                       r.r_path,
                       r.r_name,
                       r.r_type,
                       r.r_point,
                       r.r_synopsis,
                       row_number()over(order by r.r_point desc)rn
                  from resources r
                 where r.FOLDER_ID = folder
                 ) t
         where  rn<= f_endid;
    end if;
  
  end pro_getresource_bystartend;