Oracle分页查询

--建表

create table dinya_test 
( 
      transaction_id number primary key, 
      item_id number(8) not null, 
      item_description varchar2(300), 
      transaction_date date 

)


--插入数据

insert into dinya_test values(1, 12, 'BOOKS',sysdate); 
insert into dinya_test values(2, 12, 'BOOKS',sysdate+30); 
insert into dinya_test values(3, 12, 'BOOKS',to_date('2005-05-30','yyyy-mm-dd')); 
insert into dinya_test values(4, 12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd')); 
insert into dinya_test values(5, 12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd')); 
insert into dinya_test values(6, 12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd')); 
commit; 


--分页查询

select * from (
       select row_number() over(order by transaction_id desc)  rn, test.* 
       from dinya_test test
)where rn between 1 and 3

--查询第100条到第199条记录

select * from(select B.*,RowNumber rn from  (select * from A) B  where B.rn>=100) where rn<=199