Oracle Fetch子句

作者：--    发布时间：2019-11-20

在本教程中将学习如何使用oracle fetch子句来限制查询返回的行数。

oracle fetch子句简介

一些rdbms(如mysql和postgresql)使用limit子句来检索查询生成的一部分行记录。

请参阅示例数据库中的产品(products)和库存(inventories)表。两个表的结构和关系如下所示 -

以下查询使用limit子句获得库存量最高的前5个产品：

select
   product_name,
   quantity
from
    inventories
inner join products
    using(product_id)
order by
    quantity desc 
limit 5;

在此示例中，order by子句按降序对库存数量(quantity)进行排序，limit子句仅返回库存数量最多的前5个产品。

oracle数据库标准中没有limit子句。 然而，自12c发布以来，它提供了一个类似但更灵活的子句，即行限制子句。

通过使用行限制子句，重写上面的limit子句的查询，如下所示：

select
    product_name,
    quantity
from
    inventories
inner join products
        using(product_id)
order by
    quantity desc 
fetch next 5 rows only;

注意：上面查询仅能在orace 12c以上版本中运行。

如果使用的是oracle 11g及以下版本的，请参考以下语句 -

select
    product_name, quantity
from
    inventories
inner join products
        using(product_id)
where  rownum<=5
order by quantity desc;

执行上面查询语句，得到以下结果 -

在这个语句中，行限制子句是：

fetch next 5 rows only

与上面使用limit子句的语句类似，行限制子句返回库存量最高的前5个产品。

oracle fetch子句语法

以下说明了行限制子句的语法：

[ offset offset rows]
 fetch  next [  row_count | percent percent  ] rows  [ only | with ties ]

offset子句

offset子句指定在行限制开始之前要跳过行数。offset子句是可选的。 如果跳过它，则偏移量为0，行限制从第一行开始计算。

偏移量必须是一个数字或一个表达式，其值为一个数字。偏移量遵守以下规则：

• 如果偏移量是负值，则将其视为0。
• 如果偏移量为null或大于查询返回的行数，则不返回任何行。
• 如果偏移量包含一个分数，则分数部分被截断。

fetch子句

fetch子句指定要返回的行数或百分比。

为了语义清晰的目的，您可以使用关键字row而不是rows，first而不是next。 例如，以下子句的行为和产生的结果相同：

fetch next 1 rows
fetch first 1 row

only | with ties选项

仅返回fetch next(或first)后的行数或行数的百分比。

with ties返回与最后一行相同的排序键。请注意，如果使用with ties，则必须在查询中指定一个order by子句。如果不这样做，查询将不会返回额外的行。

oracle fetch子句的例子

1. 获取前n行记录的示例

以下语句返回库存量最高的前10个产品：

-- 以下查询语句仅能在oracle 12c以上版本执行
select
    product_name,
    quantity
from
    inventories
inner join products
        using(product_id)
order by
    quantity desc 
fetch next 5 rows only;

执行上面查询语句，得到以下结果 -

2. with ties示例

以下查询使用with ties选项的行限制子句：

-- 以下查询语句仅能在oracle 12c以上版本执行
select
 product_name,
 quantity
from
 inventories
inner join products
 using(product_id)
order by
 quantity desc 
fetch next 10 rows with ties;

执行上面示例查询语句，得到以下结果 -

即使查询请求了10行数据，因为它具有with ties选项，查询还返回了另外两行。 请注意，这两个附加行在quantity列的值与第10行quantity列的值相同。

3. 以百分比限制返回行的示例

以下查询返回库存量最高的前1％的产品：

-- 以下查询语句仅能在oracle 12c以上版本执行
select
    product_name,
    quantity
from
    inventories
inner join products
        using(product_id)
order by
    quantity desc 
fetch first 1 percent rows only;

执行上面示例查询语句，得到以下结果 -

库存(inventories)表总共有1112行，因此，1112中的1％是11.1，四舍五入为12(行)。

4. offset示例

以下查询将跳过库存量最高的前10个产品，并返回接下来的10个产品：

-- 以下查询语句仅能在oracle 12c以上版本执行
select
 product_name,
 quantity
from
 inventories
inner join products
 using(product_id)
order by
 quantity desc 
offset 10 rows 
fetch next 10 rows only;

注意：这个功能可以用于分页的实现。

执行上面查询语句，得到以下结果 -

在本教程中，您已学习如何使用oracle fetch子句来限制查询返回的行。