What is the usage of fetch in oracle

In Oracle, fetch is used to limit the number of rows returned by the query. You can specify the number of rows to be skipped before the row limit starts. If skipped, the offset is 0, and the row limit is calculated from the first row. , the syntax is "[OFFSET offset ROWS]FETCH NEXT ROWS[ONLY|WITH TES]".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of fetch in Oracle

The FETCH clause can be used to limit the number of rows returned by the query in Oracle. This tutorial will teach you how to use the FETCH clause.

Oracle FETCH clause syntax

The following illustrates the syntax of the row limit clause:

[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

The OFFSET clause specifies the number of rows to skip before the row limit begins. The OFFSET clause is optional. If you skip it, the offset is 0 and the row limit is calculated from the first row.

The offset must be a number or an expression whose value is a number. Offsets obey the following rules:

• If the offset is a negative value, it is treated as 0.

• If the offset is NULL or greater than the number of rows returned by the query, no rows are returned.

• If the offset contains a fraction, the fraction part is truncated.

The FETCH clause specifies the number or percentage of rows to return.

For the purpose of semantic clarity, you can use the keywords ROW instead of ROWS, and FIRST instead of NEXT. For example, the following clause behaves and produces the same result:

FETCH NEXT 1 ROWS
FETCH FIRST 1 ROW

ONLY | WITH TIES option

Returns only the number of rows or a percentage of the number of rows after FETCH NEXT (or FIRST).

WITH TIES returns the same sort key as the last row. Note that if you use WITH TIES, you must specify an ORDER BY clause in the query. If you don't do this, the query will not return additional rows.

Oracle FETCH clause example

1. Example of obtaining the first N rows of records

The following statement returns the top 10 products with the highest inventory:

-- 以下查询语句仅能在Oracle 12c以上版本执行
SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the usage of fetch in oracle. For more information, please follow other related articles on the PHP Chinese website!