Oracle常见SQL分页实现方案

在Oracle中 用SQL来实现分页有很多种实现方式 但有些语句可能并不是很通用 只能用在一些特殊场景之中

以下介绍三种比较通用的实现方案 在以下各种实现中 ROWNUM是一个最核心的关键词 在查询时他是一个虚拟的列 取值为 到记录总数的序号

首先来介绍我们工作中最常使用的一种实现方式

SELECT

FROM (SELECT ROW_ ROWNUM ROWNUM_

FROM (SELECT

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) ROW_

WHERE ROWNUM <= )

WHERE ROWNUM_ >= ;

其中最内层的查询SELECT为不进行翻页的原始查询语句 可以用自己的任意Select SQL替换 ROWNUM <= 和ROWNUM >= 控制分页查询的每页的范围

分页的目的就是控制输出结果集大小 将结果尽快的返回 上面的SQL语句在大多数情况拥有较高的效率 主要体现在WHERE ROWNUM <= 这句上 这样就控制了查询过程中的最大记录数

上面例子中展示的在查询的第二层通过ROWNUM <= 来控制最大值 在查询的最外层控制最小值 而另一种方式是去掉查询第二层的WHERE ROWNUM <= 语句 在查询的最外层控制分页的最小值和最大值 此时SQL语句如下 也就是要介绍的第二种实现方式

SELECT

FROM (SELECT A ROWNUM RN

FROM (SELECT

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) A)

WHERE RN BEEEN AND ;

由于Oracle可以将外层的查询条件推到内层查询中 以提高内层查询的执行效率 但不能跨越多层

对于第一个查询语句 第二层的查询条件WHERE ROWNUM <= 就可以被Oracle推入到内层查询中 这样Oracle查询的结果一旦超过了ROWNUM限制条件 就终止查询将结果返回了

而 第二个查询语句 由于查询条件BEEEN AND 是存在于查询的第三层 而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义 因为最内层查询不知道RN代表什么） 因此 对于第二个查询语句 Oracle最内层返回给中间层的是所有满足条件的数据 而中间层返回给最外层的也是所有数据 数据的过滤在最外层完成 显然这个效率要比第一个查询低得多

以上两种方案完全是通过ROWNUM来完成 下面一种则采用ROWID和ROWNUM相结合的方式 SQL语句如下

SELECT

FROM (SELECT RID

FROM (SELECT R RID ROWNUM LINENUM

FROM (SELECT ROWID RID

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) R

WHERE ROWNUM <= )

WHERE LINENUM >= ) T

TABLE T

WHERE T RID = T ROWID;

从语句上看 共有 层Select嵌套查询 最内层为可替换的不分页原始SQL语句 但是他查询的字段只有ROWID 而没有任何待查询的实际表字段 具体查询实际字段值是在最外层实现的

这种方式的原理大致为 首先通过ROWNUM查询到分页之后的 条实际返回记录的ROWID 最后通过ROWID将最终返回字段值查询出来并返回

和前面两种实现方式相比 该SQL的实现方式更加繁琐 通用性也不是非常好 因为要将原始的查询语句分成两部分（查询字段在最外层 表及其查询条件在最内层）

但这种实现在特定场景下还是有优势的 比如我们经常要翻页到很后面 比如 条记录中我们经常需要查 及其以后的数据 此时该方案效率可能要比前面的高

因为前面的方案中是通过ROWNUM <= 来控制的 这样就需要查询出 条数据 然后取最后 之间的数据 而这个方案直接通过ROWID取需要的那 条数据

从不断向后翻页这个角度来看 第一种实现方案的成本会越来越高 基本上是线性增长 而第三种方案的成本则不会像前者那样快速 他的增长只体现在通过查询条件读取ROWID的部分

当然 除了以上提了这些方案 我们还可以用以下的SQL来实现

SELECT

FROM TABLE

WHERE TABLE _ID NOT IN

(SELECT TABLE _ID FROM TABLE WHERE ROWNUM <= )

AND ROWNUM <= ;

