现在使用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;
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;
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;下同
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;
改成
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;