ORALCE12C分页 体验

1、登陆

sqlplus SYSTEM/MANAGER@ORALCE12C

随意找个记录多的表练习，例如：表[help]，共有938行记录

2、语法简介

语法介绍

row_limiting_clause

The row_limiting_clause allows you to limit the rows returned by the query. You can specify an offset, and number of rows or percentage of rows to return. You can use this clause to implement top-N reporting. For consistent results, specify the order_by_clause to ensure a deterministic sort order.

OFFSET

Use this clause to specify the number of rows to skip before row limiting begins. offset must be a number. If you specify a negative number, then offset is treated as 0. If you specify NULL, or a number greater than or equal to the number of rows returned by the query, then 0 rows are returned. If offset includes a fraction, then the fractional portion is truncated. If you do not specify this clause, then offsetis 0 and row limiting begins with the first row.

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

FETCH

Use this clause 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. rowcount must be a number. If you specify a negative number, then 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. If rowcount includes a fraction, then the fractional portion is truncated. If rowcount is NULL, then 0 rows are returned.

Use percent PERCENT to specify the percentage of the total number of selected rows to return. percent must be a number. If you specify a negative number, then percent is treated as 0. If percent is NULL, then 0 rows are returned.

If you do not specify rowcount or percent PERCENT, then 1 row is returned.

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

ONLY | WITH TIES Specify ONLY to return 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. If you specify WITH TIES, then you must specify theorder_by_clause. If you do not specify the order_by_clause, then no additional rows will be returned.

3、查询练习

例如：select * from help;

共有938行记录

1：获得前N条记录的语句

语法： fetch first N rows only;

例如：select * from help fetch first 5 rows only;

2：获得第N条开始后的M条的语句

语法：offset N rows fetch next M rows only;

例如：select * from help offset 3 rows fetch next 2 rows only;

3：获得前百分比N的语句

语法： fetch first N percent rows only;

例如：select * from help fetch first 1 percent rows only;