Oracle Fetch子句


在本教學中將學習如何使用Oracle FETCH子句來限制查詢返回的行數。

Oracle FETCH子句簡介

一些RDBMS(如MySQLPostgreSQL)使用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而不是ROWSFIRST而不是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列的值與第10quantity列的值相同。

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子句來限制查詢返回的行。