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：存储ordinarytable,clusteredtable,tablepartitionandsubpartition,indexe,index partitionandsubpartition2）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 <= xx3)取得最小页数: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*FROMtable_nameWHEREROWIDIN(SELECTRIDFROM(SELECTROWNUMRN, RIDFROM(SELECTROWIDRID, tablenumberFROMtable_nameORDERBYtablenumberDESC)WHEREROWNUM<= ((currentPage -1) * pageSize + pageSize))WHERERN > ((currentPage -1) * pageSize))ORDERBYtablenumberDESC;

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(SELECTT.*, ROW_NUMBER()OVER(ORDERBYtablenumberDESC) RKFROMt T)WHERERK <= ((currentPage -1) * pageSize + pageSize)ANDRK > ((currentPage -1) * pageSize);

3、根据rownum 来分

rownum是伪列，是在获取查询结果集后再加上去的 （获取一条记录加一个rownum）。对符合条件的结果添加一个从1开始的序列号

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

一般实现分页的过程如下：

--currentPage：当前页数--pageSize：每页显示几条SELECT*FROM(SELECTT.*,ROWNUMRNFROM(SELECT*FROMtORDERBYtablenumberDESC) TWHEREROWNUM<= ((currentPage -1) * pageSize + pageSize))WHERERN > ((currentPage -1) * pageSize);

4、存储过程实现

这个存储过程主要是让大家看看分页的实现过程，可忽略

--1、开发一个包，在该包中，定义类型test_cursor，是个游标createorreplacepackagetestpackage astypetest_cursor is ref cursor;endtestpackage;--2、开始编写分页的过程createorreplace procedure fenye(tableNameinvarchar2,--表名pageSizeinnumber,--一页显示记录数pageNowinnumber,--当前页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关联openp_cursorforv_sql;--计算myrows和myPageCount--组织一个sqlv_sql :=select count(*) from|| tableName;--执行sql，并把返回的值赋给myrows;executeimmediate v_sql into myrows;--计算myPageCountifmod(myrows, PageSize) =0thenmyPageCount := myrows / PageSize;elsemyPageCount := myrows / PageSize +1;endif;--关闭游标closep_cursor;end;

5、实例演示

5.1、环境准备

createtablet(EMPNONUMBER(4)notnull, ENAMEVARCHAR2(10), JOBVARCHAR2(9), MGRNUMBER(4), HIREDATEDATE, SALNUMBER(7,2), COMMNUMBER(7,2), DEPTNONUMBER(2));altertabletaddconstraintPK_EMP primarykey(EMPNO)usingindex;INSERTINTOtVALUES(7369,SMITH,CLERK,7902,TO_DATE(1980-12-17 00:00:00,SYYYY-MM-DD HH24:MI:SS),800,NULL,20);INSERTINTOtVALUES(7499,ALLEN,SALESMAN,7698,TO_DATE(1981-02-20 00:00:00,SYYYY-MM-DD HH24:MI:SS),1600,300,30);INSERTINTOtVALUES(7521,WARD,SALESMAN,7698,TO_DATE(1981-02-22 00:00:00,SYYYY-MM-DD HH24:MI:SS),1250,500,30);INSERTINTOtVALUES(7566,JONES,MANAGER,7839,TO_DATE(1981-04-02 00:00:00,SYYYY-MM-DD HH24:MI:SS),2975,NULL,20);INSERTINTOtVALUES(7654,MARTIN,SALESMAN,7698,TO_DATE(1981-09-28 00:00:00,SYYYY-MM-DD HH24:MI:SS),1250,1400,30);INSERTINTOtVALUES(7698,BLAKE,MANAGER,7839,TO_DATE(1981-05-01 00:00:00,SYYYY-MM-DD HH24:MI:SS),2850,NULL,30);INSERTINTOtVALUES(7782,CLARK,MANAGER,7839,TO_DATE(1981-06-09 00:00:00,SYYYY-MM-DD HH24:MI:SS),2450,NULL,10);INSERTINTOtVALUES(7788,SCOTT,ANALYST,7566,TO_DATE(1987-04-19 00:00:00,SYYYY-MM-DD HH24:MI:SS),3000,NULL,20);INSERTINTOtVALUES(7839,KING,PRESIDENT,NULL,TO_DATE(1981-11-17 00:00:00,SYYYY-MM-DD HH24:MI:SS),5000,NULL,10);INSERTINTOtVALUES(7844,TURNER,SALESMAN,7698,TO_DATE(1981-09-08 00:00:00,SYYYY-MM-DD HH24:MI:SS),1500,0,30);INSERTINTOtVALUES(7876,ADAMS,CLERK,7788,TO_DATE(1987-05-23 00:00:00,SYYYY-MM-DD HH24:MI:SS),1100,NULL,20);INSERTINTOtVALUES(7900,JAMES,CLERK,7698,TO_DATE(1981-12-03 00:00:00,SYYYY-MM-DD HH24:MI:SS),950,NULL,30);INSERTINTOtVALUES(7902,FORD,ANALYST,7566,TO_DATE(1981-12-03 00:00:00,SYYYY-MM-DD HH24:MI:SS),3000,NULL,20);INSERTINTOtVALUES(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*FROMtWHEREROWIDIN(SELECTRIDFROM(SELECTROWNUMRN, RIDFROM(SELECTROWIDRID, EMPNOFROMtORDERBYEMPNODESC)WHEREROWNUM<= ( (1-1) *5+5))--每页显示几条WHERERN > ((1-1) *5) )--当前页数ORDERBYEMPNODESC;--查询当前第二页，并显示6行数据（currentPage=2，pagesize=6）SELECT*FROMtWHEREROWIDIN(SELECTRIDFROM(SELECTROWNUMRN, RIDFROM(SELECTROWIDRID, EMPNOFROMtORDERBYEMPNODESC)WHEREROWNUM<= ( (2-1) *6+6))--每页显示几条WHERERN > ((2-1) *6) )--当前页数ORDERBYEMPNODESC;

5.3、根据分页函数查询

--查询当前第一页，并显示6行数据（currentPage=1，pagesize=6）SELECT*FROM(SELECTT.*, ROW_NUMBER()OVER(ORDERBYtablenumberDESC) RKFROMt T)WHERERK <= ((1-1) *5+5)--每页显示几条ANDRK > ((1-1) *5);--当前页数

5.4、根据分页函数查询

--查询当前第二页，并显示4行数据（currentPage=2，pagesize=4）SELECT*FROM(SELECTT.*,ROWNUMRNFROM(SELECT*FROMtORDERBYempnoDESC) TWHEREROWNUM<= ((2-1) *4+4))WHERERN > ((2-1) *4);

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~