oracle分页技术

分页场景在现实生活中很常见，所以我们需要懂得如何利用sql语句实现数据的分页检索。

步骤一：创建测试表

create table test (datetime date,str varchar2(32))
/

步骤二：插入测试数据

declare
v1 date:=to_date('20160801010000','yyyymmddhh24miss');
begin
for i in 1 .. 30 loop
insert into test values (v1,'hello');
v1:=v1+1/1440;
end loop;
commit;
end;
/

步骤三：编写sql语句实现分页（核心）（假设一页显示3条数据）

1.显示1~3条数据

select t2.*
from (
select t1.*,rownum rowindex from
(select * from test order by datetime asc) t1
) t2 where t2.rowindex>=1 and t2.rowindex<=(1+3-1)
/

2.显示4~6条数据

select t2.*
from (
select t1.*,rownum rowindex from
(select * from test order by datetime asc) t1
) t2 where t2.rowindex>=4 and t2.rowindex<=(4+3-1)
/

3.显示M~(M+3-1)条数据

select t2.* from ( select t1.*,rownum rowindex from (select * from test order by datetime asc) t1 ) t2 where t2.rowindex>=M and t2.rowindex<=(M+3-1) /

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!