Oracle中的分页查询

淩亂°似流年 2024-03-22 14:58 47阅读 0赞

**目录**

一、使用ROWNUM函数实现分页查询

命令格式：

注意事项：

二、使用OFFSET和FETCH NEXT语句实现分页查询

命令格式：

注意事项：

四、使用子查询实现分页查询

命令格式：

注意事项：

四、三种方法对比

--------------------

总结下Oracle中三种常用的分页查询方法！！！

## 一、使用ROWNUM函数实现分页查询 ##

ROWNUM是一个伪列，用于记录返回结果集中每一行的行号。ROWNUM是在查询结果返回之后计算的，因此它并不是存储在表中的实际列。

ROWNUM的作用是用于限制查询结果的行数，可以在SELECT语句中使用WHERE子句和ORDER BY子句，实现分页查询或筛选查询结果。

### 命令格式： ###

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

其中，start\_row和end\_row分别表示查询的起始行和结束行。

举例说明：

查询emp表前五行

select *
    from emp
    where rownum <=5;

查询表student中第6行到第10行的数据

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;

对每一个参数有类型限制，比如a是数值型，b是字符型，c是日期型

点击运行，输入参数变量，如图所示：

![877bfc0766a44761b0d8599cf7d59e0f.png][]

执行结果如下：

![b80cafe3cc324d55bd8f792fc59b524d.png][]

例如：员工表emp表每三行为一页，查询第二页到第五页的数据

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

点击运行，输入参数变量，如图所示：

![2f389b8daa27485ebdce9011a12a2429.png][]

执行结果如下：

![0656ba8358f34ce086856a5cc707aa69.png][]

按照工资由多到少排序，每页4个人

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

点击运行，输入参数变量，如图所示：

![979aff75379a49f2ab580815777217eb.png][]

执行结果如下：

![1b871fab5512444da9e62a7dba0f2c6f.png][]

### 注意事项： ###

使用ROWNUM函数实现分页查询需要注意以下几点：

1. ROWNUM是Oracle数据库中的一个伪列，它不是表中的实际列，而是Oracle数据库为了方便查询而自动添加的一个列。

2. ROWNUM是在查询结果返回之后再进行排序的，因此需要使用子查询来实现分页查询（即使用 ROWNUM时需要注意它只能用于限制返回结果的行数，不能用于筛选查询结果，因为 ROWNUM是在查询结果返回之后计算的。如果需要筛选查询结果，应该使用子查询和WHERE子 句）。

3. 在使用ROWNUM函数实现分页查询时，需要注意排序的方式，以确保查询结果的正确性。

4. 注意分页查询的性能问题，对于大型表可能会影响查询效率，需要进行优化。

5. 在使用ROWNUM函数实现分页查询时，需要注意数据的一致性，如果查询过程中有其他事务对数据进行了修改，则可能会导致查询结果不准确。

6. 使用ROWNUM函数实现分页查询时，需要注意查询语句的语法，以确保语句的正确性。

## 二、使用OFFSET和FETCH NEXT语句实现分页查询 ##

OFFSET和FETCH NEXT是用于实现分页查询的关键字。其中OFFSET用于指定需要跳过的行数，FETCH NEXT用于指定需要返回的行数，两者结合起来可以实现分页查询。

### 命令格式： ###

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

其中，start\_row表示查询的起始行，number\_of\_rows表示每页显示的行数。

举例说明：

查询表student中第6行到第10行的数据

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

### 注意事项： ###

使用OFFSET和FETCH NEXT实现分页查询需要注意以下几点：

1. OFFSET和FETCH NEXT是Oracle 12c及以上版本才支持的新特性，因此在Oracle11g中无法使用这种方式实现分页查询。

2. 在使用OFFSET和FETCH NEXT实现分页查询时，需要指定偏移量和要返回的行数，如果不指定偏移量，则默认从第一行开始查询。

3. OFFSET和FETCH NEXT可以在ORDER BY子句中使用，以确保查询结果的正确性。

4. 注意分页查询的性能问题，对于大型表可能会影响查询效率，需要进行优化。

5. 在使用OFFSET和FETCH NEXT实现分页查询时，需要注意数据的一致性，如果查询过程中有其他事务对数据进行了修改，则可能会导致查询结果不准确。

6. OFFSET和FETCH NEXT可以与其他查询语句一起使用，例如JOIN、WHERE、GROUP BY等，以实现更复杂的查询需求。

## 四、使用子查询实现分页查询 ##

### 命令格式： ###

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()函数用于生成行号，subquery是子查询的别名，start\_row和end\_row是起始行和终止行的行号，column\_name表示用于排序的列名。

举例说明：

查询表student中第6行到第10行的数据

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

### 注意事项： ###

使用子查询实现分页查询时需要注意以下几点：

1. 子查询必须加上别名，否则会报错。

2. 分页查询时必须使用ROW\_NUMBER()函数生成行号，并将其作为子查询的一部分。

3. 子查询中需要指定排序方式，以确保分页查询的正确性。

4. 分页查询时需要指定起始行和终止行的行号，以确定查询的范围。

5. 注意分页查询的性能问题，对于大型表可能会影响查询效率，需要进行优化。

6. 子查询中的WHERE条件可以用来过滤数据，但是应该尽量避免使用过于复杂的WHERE条件，以免影响查询性能。

## 四、三种方法对比 ##

1、使用子查询实现分页查询的优势是可以更灵活地控制查询的条件和排序方式，可以在子查询中使用WHERE和ORDER BY语句进行过滤和排序，同时可以在主查询中使用OFFSET和FETCH NEXT语句进行分页操作，可以控制返回的结果集的数量和起始位置。这种方法的好处是可以实现更复杂的查询，例如在查询结果中进行嵌套，或者按照多个条件进行排序。

2、使用OFFSET和FETCH NEXT实现分页查询的优势是语法简单明了，可以很容易地指定需要返回的结果集数量和起始位置。这种方法的好处是在需要简单的分页查询时，可以使用更少的代码实现，同时可以提高查询效率。 除此之外，这种分页查询方式相对于ROWNUM方式更加灵活，可以实现跳过指定行数后返回指定行数的查询结果。

3、使用ROWNUM函数实现分页查询的优势是语法简单明了，只需要**在WHERE语句中使用ROWNUM进行限制**即可。这种方法的好处是在需要简单的分页查询时，可以使用更少的代码实现，同时也可以提高查询效率。

> **总结：**
> 
> 选择使用哪种方法取决于具体的查询需求和场景。如果需要***进行复杂的查询条件和排序方式，使用子查询实现分页查询更为适合***；如果只需要***简单的分页查询，使用OFFSET和FETCH NEXT或ROWNUM函数实现都可以***。

[877bfc0766a44761b0d8599cf7d59e0f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d51f4c9d9a4645f7b34f0948c905fc10.png
[b80cafe3cc324d55bd8f792fc59b524d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/e5afe1be27724bc0b3d3524e980df71b.png
[2f389b8daa27485ebdce9011a12a2429.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/50d8ae2674c140af96bda8117a531d59.png
[0656ba8358f34ce086856a5cc707aa69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/2c5e13fd29ad46a3ab9b6e57fa1d1da8.png
[979aff75379a49f2ab580815777217eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/4ae6c4217ca54fe1b8f58b42c7b2f28c.png
[1b871fab5512444da9e62a7dba0f2c6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/5df4a4dccc1e4e0793a46d87c8e277a6.png