Oracle12c新特性 – 分页查询(TOP-N SQL)

1.语法说明

Oracle12c提供了新的SQL分页语法,TOP N的写法如下:

主要涉及到的关键字有:

  • OFFSET

A numeric value that specifies the number of rows to skip before row limiting begins. If offset is negative or no offset is specified, the offset defaults to 0 and row limiting begins with the first row. If Offset is NULL, or a number greater than or equal to the number of rows returned by the query,then 0 rows are returned. Fractional portion is truncated if offset is a fraction.

  • FETCH

This is used to specify the number of rows or percentage of rows to return. If you do not specify this clause, then all rows are returned, beginning at row offset + 1.

  • FIRST | NEXT

These keywords can be used interchangeably and are provided for semantic clarity.

  • Rowcount | percent PERCENT

Use rowcount to specify the number of rows to return. Negative or NULL rowcount is treated as 0. If rowcount is greater than the number of rows available beginning at row offset + 1, then all available rows are returned. Fractional portion is truncated if rowcount is a fraction.
Use percent PERCENT to specify the percentage of the total number of selected rows to return.
If you do not specify rowcount or percent PERCENT, then 1 row is returned.

  • ONLY | WITH TIES

ONLY returns exactly the specified number of rows or percentage of rows.
Specify WITH TIES to return additional rows with the same sort key as the last row fetched. WITH TIES must be specified with the order_by_clause, otherwise, no additional rows will be returned.

在Oracle 12c之前的版本,Oracle分页主要是使用ROWNUM伪列+子查询实现,如下:

SQL> select EMPNO, ename, job from (
  2  select EMPNO, ename, job, rownum rn
  3  from emp
  4  where rownum < 10   5  ) where rn > 5;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT

2. SQL写法样例

2.1 取前5行

SQL> select EMPNO, ename, job from emp FETCH FIRST 5 ROWS ONLY;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN

2.2 跳过5行取后5行

SQL> select EMPNO, ename, job from emp OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;   

     EMPNO ENAME      JOB
---------- ---------- ---------
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN

2.3 取数据行百分比

注意:offset不提供percent功能
+ 取5%的数据量

SQL> select EMPNO, ename, job from emp FETCH FIRST 5 PERCENT ROWS ONLY;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK

2.4 WITH TIES

当SQL语句中有WITH TIES关键字,那会把ORDER BY值相同的数据行全部取出。如:

SQL> select EMPNO, ename, job, sal from emp FETCH FIRST 5 PERCENT ROWS WITH TIES;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
SQL> select EMPNO, ename, job, sal from emp order by sal FETCH FIRST 5 PERCENT ROWS WITH TIES;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7934 MILLER     CLERK            800

3.row_limiting_clause限制

无法和for update一起使用
无法带sequence伪列:CURRVAL 或 NEXTVAL
无法在带row_limiting_clause子句的SELECT语句上创建增量刷新的物化视图

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。