12c新的分页方式

1 不能和for update一起用
2 select子句不能包含sequence，如currval，nextval这些
3 如果用在物化视图里，物化视图不能增量刷新

创建测试表

drop table t purge;
create table t (id int, name varchar2(20));
insert into t select level, 'name'||level from dual connect by level < 100;
commit;

返回前n行

SQL> select * from t where id between 30 and 70 order by id   fetch next 3 rows only;

        ID NAME
---------- --------------------------------------------------
        30 name30
        31 name31
        32 name32

分页
SQL> select * from t where id between 30 and 70 order by id   offset 30 rows fetch next 3 rows only;

        ID NAME
---------- --------------------------------------------------
        60 name60
        61 name61
        62 name62

返回百分之n行
SQL> select * from t where id between 30 and 70 order by id   fetch first 25 percent rows only;

        ID NAME
---------- --------------------------------------------------
        30 name30
        31 name31
        32 name32
        33 name33
        34 name34
        35 name35
        36 name36
        37 name37
        38 name38
        39 name39
        40 name40

11 rows selected.

返回并列前n行

插入重复数据

insert into t select * from t where id = 2;
insert into t select * from t where id = 2;
insert into t select * from t where id = 2;

commit;

普通智能严格返回前2行

SQL> select * from t order by id fetch next 2 rows   only;

        ID NAME
---------- --------------------------------------------------
         1 name1
         2 name2 

使用with ties 后，就能把“并列第二”的全都显示出来
SQL> select * from t order by id fetch next 2 rows   with ties;

        ID NAME
---------- --------------------------------------------------
         1 name1
         2 name2
         2 name2
         2 name2
         2 name2
         2 name2
         2 name2
         2 name2
         2 name2

9 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-1485501/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-1485501/