Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use fetch in oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

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 the row limit is 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: Windows10 system, Oracle 11g version, Dell G3 computer.

What is the use of fetch in oracle

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

Oracle FETCH clause syntax

The syntax of the row restriction clause is explained as follows:

[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 line.

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

If the offset is negative, 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 semantic clarity, you can use the keyword ROW instead of ROWS,FIRST instead of NEXT. For example, the following clause behaves and produces the same result:

FETCH NEXT 1 ROWSFETCH FIRST 1 ROW

ONLY | WITH TIES option

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

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

Oracle FETCH clause instance

1. Get an example of the first N rows of records

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

-- the following query statement can only execute SELECT product_name in Oracle 12c or above. This is the end of the article on "how to use fetch in oracle" by quantityFROM inventoriesINNER JOIN products USING (product_id) ORDER BY quantity DESC FETCH NEXT 5 ROWS ONLY;. I hope the above content will be helpful to you so that you can learn more knowledge. If you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.