oracle 分页查询语句(一文看懂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;

oracle 分页查询语句(一文看懂Oracle分页实现方案的三种方式)(1)

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;

oracle 分页查询语句(一文看懂Oracle分页实现方案的三种方式)(2)

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); --当前页数

oracle 分页查询语句(一文看懂Oracle分页实现方案的三种方式)(3)

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);

oracle 分页查询语句(一文看懂Oracle分页实现方案的三种方式)(4)


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

oracle 分页查询语句(一文看懂Oracle分页实现方案的三种方式)(5)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页