1 不能和for update一起用
2 select子句不能包含sequence，如currval，nextval这些
3 如果用在物化视图里，物化视图不能增量刷新
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;
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.
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
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博客 ” ，链接：https://blog.itpub.net/26239116/viewspace-1485501/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mongodb分片balance
下一篇：
12c新的自增键方式
请登录后发表评论
登录
全部评论
