几乎都是官方文档上的内容。
[ OFFSET offset { ROW | ROWS} ]
[ FETCH { FIRST | NEXT }[ { rowcount | percent PERCENT } ]
{ ROW| ROWS } { ONLY | WITH TIES } ]
row_limiting_clause
The row_limiting_clause allows you to limit therows returned by the query. You can
specify an offset, and number of rows or percentageof rows to return. You can use this
clause to implement top-N reporting. For consistentresults, specify the order_by_
clauseto ensure a deterministic sort order.
row_limiting_clause允许限制返回行的个数。
可以指定offset和行数（或者百分比）来返回行。
可以使用这个子句去实现top-N报表。
为保证一致性，需要指定order_by 子句以确定排列顺序。
OFFSET
Use this clause to specify the number of rows toskip before row limiting begins.
offset must be a number. If you specify a negativenumber, then offsetis treated as
0. If you specify NULL, or a number greater than orequal to the number of rows
returned by the query, then 0 rows are returned. Ifoffsetincludes a fraction, then the
fractional portion is truncated. If you do notspecify this clause, then offsetis 0 and
row limiting begins with the first row.
使用这个子句可以指定跳跃多少行开始计数。
offset必须为一个数字。
如果指定一个附属，那么会被当作0来处理。
如果指定为null，或者数字大于结果集的行数，就会返回0行。
如果offset是一个小数，那么小数点会被截取。
如果没有offset子句，那么默认为0，从第一行开始计数。
ROW | ROWS
These keywords can be usedinterchangeably and are provided for
semantic clarity.
这些关键字使语义更加准确
FETCH
Use this clause to specify the number of rows orpercentage of rows to return. If you
do not specify this clause, then all rows arereturned, beginning at row offset+ 1.
使用这个子句去指定返回行的个数或者返回行的百分比。如果没有指定，那么所有的行都会被返回，开始行为offset+1。
FIRST | NEXT
These keywords can be used interchangeably and areprovided for
semantic clarity.
这些关键字使语义更加准确
rowcount| percent PERCENT
Use rowcount to specify the number of rows toreturn.
rowcount must be a number. If you specify anegative number, then rowcountis
treated as 0. If rowcountis greater than the numberof rows available beginning at row
offset+ 1, then all available rows are returned. Ifrowcount includes a fraction, then
the fractional portion is truncated. If rowcountisNULL, then 0 rows are returned.
Use percent PERCENT to specify the percentage ofthe total number of selected rows to
return. percent must be a number. If you specify anegative number, then percentis
treated as 0. If percentis NULL, then 0 rows arereturned.
If you do not specify rowcountor percent PERCENT,then 1 row is returned.
使用rowcount去指定返回多少行。
rowcount必须为一个数字，如果指定了一个负数，那么rowcount会被当作0。如果rowcount大于以offset+1开始计数的所有行个数，那么所有的行都会被返回。
如果rowcount是一个小数，那么小数部分会被截断。如果rowcount为null，那么返回0行。
使用percent去指定返回总行数的百分比。必须为一个数字。如果指定为负数，那么会被当作0。
如果为null，那么返回0行。（其实都是一个套路嘛）
ROW | ROWS
These keywords can be usedinterchangeably and are provided for
semantic clarity.
这些关键字使语义更加准确
ONLY | WITH TIES
Specify ONLYto return exactly the specified numberof rows or
percentage of rows.
指定only会返回明确的行数或者是百分比的行数。
Specify WITH TIES to return additional rows withthe same sort key as the last row
fetched. If you specify WITH TIES, then you mustspecify the order_by_clause. If you
do not specify the order_by_clause, then noadditional rows will be returned.
如果指定with ties子句，那么拥有和最后一行相同的排序键值的行都会被fetch。如果指定了with ties子句，那么必须指定order by 。如果没有指定order by，那么不会有附加的行被返回。
Restrictions on the row_limiting_clause
This clause is subject to thefollowing
restrictions:
■ You cannot specify this clause with the for_update_clause.
■ If you specify this clause, then the select list cannot contain thesequence
pseudocolumns CURRVALor NEXTVAL.
■ Materialized views are not eligible for an incremental refreshif the defining query
contains the row_limiting_clause.
row_limiting_clause子句的限制：
无法指定for update子句
无法包含序列的伪列currentval或者nextval
如果定义的查询语句中包含row_limiting_clause，那么无法在这之上创建增量刷新的物化视图。
下面为可能发生的错误举例：
1、
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH first 5 ROWS ONLY for update ;
SELECT employee_id, last_name
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view withDISTINCT, GROUP BY, etc.
2、
SELECT seq.currval,employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
/ SELECT seq.currval,employee_id, last_name
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
3、
CREATE MATERIALIZED VIEW LOG ON employees withprimary key ; Materialized view log created. CREATE MATERIALIZED VIEW mym REFRESH FAST AS
(
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY
)
/ FETCH FIRST 5 ROWS ONLY
*
ERROR at line 6:
ORA-12015: cannot create a fast refresh materializedview from a complex query
CREATE MATERIALIZED VIEW mym REFRESH FAST AS
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
/ Materialized view created. CREATE MATERIALIZED VIEW mym
as
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY
/ FETCH FIRST 5 ROWS ONLY
*
ERROR at line 6:
ORA-00933: SQL command not properly ended
记住加括号
CREATE MATERIALIZED VIEW mym
as
(SELECTemployee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY)
/
Materialized view created.
默认是按需更新的物化视图所以没有什么问题。
先来几个例子
Row Limiting: Examples
The following statement returns the 5 employeeswith the
lowest employee_id values:
下面返回的是empid最小的5行。
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;
这里将FIRST换成NEXT或者将ROWS换成ROW都没有什么区别，但是拥有这些关键字是必须的。也证明了官档上说的它们的作用是让语义更加准确。
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
下面返回的是跳过empid最小的5行的下5行数据。
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
offset 不提供percent功能…
EMPLOYEE_ID LAST_NAME
----------- -------------------------
105 Austin
106 Pataballa
107 Lorentz
108 Greenberg
109 Faviet
下面返回的是薪水最小的5%的数据。
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500
使用了with ties子句，下面的语句返回最小薪水的5%的数据，附加和最后一行相同薪水的数据。
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
132 Olson 2100
128 Markle 2200
136 Philtanker 2200
127 Landry 2400
135 Gee 2400
119 Colmenares 2500
131 Marlow 2500
140 Patel 2500
144 Vargas 2500
182 Sullivan 2500
191 Perkins 2500
如果通过上面的例子还没有完全搞懂，那么就看下面的官方文档的翻译吧。
Perform top-N queries by specifying an offset, andthe number of rows or
percentage of rows to return.
可以通过指定偏移量、需要返回的行数来实现top-n查询