總結(jié)下Oracle中三種常用的分頁查詢方法?。?！

一、使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢

ROWNUM是一個偽列，用于記錄返回結(jié)果集中每一行的行號。ROWNUM是在查詢結(jié)果返回之后計算的，因此它并不是存儲在表中的實(shí)際列。

ROWNUM的作用是用于限制查詢結(jié)果的行數(shù)，可以在SELECT語句中使用WHERE子句和ORDER BY子句，實(shí)現(xiàn)分頁查詢或篩選查詢結(jié)果。

命令格式：

SELECT * 
FROM ( SELECT t.*, 
       ROWNUM rn 
       FROM table_name t 
       WHERE ROWNUM <= end_row ) 
WHERE rn > start_row;

其中，start_row和end_row分別表示查詢的起始行和結(jié)束行。

舉例說明：

查詢emp表前五行

select *
from emp
where rownum <=5;

查詢表student中第6行到第10行的數(shù)據(jù)

SELECT * 
FROM ( SELECT t.*, 
       ROWNUM rn 
       FROM student t 
       WHERE ROWNUM <= 10 ) 
WHERE rn > 5;

查詢工資最高的前5人

select *
from (
select ename
from emp
order by sal desc)
where rownum<=5;

 查詢工資最高的6-10名

select ename
from (select rownum a,e.*
      from(select ename
           from emp 
           order by sal desc) e)
where a between 6 and 10;

  rownum主要是用在分頁查詢，引入一個特殊使用符號：宏代換 &；

select &a,'&b',date'&c'
from dual;

 對每一個參數(shù)有類型限制，比如a是數(shù)值型，b是字符型，c是日期型

點(diǎn)擊運(yùn)行，輸入?yún)?shù)變量，如圖所示：

 執(zhí)行結(jié)果如下：

 例如：員工表emp表每三行為一頁，查詢第二頁到第五頁的數(shù)據(jù)

select *
from (select rownum a,e.* from emp e)
where a between &a*3-2 and &b*3;

 點(diǎn)擊運(yùn)行，輸入?yún)?shù)變量，如圖所示：

  執(zhí)行結(jié)果如下：

 按照工資由多到少排序，每頁4個人

select *
from (select rownum 行號,e.*
      from (select * 
            from emp
            order by sal desc) e)
where 行號 between &a*4-3 and &b*4;

 點(diǎn)擊運(yùn)行，輸入?yún)?shù)變量，如圖所示：

執(zhí)行結(jié)果如下：

注意事項：

使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢需要注意以下幾點(diǎn)：

1. ROWNUM是Oracle數(shù)據(jù)庫中的一個偽列，它不是表中的實(shí)際列，而是Oracle數(shù)據(jù)庫為了方便查詢而自動添加的一個列。

2. ROWNUM是在查詢結(jié)果返回之后再進(jìn)行排序的，因此需要使用子查詢來實(shí)現(xiàn)分頁查詢（即使用        ROWNUM時需要注意它只能用于限制返回結(jié)果的行數(shù)，不能用于篩選查詢結(jié)果，因?yàn)?nbsp;              ROWNUM是在查詢結(jié)果返回之后計算的。如果需要篩選查詢結(jié)果，應(yīng)該使用子查詢和WHERE子  句）。

3. 在使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢時，需要注意排序的方式，以確保查詢結(jié)果的正確性。

4. 注意分頁查詢的性能問題，對于大型表可能會影響查詢效率，需要進(jìn)行優(yōu)化。

5. 在使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢時，需要注意數(shù)據(jù)的一致性，如果查詢過程中有其他事務(wù)對數(shù)據(jù)進(jìn)行了修改，則可能會導(dǎo)致查詢結(jié)果不準(zhǔn)確。

6. 使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢時，需要注意查詢語句的語法，以確保語句的正確性。

二、使用OFFSET和FETCH NEXT語句實(shí)現(xiàn)分頁查詢

OFFSET和FETCH NEXT是用于實(shí)現(xiàn)分頁查詢的關(guān)鍵字。其中OFFSET用于指定需要跳過的行數(shù)，F(xiàn)ETCH NEXT用于指定需要返回的行數(shù)，兩者結(jié)合起來可以實(shí)現(xiàn)分頁查詢。

命令格式：

SELECT * 
FROM table_name 
OFFSET start_row ROWS 
FETCH NEXT number_of_rows ROWS ONLY;

其中，start_row表示查詢的起始行，number_of_rows表示每頁顯示的行數(shù)。

舉例說明：

查詢表student中第6行到第10行的數(shù)據(jù)

SELECT * 
FROM student 
OFFSET 5 ROWS 
FETCH NEXT 5 ROWS ONLY;

注意事項：

使用OFFSET和FETCH NEXT實(shí)現(xiàn)分頁查詢需要注意以下幾點(diǎn)：

