Oracle分页查询

向右看齐 2022-09-08 05:00 199阅读 0赞

## oracle 分页 ##

`oracle在11c中所使用的分页是通过rownum或者rowid进行实现，但是这样的方式实现起来相对麻烦，所以在oracle更新到12c时就引入了新的分页方式 offset 0 rows fetch next 10 rows only`

### 11C ###

#### rownum ####

在做分段查询的时候，在mysql中可以使用`limit a,b` 这种方式进行分段查询，同样的分页查询也是类似的道理，但是在ORACL中就不太一样了，如果在ORACLE中使用`limit` 就会报错命令语句未正确结束，造成这个问题的原因就是在ORACLE中不存在`limit`，那么同样的要想实现`limit分段查询的`可以使用ORACLE中的关键字**rownum**

#### 用法 ####

下面就用一个实际的例子举例说明

1.  例如我现在要查询一些符合条件的数据，那么我使用了如下的SQL进行查询，查询出了总计103条数据

![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16]  
![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 1]

1.  但是现在需求改变，不查总的数据，而是每次只查询指定数量的数据，这时候或许就会有人想既然可以使用rownum进行类似于limit这样的查询那就使用`rownum a,b`这种方式是不可取的，因为语法就不对。
2.  那百度后发现rownum可以使用`>或<`进行取范围值，那就直接在条件后面加上`rownum >=0 and rownum <=10` 如果这样查确实能查出前10条数据，那如果换成`rownum >=10 and rownum <=20`这样就无法查询出`第10条到第20条`而是为空集

#### 实现 ####

所以想使用rownum实现分段查询那么就要理解rownum的原理并使用到子查询，其实rownum就是行号。例如上面我使用该条SQL已经能查询出总的数据量是103条，那么就可以在这个SQL里面加入rownum这个字段并且取一个别名，并且`将这条SQL作为一个查询的子查询`

1.  如下所示，在以上查询的SQL外面套一个查询，也就是将前面查询出103条数据的SQL作为一个子查询

select
    	*
    from
    	( `查询出了103条数据的SQL语句`  ) t
    where
    	t.rn between 101 and 200

并且在查询出为103条数据的查询字段中加入`rownum rn` 其中rn是rownum的别名，上面的`t`是子查询的别名,而`101，200就是查询的范围`

1.  总的SQL如下  
    ![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
    ![请添加图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 3]

通过以上的这种方式就实现了ORACLE中类似于`limit的分段查询`，同样的如果考虑到效率问题可以将select \* 换成

select 字段，而这些字段就是子查询中查询的字段值。

#### 思路 ####

1.  写好总的查询SQL，就是查询所有数据的
2.  在该条SQL里面加入rownnum字段，并且给该条子查询SQL取别名
3.  将该条SQL做为子查询嵌套在内侧
4.  使用`子查询别名.rownum别名`的方式给定一个范围

#### mybatis-plus中分页实现 ####

其实在mybatis-plus中也时通过解析拼接sql的方式实现了分页的效果，如下：

就是使用了ROWNUM，ROW\_ID实现，而这两个时oracle中自带的关键子，不存在于查询的表中，所以能较好的实现分页效果。

// oracle实现
    	public DialectModel buildPaginationSql(String originalSql, long offset, long limit) { 
             limit = offset >= 1L ? offset + limit : limit;
             String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " + originalSql + " ) TMP WHERE ROWNUM <=" + "?" + ") WHERE ROW_ID > " + "?";
            return (new DialectModel(sql, limit, offset)).setConsumerChain();
        }

### 12C ###

通过上面的语句可以看到，在11c中主要是使用了`ROWNUM，ROW_ID`, 如果初次接触处理不当可能就会在查询结果中带有行号ROWNUM，当然如果参照着mybatis-plus中分页实现方式就能较好的避免这个问题。但是通过观察却能发现一个问题就是，上面的SQL看起来有些复杂，所以在oracle 12c的版本中引入了一个新的分页方式`offset " + x + " rows fetch next " + y + " rows only"`

//分页SQL处理
    String strSql = sql + " offset " + x + " rows fetch next " + y + " rows only";

其中x表是从第几条取值，y表示每次取多少条。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/c89256dbd2054cc9b0599fe84c18161a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/0d7111f382cf45c9a394bc39967b7c41.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220829/63a3c00fe9474e7cb806d06506078691.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5ZSQ5b6u5riv_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220829/e29889b5eb09423cb4f6f0738f9b452d.png