Oracle | 拾忆🍂

Oracle中分页查询语句

Oracle分页查询语句使我们最常用的语句之一，下面就为您介绍的Oracle分页查询语句的用法，如果您对此方面感兴趣的话，不妨一看。

Oracle分页查询语句基本上可以按照本文给出的格式来进行套用。
Oracle分分页查询格式：

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个Oracle分分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。查询语句如下：

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

分页计算方式：

//page是页数，rows是显示行数
int page=2;
int rows=5;                            
List<Articles> list=a.select(page*rows+1，(page-1)*rows);
//  sql语句：  select * from(select a.*,rownum rn from (select * from t_articles) a where rownum < 11) where rn>5
//第一个参数，对应着第一个rownum<11,第二个参数对应着rn>5

分页查询

在 Oracle 数据库中，进行分页查询有几种方法。让我们一起探讨一下这些方法：

使用 ROWNUM 实现分页查询：
- 首先，我们有一个名为 emp 的表（这是 Oracle 中的 scott 用户自带的表）。
- 假设我们要分页查询该表，每页显示 5 条记录。
- 下面是使用ROWNUM函数实现分页查询的示例 SQL 语句：
```
SELECT *
FROM (SELECT ROWNUM AS no, a.*
      FROM emp a)
WHERE no BETWEEN (pageNo - 1) * pageSize + 1 AND pageNo * pageSize;
```
  其中：
  - pageNo 代表当前页码。
  - pageSize 代表每页显示的数据条数。
  - 例如，如果当前是第 1 页，每页显示 5 条，那么上述 SQL 语句将查询出第 1 到 5 条记录。
  - BETWEED AND 为闭区间，包含两端的值。
使用 ROWNUM + ORDER BY 实现分页查询：
- 为了使用 ROWNUM 和 ORDER BY 从 Oracle 数据库中获取第5到第10条数据，可以使用子查询的方式。以下是具体的实现方法：
  1. 嵌套查询：首先使用一个子查询对数据进行排序并分配 ROWNUM。
  2. 外层查询：在外层查询中进行行号过滤。
- SQL 示例
  假设有一张表 T_PRINT_BLOCK_ERR_BILL，我们需要查询 ACCOUNT_NO 为特定值的记录，并获取按 END_DT 排序的第5到第10行数据。
```
SELECT *
FROM (
    SELECT a.*, ROWNUM rnum
    FROM (
        SELECT END_DATE, other_columns -- 选择需要的列
        FROM T_PRINT_BLOCK_ERR_BILL
        WHERE ACCOUNT_NO = #{accountNo}
        ORDER BY END_DATE DESC
    ) a
    WHERE ROWNUM <= 10
)
WHERE rnum >= 5;
```
- 解释
  - 内层查询 a 对 T_PRINT_BLOCK_ERR_BILL 表按 END_DT 列进行排序。
  - ROWNUM <= 10 确保在内层查询中只获取前10行数据，这样可以避免不必要的全表扫描。
  - 外层查询过滤出 ROWNUM 在5到10之间的数据。
使用 OFFSET 和 FETCH 子句实现分页查询：
- 这是一种更现代的方法，适用于 Oracle 12c 及更高版本。
- 示例 SQL 语句如下：
```
SELECT *
FROM emp
OFFSET (pageNo - 1) * pageSize ROWS
FETCH NEXT pageSize ROWS ONLY;
```
  其中：
  - pageNo 代表当前页码。
  - pageSize 代表每页显示的数据条数。
使用窗口函数 ROW_NUMBER() 实现分页查询：
- ROW_NUMBER() OVER () 是 Oracle 数据库中的一个窗口函数，为查询结果中的每一行分配一个唯一的行号。
- 示例 SQL 语句如下：
```
SELECT *
FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY empno) AS rn
      FROM emp a)
WHERE rn BETWEEN 5 AND 10;
```
  这将查询出emp表中按empno列排序后的第 5 到 10 条记录。

无论您选择哪种方法，都可以实现分页查询功能。请根据您的需求和数据库版本选择适合的方法。1 2 3 4 5

了解详细信息:1. blog.csdn.net 2. zhihu.com 3. cloud.baidu.com 4. cnblogs.com 5. blog.csdn.net

ROWNUM

查第一条

SELECT END_DT
FROM (
        select ...
    )
WHERE ROWNUM = 1

DB_LINK

创建

CREATE DATABASE LINK zwdb_prod
CONNECT TO remote_user IDENTIFIED BY remote_password
USING '(DESCRIPTION=
          (ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=1521))
          (CONNECT_DATA=(SERVICE_NAME=remote_service_name))
       )';

使用数据库链接进行查询的示例

一旦创建了数据库链接，你就可以使用它来访问远程数据库中的表。例如：

SELECT COUNT(*)
FROM auth_user_relate_postn@zwdb_prod
WHERE login = 'some_login_value';

示例解释

remote_host：远程数据库所在的主机名或 IP 地址。
1521：远程数据库监听的端口号（通常是 1521，但可能会有所不同）。
remote_service_name：远程数据库的服务名（可以通过 Oracle Net 服务名配置文件 tnsnames.ora 找到）。

完整示例

假设具体参数如下：

远程主机名：remote_db_host
端口：1521
服务名：remote_service
用户名：remote_user
密码：remote_password

你可以这样创建数据库链接并查询：

-- 创建数据库链接
CREATE DATABASE LINK zwdb_prod
CONNECT TO remote_user IDENTIFIED BY remote_password
USING '(DESCRIPTION=
          (ADDRESS=(PROTOCOL=TCP)(HOST=remote_db_host)(PORT=1521))
          (CONNECT_DATA=(SERVICE_NAME=remote_service))
       )';

-- 使用数据库链接进行查询
SELECT COUNT(*)
FROM auth_user_relate_postn@zwdb_prod
WHERE login = 'some_login_value';

这样就可以通过名为 zwdb_prod 的数据库链接，连接到远程数据库，并查询 auth_user_relate_postn 表中的数据。

查询有哪些DB_LINK

在Oracle数据库中，数据库链接（database link，简称dblink）是用于连接到另一个数据库实例或者同一数据库实例中的另一个用户的对象。您可以使用以下方法查找数据库链接的定义和信息：

查询所有数据库链接：
可以查询数据库中定义的所有数据库链接，可以通过以下 SQL 查询语句来完成：
```
SELECT * FROM dba_db_links;
```
如果您没有 DBA 权限，也可以查询当前用户有权限访问的数据库链接：
```
SELECT * FROM all_db_links;
```
如果您只想查看当前会话中可用的链接，可以使用 user_db_links 视图：
```
SELECT * FROM user_db_links;
```
查看特定数据库链接的定义：
可以通过查询 DBA_DB_LINKS 或者 ALL_DB_LINKS 视图来查看特定数据库链接的详细信息，例如链接的名字、用户名、连接字符串等信息。
使用数据字典视图查询链接详细信息：
在数据字典视图中，您可以找到有关数据库链接的详细信息。有几个相关的视图可以帮助您查找数据库链接的信息，例如：
- DBA_DB_LINKS: 包含所有数据库链接的信息，需要 DBA 权限。
- ALL_DB_LINKS: 包含当前用户可以访问的所有数据库链接的信息。
- USER_DB_LINKS: 包含当前用户创建的数据库链接的信息。

通过查询这些视图，您可以找到关于数据库链接的定义和详细信息。