1. OFFSET和FETCH NEXT是Oracle 12c及以上版本才支持的新特性，因此在Oracle11g中無法使用這種方式實(shí)現(xiàn)分頁查詢。

2. 在使用OFFSET和FETCH NEXT實(shí)現(xiàn)分頁查詢時，需要指定偏移量和要返回的行數(shù)，如果不指定偏移量，則默認(rèn)從第一行開始查詢。

3. OFFSET和FETCH NEXT可以在ORDER BY子句中使用，以確保查詢結(jié)果的正確性。

4. 注意分頁查詢的性能問題，對于大型表可能會影響查詢效率，需要進(jìn)行優(yōu)化。

5. 在使用OFFSET和FETCH NEXT實(shí)現(xiàn)分頁查詢時，需要注意數(shù)據(jù)的一致性，如果查詢過程中有其他事務(wù)對數(shù)據(jù)進(jìn)行了修改，則可能會導(dǎo)致查詢結(jié)果不準(zhǔn)確。

6. OFFSET和FETCH NEXT可以與其他查詢語句一起使用，例如JOIN、WHERE、GROUP BY等，以實(shí)現(xiàn)更復(fù)雜的查詢需求。

三、使用子查詢實(shí)現(xiàn)分頁查詢

命令格式：

SELECT * 
FROM ( SELECT t.*, 
       ROW_NUMBER() OVER (ORDER BY column_name) rn 
       FROM table_name t ) subquery
WHERE rn BETWEEN start_row AND end_row;

其中，ROW_NUMBER()函數(shù)用于生成行號，subquery是子查詢的別名，start_row和end_row是起始行和終止行的行號，column_name表示用于排序的列名。

舉例說明：

查詢表student中第6行到第10行的數(shù)據(jù)

SELECT * 
FROM ( SELECT t.*, 
       ROW_NUMBER() OVER (ORDER BY id) rn 
       FROM student t ) 
WHERE rn BETWEEN 6 AND 10;

注意事項：

使用子查詢實(shí)現(xiàn)分頁查詢時需要注意以下幾點(diǎn)：

1. 子查詢必須加上別名，否則會報錯。

2. 分頁查詢時必須使用ROW_NUMBER()函數(shù)生成行號，并將其作為子查詢的一部分。

3. 子查詢中需要指定排序方式，以確保分頁查詢的正確性。

4. 分頁查詢時需要指定起始行和終止行的行號，以確定查詢的范圍。

5. 注意分頁查詢的性能問題，對于大型表可能會影響查詢效率，需要進(jìn)行優(yōu)化。

6. 子查詢中的WHERE條件可以用來過濾數(shù)據(jù)，但是應(yīng)該盡量避免使用過于復(fù)雜的WHERE條件，以免影響查詢性能。

四、三種方法對比

1、使用子查詢實(shí)現(xiàn)分頁查詢的優(yōu)勢是可以更靈活地控制查詢的條件和排序方式，可以在子查詢中使用WHERE和ORDER BY語句進(jìn)行過濾和排序，同時可以在主查詢中使用OFFSET和FETCH NEXT語句進(jìn)行分頁操作，可以控制返回的結(jié)果集的數(shù)量和起始位置。這種方法的好處是可以實(shí)現(xiàn)更復(fù)雜的查詢，例如在查詢結(jié)果中進(jìn)行嵌套，或者按照多個條件進(jìn)行排序。

2、使用OFFSET和FETCH NEXT實(shí)現(xiàn)分頁查詢的優(yōu)勢是語法簡單明了，可以很容易地指定需要返回的結(jié)果集數(shù)量和起始位置。這種方法的好處是在需要簡單的分頁查詢時，可以使用更少的代碼實(shí)現(xiàn)，同時可以提高查詢效率。 除此之外，這種分頁查詢方式相對于ROWNUM方式更加靈活，可以實(shí)現(xiàn)跳過指定行數(shù)后返回指定行數(shù)的查詢結(jié)果。

3、使用ROWNUM函數(shù)實(shí)現(xiàn)分頁查詢的優(yōu)勢是語法簡單明了，只需要在WHERE語句中使用ROWNUM進(jìn)行限制即可。這種方法的好處是在需要簡單的分頁查詢時，可以使用更少的代碼實(shí)現(xiàn)，同時也可以提高查詢效率。

總結(jié)：

選擇使用哪種方法取決于具體的查詢需求和場景。如果需要進(jìn)行復(fù)雜的查詢條件和排序方式，使用子查詢實(shí)現(xiàn)分頁查詢更為適合；如果只需要簡單的分頁查詢，使用OFFSET和FETCH NEXT或ROWNUM函數(shù)實(shí)現(xiàn)都可以。

到此這篇關(guān)于Oracle中3種常用的分頁查詢方法的文章就介紹到這了,更多相關(guān)Oracle分頁查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論