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

select from (select t rownum row_id form (select from 表名) t ) where row_id>=1 and row_id<=10

这条SQL语句就可以实现分页查询，当然光有SQL还是不行，用以下的PHP 函数就可实现分页了。

/分页函数/

function page($page,$total,$phpfile,$pagesize=10,$pagelen=10,$str='')

{

$pagecode = '';//定义变量，存放分页生成的HTML

$page = intval($page);//避免非数字页码

$total = intval($total);//保证总记录数值类型正确

if(!$total) return array();//总记录数为零返回空数组

$pages = ceil($total/$pagesize);//计算总分页

//处理页码合法性

if($page<1) $page = 1;

if($page>$pages) $page = $pages;

//计算查询偏移量

$offset = $pagesize($page-1)+1;

//页码范围计算

$init = 1;//起始页码数

$max = $pages;//结束页码数

$pagelen = ($pagelen%2)$pagelen:$pagelen+1;//页码个数

$pageoffset = ($pagelen-1)/2;//页码个数左右偏移量

//生成html

$pagecode='<div class="page">';

$pagecode="<span>共"$total"条信息 第"$page"页/共"$pages"页</span>\n";//第几页,共几页

//如果是第一页，则不显示第一页和上一页的连接

if($page!=1){

$pagecode="<a href=\"{$phpfile}page=1&$str\"> 首页 </a> ";//第一页

$pagecode="<a href=\"{$phpfile}page="($page-1)"&$str\"> 上页 </a> ";//上一页

}

//分页数大于页码个数时可以偏移

if($pages>$pagelen)

{

//如果当前页小于等于左偏移

if($page<=$pageoffset)

{

$init=1;

$max = $pagelen;

}else

{

//如果当前页大于左偏移

//如果当前页码右偏移超出最大分页数

if($page+$pageoffset>=$pages+1)

{

$init = $pages-$pagelen+1;

}else{

//左右偏移都存在时的计算

$init = $page-$pageoffset;

$max = $page+$pageoffset;

}

}

}

//生成html

for($i=$init;$i<=$max;$i++)

{

if($i==$page)

{

$pagecode='<span><font color = red><b>'$i'</b></font></span>';

} else {

$pagecode="<a href=\"{$phpfile}page={$i}&$str\"> $i </a>";

}

}

if($page!=$pages)

{

$pagecode="<a href=\"{$phpfile}page="($page+1)"&$str\"> 下页 </a>";//下一页

$pagecode="<a href=\"{$phpfile}page={$pages}&$str\"> 末页</a>";//最后一页

}

$pagecode='</div>';

$onset = $offset + $pagesize -1;

return array('pagecode'=>$pagecode,'sqllimit'=>' and row_id between '$offset' and '$onset);

}

使用方法如下：

1，首先查询最大数据条数

$sql="select count() co_unt from 表名";

$st = OCIParse($conn, $sql);//分析指令

OCIExecute($st); //执行指令

$row=oci_fetch_array($st);

$counts=$row['CO_UNT'];

if($counts>0){

$page=isset($_GET['page'])$_GET['page']:1;

$getpageinfo = page($page,$counts,$phpfile,$chahaoString,10,15);

$pagesql=$getpageinfo['sqllimit'];//组合完整的SQL语句

$pagenav=$getpageinfo['pagecode'];//分页显示的样式代码

$sql="select from (select t rownum row_id form (select from 表名) t ) where 1=1 "$pagesql

}

下面是用ORACLE数据库pl/sql编程实现的一个方式：

-------------------创建一个包--------------------------

create or replace package pages_query_pak as

type pages_cursor is ref cursor; --定一个游标，保存数据查询得到的结果集

end pages_query_pak;

------------------创建一个过程----------------------

create or replace procedure pages_pro(

tableName in varchar2,

pageSize in number,--每一页显示的记录数

pageNow in number,--显示第几页

myRows out number,--总记录数

pageCount out number,--总页数

page_cursor out pages_query_pakpages_cursor--返回的记录集 这里有用了上面那个包

) is

v_sql varchar2(1000);

v_begin number:=(pageNow-1)pageSize+1;

v_end number:=pageNowpageSize;

begin

v_sql:='select from (select bt,rownum rnum from (select from '|| tableName ||')bt

where rownum<='||v_end||') where rnum>='||v_begin;

open page_cursor for v_sql;

--计算myRows和pageCount

v_sql:='select count() from '||tableName;

execute immediate v_sql into myRows;

if mod(myRows,pageSize)=0 then

pageCount:=myRows/pageSize;

else

pageCount:=myRows/pageSize+1;

end if;

end;

以上就是关于Oracle常见SQL分页实现方案全部的内容，包括:Oracle常见SQL分页实现方案、如何用easyUI做Oracle的分页、实现oracle分页的sql语句等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！