SELECT

FROM TABLE

WHERE ROWNUM <=

MINUS

SELECT FROM TABLE WHERE ROWNUM <= ;

………………

注意 当ROWNUM作为查询条件时 他是在order by之前执行 所以要特别小心

比如我们想查询TABLE 中按TABLE _ID倒序排列的前 条记录不能用如下的SQL来完成

lishixinzhi/Article/program/Oracle/201311/11198

Oracle内分页：

1首先创建返回结果集的包，代码如下:

--创建包

create or replace package types as

type cursorType is ref cursor;

end;12341234

2创建实现分页查询的存储过程：

--创建存储过程

CREATE OR REPLACE PROCEDURE PROC_GET_DATA_PAGING(P_TABLENAME IN VARCHAR2, --表（视图）名

P_STRWHERE IN VARCHAR2, --查询条件

P_ORDERCOLUMN IN VARCHAR2, --排序的列

P_CURPAGE IN OUT NUMBER, --当前页

P_PAGESIZE IN OUT NUMBER, --每页显示记录条数

P_TOTALRECORDS OUT NUMBER, --总记录数

P_TOTALPAGES OUT NUMBER, --总页数

V_CUR OUT TYPESCURSORTYPE) --返回的结果集

IS

V_SQL VARCHAR2(4000) := ''; --SQL语句

V_STARTRECORD NUMBER(10); --开始显示的记录条数

V_ENDRECORD NUMBER(10); --结束显示的记录条数

V_SHOWALL INTEGER; --是否显示全部记录

BEGIN

--记录中总记录条数

V_SQL := 'SELECT TO_NUMBER(COUNT()) FROM ' || P_TABLENAME ||

' WHERE 1=1 ';

IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN

V_SQL := V_SQL || P_STRWHERE;

END IF;

EXECUTE IMMEDIATE V_SQL

INTO P_TOTALRECORDS;

--验证页面记录大小

IF P_PAGESIZE <= 0 THEN

V_SHOWALL := 1;

P_PAGESIZE := 0;

END IF;

IF V_SHOWALL IS NULL THEN

--根据页大小计算总页数

IF MOD(P_TOTALRECORDS, P_PAGESIZE) = 0 THEN

P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0);

ELSE

P_TOTALPAGES := TRUNC(P_TOTALRECORDS / P_PAGESIZE, 0) + 1;

END IF;

ELSE

P_TOTALPAGES := 1;

END IF;

--验证页号

IF P_CURPAGE < 1 THEN

P_CURPAGE := 1;

END IF;

IF P_CURPAGE > P_TOTALPAGES THEN

P_CURPAGE := P_TOTALPAGES;

END IF;

--实现分页查询

V_STARTRECORD := (P_CURPAGE - 1) P_PAGESIZE + 1;

V_ENDRECORD := P_CURPAGE P_PAGESIZE;

V_SQL := 'SELECT FROM (SELECT A, ROWNUM R FROM ' ||

'(SELECT FROM ' || P_TABLENAME;

IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN

V_SQL := V_SQL || ' WHERE 1=1 ' || P_STRWHERE;

END IF;

IF P_ORDERCOLUMN IS NOT NULL OR P_ORDERCOLUMN <> '' THEN

V_SQL := V_SQL || ' ORDER BY ' || P_ORDERCOLUMN;

END IF;

IF V_SHOWALL IS NULL THEN

V_SQL := V_SQL || ') A WHERE ROWNUM <= ' || V_ENDRECORD ||

') B WHERE R >= ' || V_STARTRECORD;

ELSE

V_SQL := V_SQL || ') A ) B ';

END IF;

DBMS_OUTPUTPUT_LINE(V_SQL);

OPEN V_CUR FOR V_SQL;

END PROC_GET_DATA_PAGING;123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172

一定要先创建返回结果集的包，否则编译存储过程有问题。

以上就是关于Oracle常见SQL分页实现方案全部的内容，包括:Oracle常见SQL分页实现方案、oracle用游标分页查询学生所有信息、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！