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