Oracle 分页方法研究

• 1、Oracle 中的三大分页方法
  • 1.1、通过分析函数分页
  • 1.2、通过 ROWNUM 分页
  • 1.3、通过 ROWID 分页
• 2、Oracle 分页解决方案浅析
  • 2.1、纯后端代码完成分页
  • 2.2、通过存储过程来分页
  • 2.3、两个通用的分页存储过程
• 3、总结

1、Oracle 中的三大分页方法

本人最近总结了一下 Oracle 中的分页写法，从纯粹的 SQL 写法上来看，所谓分页就是嵌套子查询，无非就是不同的分页方法嵌套的子查询层数不同而已。Oracle 中一共有三种分页写法，分别是：嵌套一层子查询的分析函数分页、嵌套两层子查询的 ROWNUM 分页和嵌套三层子查询的 ROWID 分页。

1.1、通过分析函数分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。只嵌套一层子查询，写法简洁，容易理解，但一般没人用这种方法。只需要在子查询中的分析函数内部排序即可实现排序功能。

SELECT t2.staff_name,t2.birthday FROM(
  SELECT t1.staff_name,t1.birthday,ROW_NUMBER() OVER(ORDER BY t1.birthday) rn
  FROM demo.t_staff t1
) t2 WHERE t2.rn >= ((1-1)*3+1) AND t2.rn <= (1*3);

1.2、通过 ROWNUM 分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。嵌套两层子查询，写法比较灵活，一般都是用这种方法。只需要在子查询内部排序即可实现排序功能。

SELECT t3.staff_name,t3.birthday FROM(
  SELECT t2.*,ROWNUM rn FROM(
    SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2 WHERE ROWNUM <= (1*3)
) t3 WHERE t3.rn >= ((1-1)*3+1);

通过 ROWNUM 分页的一种变通写法（相对来说更好理解）：

SELECT t3.staff_name,t3.birthday FROM(
  SELECT t2.*,ROWNUM rn FROM(
    SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2
) t3 WHERE t3.rn >= ((1-1)*3+1) AND t3.rn <= (1*3);

1.3、通过 ROWID 分页

按员工年龄排序，每页显示 3 个员工，取第 1 页的数据。写法复杂，不太灵活，不易理解，很少有人用这种方法。必须在最内层子查询和最外层查询中都排序才可实现排序功能。

SELECT t4.staff_name,t4.birthday
FROM demo.t_staff t4
WHERE t4.ROWID IN(
  SELECT t3.rid FROM(
    SELECT t2.rid,ROWNUM rn FROM(
      SELECT t1.ROWID rid FROM demo.t_staff t1 ORDER BY t1.birthday
    ) t2 WHERE ROWNUM <= (1*3)
  ) t3 WHERE t3.rn >= ((1-1)*3+1)
) ORDER BY t4.birthday;

2、Oracle 分页解决方案浅析

Oracle 中的三大分页方法应用最广泛的还是第二种，也就是基于 ROWNUM 的分页方法。由于实现分页的语法是固定的，所以一般项目中都是会提供一个公用的分页模版方法，然后其它需要分页的业务方法再调用这个方法来完成分页功能的。

分页的实现过程就是拼接 SQL 语句的过程，但选择在那个地方来完成拼接也是有讲究的。一般来说在服务端拼接是一个比较好的选择，这种方案主要好处就是灵活、简单、易维护。另一种比较常见的做法是通过存储过程来分页，然后在服务端调用存储过程，这种方案理论上分页效率比较高，但实现过程相对复杂ÿ