在12.1之前,Oracle不支持LIMIT或OFFSET关键字。如果要检索结果集的行N到M,您需要类似以下内容:
SELECT a.*
FROM (SELECT b.*,
rownum b_rownum
FROM (SELECT c.*
FROM some_table c
ORDER BY some_column) b
WHERE rownum <= <>) a
WHERE b_rownum >= <>
或使用分析函数:
SELECT a.*
FROM (SELECT b.*,
rank() over (order by some_column) rnk
FROM some_table)
WHERE rnk BETWEEN <> AND <>
ORDER BY some_column
这两种方法中的任一种将排序给出排序结果的行N到M.
在12.1和更高版本中,可以使用OFFSET和/或FETCH [FIRST | NEXT]运算符:
SELECT *
FROM some_table
ORDER BY some_column
OFFSET <> ROWS
FETCH NEXT <> ROWS ONLY