oracle分页(一文看懂Oracle分页实现方案的三种方式)
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)Physicalrowids:存储ordinarytable,clusteredtable,tablepartitionandsubpartition,indexe,indexpartitionandsubpartition2)Logicalrowids:存储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 DESC
2)取得最大页数: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*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)RKFROMtT)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,是个游标createorreplacepackagetestpackageastypetest_cursorisrefcursor;endtestpackage;--2、开始编写分页的过程createorreplaceprocedurefenye(tableNameinvarchar2,--表名 pageSizeinnumber,--一页显示记录数 pageNowinnumber,--当前页 myrowsoutnumber,--总记录数 myPageCountoutnumber,--总页数 p_cursorouttestpackage.test_cursor--返回的记录集 )is--定义部分 --定义sql语句字符串 v_sqlvarchar2(1000);--定义两个整数 v_beginnumber:=(pageNow-1)*pageSize+1; v_endnumber:=pageNow*pageSize; begin--执行部份 v_sql:='select*from(selectt1.*,rownumrnfrom(select*from'|| tableName||')t1whererownum<='||v_end||')wherern>='|| v_begin;--把游标和sql关联 openp_cursorforv_sql;--计算myrows和myPageCount --组织一个sql v_sql:='selectcount(*)from'||tableName;--执行sql,并把返回的值赋给myrows; executeimmediatev_sql intomyrows;--计算myPageCount ifmod(myrows,PageSize)=0then myPageCount:=myrows/PageSize;else myPageCount:=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_EMPprimarykey(EMPNO)usingindex;INSERTINTOtVALUES('7369','SMITH','CLERK','7902',TO_DATE('1980-12-1700:00:00','SYYYY-MM-DDHH24:MI:SS'),'800',NULL,'20');INSERTINTOtVALUES('7499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-2000:00:00','SYYYY-MM-DDHH24:MI:SS'),'1600','300','30');INSERTINTOtVALUES('7521','WARD','SALESMAN','7698',TO_DATE('1981-02-2200:00:00','SYYYY-MM-DDHH24:MI:SS'),'1250','500','30');INSERTINTOtVALUES('7566','JONES','MANAGER','7839',TO_DATE('1981-04-0200:00:00','SYYYY-MM-DDHH24:MI:SS'),'2975',NULL,'20');INSERTINTOtVALUES('7654','MARTIN','SALESMAN','7698',TO_DATE('1981-09-2800:00:00','SYYYY-MM-DDHH24:MI:SS'),'1250','1400','30');INSERTINTOtVALUES('7698','BLAKE','MANAGER','7839',TO_DATE('1981-05-0100:00:00','SYYYY-MM-DDHH24:MI:SS'),'2850',NULL,'30');INSERTINTOtVALUES('7782','CLARK','MANAGER','7839',TO_DATE('1981-06-0900:00:00','SYYYY-MM-DDHH24:MI:SS'),'2450',NULL,'10');INSERTINTOtVALUES('7788','SCOTT','ANALYST','7566',TO_DATE('1987-04-1900:00:00','SYYYY-MM-DDHH24:MI:SS'),'3000',NULL,'20');INSERTINTOtVALUES('7839','KING','PRESIDENT',NULL,TO_DATE('1981-11-1700:00:00','SYYYY-MM-DDHH24:MI:SS'),'5000',NULL,'10');INSERTINTOtVALUES('7844','TURNER','SALESMAN','7698',TO_DATE('1981-09-0800:00:00','SYYYY-MM-DDHH24:MI:SS'),'1500','0','30');INSERTINTOtVALUES('7876','ADAMS','CLERK','7788',TO_DATE('1987-05-2300:00:00','SYYYY-MM-DDHH24:MI:SS'),'1100',NULL,'20');INSERTINTOtVALUES('7900','JAMES','CLERK','7698',TO_DATE('1981-12-0300:00:00','SYYYY-MM-DDHH24:MI:SS'),'950',NULL,'30');INSERTINTOtVALUES('7902','FORD','ANALYST','7566',TO_DATE('1981-12-0300:00:00','SYYYY-MM-DDHH24:MI:SS'),'3000',NULL,'20');INSERTINTOtVALUES('7934','MILLER','CLERK','7782',TO_DATE('1982-01-2300:00:00','SYYYY-MM-DDHH24: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)RKFROMtT)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方面的内容,感兴趣的朋友可以关注下~
缔造者名字(湘军缔造者罗泽南)缔造者名字(湘军缔造者罗泽南)曾国藩组建湘军时,是以罗泽南所部湘勇为基干,湘军规制,都是由罗泽南协助制定。罗泽南是湘军缔造者,他与曾国藩和胡林翼是亦师亦友的关系,也是赫赫有名的晚清
道可道非常道(道可道,非常道,道尽了天地玄机)道可道非常道(道可道,非常道,道尽了天地玄机)老子的开篇讲的是什么?道可道,非常道。听说过老子的人,恐怕都不会感到陌生,也被许多人所津津乐道。那么,道可道,非常道,到底想要表达的是
五棵松林静小吃(林静小吃一家不起眼的麻辣烫开了小20年了)五棵松林静小吃(林静小吃一家不起眼的麻辣烫开了小20年了)这几年北京街头出现了很多麻辣烫连锁店,好吃不好吃的大家众说纷纭。今天推荐一家我知道的麻辣烫老店林静小吃。店位于五棵松路口西
抽纸团购(社区团购的另一杀手锏正在浮出)抽纸团购(社区团购的另一杀手锏正在浮出)见实大胆预测一下,继供应链团长之后,社区团购将会进入自有品牌维度的竞争。当然供应链和团长的竞争还在持续,实际上据见实了解市场上已经出现区域团
化妆品团购(玩社区团购的化妆品)化妆品团购(玩社区团购的化妆品)社区团购在近两年来特别火,这个最初只用来买菜的购物渠道,如今已经变成多品类的生活馆。在疫情之后,消费者出门次数减少,再加上懒人经济的推动,社区团购的
九转内丹术(中医内核的道教养生法门,九转内丹术!)九转内丹术(中医内核的道教养生法门,九转内丹术!)九转内丹术属于道教气功养生法,由陈朴所创。本书世传有两种一名陈先生内丹诀,一名九转金丹秘诀。可互相参证。内丹之功,起于一而成于九。
范冰冰成龙(范冰冰成龙到底什么关系?)范冰冰成龙(范冰冰成龙到底什么关系?)成龙和范爷的关系是那种说不清道不明剪不断理还乱的关系。大家都知道成龙是老司机,自然是手段高明首先,二人情同兄妹爱,怎样玩就怎么玩。此前接受采访
嘴唇上有白点(为什么唇部有白色颗粒)嘴唇上有白点(为什么唇部有白色颗粒)美人有约,让美触手可及。每到换季之时人的皮肤特别容易干燥,尤其是脸部。在此期间大多人都会尤为注重保养脸部,但却会忽略了唇部的保养,以至于唇部出现
尿不出来怎么办(小便尿不出来肚子胀怎么回事)护士,快来帮帮我,我的肚子涨得不行了,可就是尿不出来!前几天,两江新区第二人民医院外一科一病房里传出求救声。护士们立即来到病床查看情况。原来是当天上午刚做了阑尾炎手术的郭阿姨,术后
紧张性头痛怎么办(如何缓解紧张性头痛?)紧张性头痛怎么办(如何缓解紧张性头痛?)紧张性头疼,又称紧张型头痛或肌肉收缩性头疼,是最常见的头痛类型,占所有头痛类型的78。好发于中青年,终身发病率达到3078。虽然紧张性头痛尚
道法自然(老子道法自然的悖论和缺憾)道法自然(老子道法自然的悖论和缺憾)道法自然却否认人性是自然天性,这是老子理论的悖论,也是缺憾。道德经第二章为说明有无相生,难易相成,长短相形等矛盾的对立面伴生,提到天下皆知美之为