oracle 分页查询

 1 要把ROWID来分
select * from t_xiaoxi where rowid in(select rid from select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;

2 按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t )where rk<100000 and rk>9980;

3 按rownum来分。
select * from (select t.* rownum rn from (select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980;

效率1>3>2

CREATE OR REPLACE PACKAGE "EA_PAK_PAGESELECT" as
type ResultData is ref cursor;  
procedure sp_Page(p_PageSize int,          --每页记录数                  
p_PageNo int,            --当前页码,从 1 开始                  
p_SqlSelect varchar2,    --查询语句,含排序部分                  
p_SqlCount varchar2,     --获取记录总数的查询语句                  
p_OutRecordCount out int,--返回总记录数                  
p_OutCursor out ResultData);
end;

CREATE OR REPLACE PACKAGE BODY "EA_PAK_PAGESELECT" as
procedure sp_Page(p_PageSize int,          --每页记录数                  
p_PageNo int,            --当前页码,从 1 开始                  
p_SqlSelect varchar2,    --查询语句,含排序部分                  
p_SqlCount varchar2,     --获取记录总数的查询语句                  
p_OutRecordCount out int,--返回总记录数                  
p_OutCursor out ResultData) 
as
v_sql varchar2(3000);      
v_count int;      
v_heiRownum int;      
v_lowRownum int;  
begin    ----取记录总数    
execute immediate p_SqlCount into v_count;    
p_OutRecordCount := v_count;    ----执行分页查询    
v_heiRownum := p_PageNo * p_PageSize;    
v_lowRownum := v_heiRownum - p_PageSize +1;    
v_sql := 'SELECT *  FROM (SELECT A.*, rownum rn   FROM  ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) ||') B WHERE rn >= '|| to_char(v_lowRownum);              --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn    
OPEN p_OutCursor FOR  v_sql;  
end sp_Page;
end;