oracle 分页语句（四类数据库分页实现方案总结之Oracle分页实现）
闲来无事，整理下Oracle、mysql、mssql以及PG数据库的分页实现方式，大家可以简单做个对比，看下不同数据库在分页这块是怎么实现的。今天先介绍一下Oracle分页的实现方式。
oracle的分页一共有三种方式，但在Oracle中实现分页的方法主要是用ROWNUM关键字和用ROWID关键字两种。Rownum 和 Rowid是Oracle数据库所特有的，通过他们可以查询到指定行数范围内的数据记录。
1、根据rowid来分
Oracle使用rowid数据类型存储行地址,rowid是物理存在的，实际存在的一个列，是一种数据类型。 基于64为编码的18个字符来唯一标识的一条记录的物理位置的一个ID。而唯一标识出对应的存储的物理位置, 类似hashcode值。
rowid可以分成两种，分别适于不同的对象：
1）Physical rowids：存储ordinary table,clustered table,table partition and subpartition,indexe,index partition and subpartition
2）Logical rowids : 存储IOT的行地址
另一种rowid类型叫universal rowed(UROWID),支持上述physical rowid和logical rowed,并且支持非oracle table, 即支持所有类型的rowid, 但COMPATIBLE必须在8.1或以上.
每个表在oracle内部都有一个ROWID伪列,它在所有sql中无法显示，不占存储空间; 它用于从表中查询行的地址或者在where中进行参照,rowid伪列不存储在数据库中,它不是数据库数据,这是从database及table的逻辑结构来说的,事实上,在物理结构上,每行由一个或多个row pieces组成,每个row piece的头部包含了这个piece的address,即rowid.从这个意义上来说，rowid还是占了磁盘空间的。
我们在创建表时，可以为列指定为rowid数据类型，但oracle并不保证列中的数据是合法的rowid值,必须由应用程序来保证, 另外,类型为rowid的列需要6 bytes存储数据
一般实现分页的过程如下：
- 1)获取数据物理地址:SELECT ROWID RID, tablenumber FROM table_name ORDER BY tablenumber DESC2)取得最大页数:SELECT ROWNUM RN, RID FROM (SELECT ROWID RID, tablenumber FROM table_name ORDER BY tablenumber DESC) WHERE ROWNUM <= xx
- 3)取得最小页数:SELECT RID FROM(SELECT ROWNUM RN, RID FROM (SELECT ROWID RID, tablenumber FROM table_name ORDER BY tablenumber DESC) WHERE ROWNUM <= xx)
- 4)因为取得的页数都是物理地址，再根据物理地址，查询出具体数据
--currentPage：当前页数
--pageSize：每页显示几条
SELECT *
FROM table_name
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, tablenumber
FROM table_name
ORDER BY tablenumber DESC)
WHERE ROWNUM <= ((currentPage - 1) * pageSize pageSize))
WHERE RN > ((currentPage - 1) * pageSize))
ORDER BY tablenumber DESC;
2、按分析函数 ROW_NUMBER() OVER()来分
语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
oracle中的ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）
一般实现分页的过程如下：
--currentPage：当前页数
--pageSize：每页显示几条
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY tablenumber DESC) RK FROM t T)
WHERE RK <= ((currentPage - 1) * pageSize pageSize)
AND RK > ((currentPage - 1) * pageSize);
3、根据rownum 来分
rownum是伪列，是在获取查询结果集后再加上去的 （获取一条记录加一个rownum）。对符合条件的结果添加一个从1开始的序列号
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
一般实现分页的过程如下：
--currentPage：当前页数
--pageSize：每页显示几条
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT * FROM t ORDER BY tablenumber DESC) T
WHERE ROWNUM <= ((currentPage - 1) * pageSize pageSize))
WHERE RN > ((currentPage - 1) * pageSize);
4、存储过程实现
这个存储过程主要是让大家看看分页的实现过程，可忽略
--1、开发一个包，在该包中，定义类型test_cursor，是个游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--2、开始编写分页的过程
create or replace procedure fenye(tableName in varchar2, --表名
pageSize in number, --一页显示记录数
pageNow in number, --当前页
myrows out number, --总记录数
myPageCount out number, --总页数
p_cursor out testpackage.test_cursor --返回的记录集
) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number := (pageNow - 1) * pageSize 1;
v_end number := pageNow * pageSize;
begin
--执行部份
v_sql := select * from (select t1.*,rownum rn from (select * from ||
tableName || ) t1 where rownum<= || v_end || ) where rn>= ||
v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql
v_sql := select count(*) from || tableName;
--执行sql，并把返回的值赋给myrows;
execute immediate v_sql
into myrows;
--计算myPageCount
if mod(myrows, PageSize) = 0 then
myPageCount := myrows / PageSize;
else
myPageCount := myrows / PageSize 1;
end if;
--关闭游标
close p_cursor;
end;
5、实例演示
5.1、环境准备
create table t(EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
alter table t add constraint PK_EMP primary key (EMPNO) using index;
INSERT INTO t VALUES (7369, SMITH, CLERK, 7902, TO_DATE(1980-12-17 00:00:00, SYYYY-MM-DD HH24:MI:SS), 800, NULL, 20);
INSERT INTO t VALUES (7499, ALLEN, SALESMAN, 7698, TO_DATE(1981-02-20 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1600, 300, 30);
INSERT INTO t VALUES (7521, WARD, SALESMAN, 7698, TO_DATE(1981-02-22 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1250, 500, 30);
INSERT INTO t VALUES (7566, JONES, MANAGER, 7839, TO_DATE(1981-04-02 00:00:00, SYYYY-MM-DD HH24:MI:SS), 2975, NULL, 20);
INSERT INTO t VALUES (7654, MARTIN, SALESMAN, 7698, TO_DATE(1981-09-28 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1250, 1400, 30);
INSERT INTO t VALUES (7698, BLAKE, MANAGER, 7839, TO_DATE(1981-05-01 00:00:00, SYYYY-MM-DD HH24:MI:SS), 2850, NULL, 30);
INSERT INTO t VALUES (7782, CLARK, MANAGER, 7839, TO_DATE(1981-06-09 00:00:00, SYYYY-MM-DD HH24:MI:SS), 2450, NULL, 10);
INSERT INTO t VALUES (7788, SCOTT, ANALYST, 7566, TO_DATE(1987-04-19 00:00:00, SYYYY-MM-DD HH24:MI:SS), 3000, NULL, 20);
INSERT INTO t VALUES (7839, KING, PRESIDENT, NULL, TO_DATE(1981-11-17 00:00:00, SYYYY-MM-DD HH24:MI:SS), 5000, NULL, 10);
INSERT INTO t VALUES (7844, TURNER, SALESMAN, 7698, TO_DATE(1981-09-08 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1500, 0, 30);
INSERT INTO t VALUES (7876, ADAMS, CLERK, 7788, TO_DATE(1987-05-23 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1100, NULL, 20);
INSERT INTO t VALUES (7900, JAMES, CLERK, 7698, TO_DATE(1981-12-03 00:00:00, SYYYY-MM-DD HH24:MI:SS), 950, NULL, 30);
INSERT INTO t VALUES (7902, FORD, ANALYST, 7566, TO_DATE(1981-12-03 00:00:00, SYYYY-MM-DD HH24:MI:SS), 3000, NULL, 20);
INSERT INTO t VALUES (7934, MILLER, CLERK, 7782, TO_DATE(1982-01-23 00:00:00, SYYYY-MM-DD HH24:MI:SS), 1300, NULL, 10);
commit;
5.2、根据rowid查询
--查询当前第一页，并显示5行数据（currentPage=1，pagesize=5）
SELECT *
FROM t
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, EMPNO FROM t ORDER BY EMPNO DESC)
WHERE ROWNUM <= ( (1-1) * 5 5 )) --每页显示几条
WHERE RN > ((1-1) * 5) ) --当前页数
ORDER BY EMPNO DESC;
--查询当前第二页，并显示6行数据（currentPage=2，pagesize=6）
SELECT *
FROM t
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, EMPNO FROM t ORDER BY EMPNO DESC)
WHERE ROWNUM <= ( (2-1) * 6 6)) --每页显示几条
WHERE RN > ((2-1) * 6) ) --当前页数
ORDER BY EMPNO DESC;
5.3、根据分页函数查询
--查询当前第一页，并显示6行数据（currentPage=1，pagesize=6）
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY tablenumber DESC) RK FROM t T)
WHERE RK <= ((1 - 1) * 5 5) --每页显示几条
AND RK > ((1 - 1) * 5); --当前页数
5.4、根据分页函数查询
--查询当前第二页，并显示4行数据（currentPage=2，pagesize=4）
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT * FROM t ORDER BY empno DESC) T
WHERE ROWNUM <= ((2 - 1) * 4 4))
WHERE RN > ((2 - 1) * 4);
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
免责声明：本文仅代表文章作者的个人观点，与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺，请读者仅作参考，并自行核实相关内容。
