使用 PHP 和 Oracle 实施分页结果集

学习如何实施分页结果集并高效显示大型结果集。

本文相关下载： 
 Oracle 数据库 10g 
 Zend Core for Oracle 
 Apache HTTP Server 1.3 和更高版本

2005 年 11 月发表

分页结果集是用于高效显示查询结果集的常用技术。最著名的例子或许就是 Google 搜索结果页面底部的“结果页码”。

在本方法文档中，您将学习如何使用 PHP 和 Oracle 实施分页结果集，以及用于显示大型数据集的高效技术。

分页结果集的必要性

基于 Web 的应用程序不像桌面 GUI 那样，当用户执行向上滚动或向下滚动操作时可以使结果游标在会话中保持打开状态，它们受到 HTTP 协议的无状态请求/响应生命周期的限制，也就是说，请求完成后，会话随即终止。此外，如果一次在 HTML 表中发送数千行信息，最终用户将明显受到影响 - 底层基础架构就更不用说了。

解决此问题的一个切实可行的方法就是为用户提供“页面”这一抽象概念，它为用户提供结果集中的相对位置。可以通过 URL 传递页码，从而保持 HTTP 的无状态性 - 这是 Rasmus Lerdorf 在他的文章 “Do You PHP?”中所描述的不共享体系结构的重要部分。在服务器端，请求的页码映射为结果集中行的相应子序列，然后发布给用户。用于实施分页结果集的 Sample Data As 示例数据。在本方法文档中，您将使用 Feedster Top 500 Blog 数据，http://top500.feedster.com/top500.tab 提供了此数据,数据的格式易于分析。将该数据存储在下表中：

CREATE TABLE feedster_top_blogs (rank NUMBER PRIMARY KEY,name VARCHAR2(200),url VARCHAR2(200),links NUMBER);

实施分页结果集将涉及应用程序的所有“层”。首先创建 SELECT 查询，该查询返回一个只包含给定页面的行的结果游标，从而最大限度地减少了 Web 与数据库服务器之间的通信量。您还需要在用户界面中提供“pager”来浏览结果集。最后，您需要编写一些将页码转换为结果集中的相应行号的逻辑。

用户界面

假设您要允许用户使用“页码”标识他们要查看的结果集部分。他们请求的页码将通过 URL（如 http://example.com/results.php?page=5）传递。该方法体现了无状态性 - 任何人只需通过输入正确的 URL 便可以将他们的浏览器指向“第 5 页”。当然，如果底层数据随时间而改变，那么针对任何给定页面显示的结果也将有所不同（请参见下面的读取一致性部分）。

同时，为帮助用户浏览结果集，您需要在显示中提供一个“pager”元素，用于提供到其他页面的链接。假设您要支持任意数量的页面（可能有很多页面），pager 元素需要将自身限制为一定数量的页面的“滑动窗口”，显示在在数据集中当前页面的两侧。这可以避免因显示过多的信息而为用户带来负担，并可以更加简便地在浏览器中控制布局。

在本示例中，您将在活动页面的两侧最多显示五个页面。除了“滑动窗口”以外，还将提供指向数据集第一页和最后一页的链接以及上一页和下一页的链接。

以下代码使用一个 PHP 函数来绘制 pager“组件”，可将任意 URL 传递给该函数，该函数将根据该 URL 输出包含链接（指向结果集中的页面）的 HTML：

<?php function draw_pager($url, $total_pages, $current_page = 1) {    if ( $current_page <= 0 || $current_page > $total_pages ) {$current_page = 1;    }    if ( $current_page > 1 ) {printf( "<a href='$url?page=%d'>[Start]</a> \n" , 1);printf( "<a href='$url?page=%d'>[Prev]</a> \n" , ($current_page-1));    }    for( $i = ($current_page-5); $i <= $current_page+5; $i++ ) {        if ($i < 1) continue;if ( $i > $total_pages ) break;        if ( $i != $current_page ) {printf( "<a href='$url?page=%1\$d'>%1\$d</a> \n" , $i);} else {printf("<a href='$url?page=%1\$d'><strong>%1\$d</strong></a> \n",$i);        }            }    if ( $current_page < $total_pages ) {printf( "<a href='$url?page=%d'>[Next]</a> \n" , ($current_page+1));printf( "<a href='$url?page=%d'>[End]</a> \n" , $total_pages);    }    }?>

如果在数据集总共包含 50 个页面并且当前页面为 15 的情况下调用此函数：

draw_pager('http://example.com/results.php',50,15);

它将绘制以下 HTML：

<a href='http://example.com/results.php?page=1'>[Start]</a> <a href='http://example.com/results.php?page=14'>[Prev]</a> <a href='http://example.com/results.php?page=10'>10</a> <a href='http://example.com/results.php?page=11'>11</a> <a href='http://example.com/results.php?page=12'>12</a> <a href='http://example.com/results.php?page=13'>13</a> <a href='http://example.com/results.php?page=14'>14</a> <a href='http://example.com/results.php?page=15'><strong>15</strong></a> <a href='http://example.com/results.php?page=16'>16</a> <a href='http://example.com/results.php?page=17'>17</a> <a href='http://example.com/results.php?page=18'>18</a> <a href='http://example.com/results.php?page=19'>19</a> <a href='http://example.com/results.php?page=20'>20</a> <a href='http://example.com/results.php?page=16'>[Next]</a> <a href='http://example.com/results.php?page=50'>[End]</a>

此 HTML 的作用将类似于 Amazon 的 A9 搜索页面。相比之下，Google 的实施通过 URL 传递起始行号（而非抽象页码），而“pager”采用另一种方式，即显示链接 1-10、11-20（依此类推），而非一组根据当前页面变化的页面。

以上的 draw_pager() 函数存在一个问题，即它无法巧妙地修改传递的 URL。如果要为它传递一个如下所示的基础 URL：

http://example.com/results.php?orderby=name

它将生成如下所示的 URL：

http://example.com/results.php?orderby=name?page=5

相反，它将生成如下所示的 URL（如果已经使用了一个现有的 GET 查询参数）：

http://example.com/results.php?orderby=name&page=5

要解决此问题，您会发现 PHP 的 parse_url () 函数很有用。但切勿将 &page 附加到一个已经具有该参数并在将所有输入参数以 HTML 的形式输出之前对其进行验证或清除的 URL；否则，应用程序将受到 XSS 漏洞的威胁。

页面到行计算

在注意以上 pager 的同时，您现在需要进行两个计算：确定页面总数并将页码转换为行号。为此，您需要确定一个值来表示将在单个页面上显示的行数。（在更复杂的实施中，也可以允许用户更改该值。）

在决定每页的行数后，以下 PHP 函数 total_pages() 输出总页数：

function total_pages($total_rows, $rows_per_page) {if ( $total_rows < 1 ) $total_rows = 1;return ceil($total_rows/$rows_per_page);}

总行数将为要分页的结果集中所有行的计数。对于本文的示例数据，您知道总行数正好为 500 行，但在行数已经更改的表中，您将需要查询数据库以获取该值。（请参见读取一致性部分。）您可能要向 total_pages() 中添加一个设置，即对要显示的最大页数设置限制。（请参见最大页数部分。）

要将给定的页码转换为结果集中的行号，可以使用以下函数：

function page_to_row($current_page, $rows_per_page) {$start_row = ($current_page-1) * $rows_per_page + 1;return $start_row;}

它返回与 $current 页面相对应的结果集中的第一个行号。

读取一致性

根据前面介绍的 HTTP 和 Web 的无状态本质，此处假设数据集的分页显示是无状态的。也就是说，如果当用户正在浏览数据集时插入或删除了行或所做的更新更改了行的相对顺序，那么将出现不一致性，如行存在于多个页面中、数据已经在 HTTP 请求之间更改以及总页面数出现变化。

对于极少更改的数据集，这可能算不上是一个严重问题，因为用户很少注意到这样的不一致性。但对于易失的数据以及用户必须收到一致视图的情况而言，可以考虑使用定期的数据集“快照”（或许是通过 URL 传递其他“快照时间”。该方法使您可以保持无状态性，但在保存的数据数量和保存时间方面存在问题。

一个可选解决方案是使用用户会话存储数据集的静态副本，从而使数据集具有状态性。利用该方法，您可以为每个用户提供一致的视图，而不会使实施细节（如数据集的存在时间）为他们带来麻烦。同时，使用会话很容易产生存储问题，这是因为每个用户都在其会话存在期间获取各自的数据集副本 - 在用户数量很多的情况下，将出现问题。一个可以最大限度地降低存储要求的优化方法是假设大多数用户将只关注于数据集的前几个页面，因此只需要保留此数据。

将数据实际存储到什么位置将取决于特定的要求和环境，在某些情况下最简单的方法是序列化 PHP 数据结构或利用服务数据对象扩展，而在其他情况下则可以考虑使用多个表来保存快照。

通常情况下，应避免尝试采用有状态的方式实施数据集（除非绝对有必要这样做），这是因为这样做将增大应用程序逻辑的复杂性，使管理数据变得更困难并增大服务器在负载很高的情况下出现故障的风险。

行计数

在给定以上两个函数的情况下，您需要一个 SELECT 语句来确定整个结果集中的总行数，并需要另一个语句只返回当前页面的这些行。

要计算给定的 SELECT 语句将选择的行数，可以将该语句嵌套到其他对行进行计数的语句，如以下 PHP 函数所示：

function count_rows(& $conn, $select) {$sql = "SELECT COUNT(*) AS num_rows FROM($select)";$stmt = oci_parse($conn,$sql);oci_define_by_name($stmt,"NUM_ROWS",$num_rows);oci_execute($stmt);oci_fetch($stmt);return $num_rows;}

但使用 COUNT 可能并不是一个有效的解决方案，这是因为仅当您迭带到最后一行时才能了解结果游标返回的总行数。换言之，该可选方法就是使用 SELECT 选择并遍历整个结果集，这样做的效率将低很多。

请注意 John Lim 在 “优化 PHP 和 Oracle”中介绍的有关使用单独表通过 INSERT 和 DELETE 触发器跟踪总行数的提示。如果您知道数据不经常更改，则请使用另一个解决方案，即在短期内将结果缓存到 Web 服务器上。

count_rows() 的限制之一是它假设传递的 SELECT 语句不需要绑定任何参数。如果您需要绑定参数（尤其是那些将更改总行数的参数），则可以考虑对其进行扩展，方法是接受参数名称的散列作为 count_rows()（您将在调用 oci_bind_by_name 时对其进行遍历）的第三个参数。

您可能还需要让该函数限制返回给最大值的值以禁止分页到大结果集的结尾。（请参见下面的最大页数部分。）

注意，在某些设计中，如果您知道将要处理大量数据，则可以考虑忽略行计数逻辑。注意，您可以对可用的最大页数设置限制（请再次参见下面的最大页数），可以让上面的 count_rows() 为已知大于 50 页的数据集返回任意大的数字，同时为用户提供一个类似“Your search returned more than 50 pages of data”的消息。

获取数据集页面

要实际上使用 SELECT 选择特殊页面所需的行，可以使用类似的“SQL 包装”函数，例如：

function & paged_result(& $conn, $select, $start_row, $rows_per_page) {$sql = "SELECT            *FROM             (SELECTr.*, ROWNUM as row_number FROM( $select ) rWHEREROWNUM <= :end_row            )WHERE :start_row <= row_number";    $stmt = oci_parse($conn,$sql);    oci_bind_by_name($stmt, ':start_row', $start_row);    // Calculate the number of the last row in the page$end_row = $start_row + $rows_per_page - 1;oci_bind_by_name($stmt, ':end_row', $end_row);    oci_execute($stmt);    // Prefetch the number of rows per pageoci_set_prefetch($stmt, $rows_per_page);    return $stmt;}

注意此处的两个子 SELECT 语句。内部的子 SELECT 首先检索 $end_row 之前的所有行，而外部的子 SELECT 滤掉 $start_row 之前的所有行。这是访问您所关注的行的最高效方法，因为它只搜索到 $end_row 之前，而不是先准备整个结果集。

您可能很想知道使用 BETWEEN 子句是否更简单，如：

$sql = "SELECT            *FROM             (SELECTr.*, ROWNUM as row_number FROM:( $select ) r            )WHERE row_number BETWEEN :start_row AND :end_row";

该方法存在这样一个问题：它首先检索结果集中的所有行，然后应用一个过滤器将该结果集缩小至一个相关的、更小的范围，这样做的效率要低很多。

还应注意，我调用了 oci_set_prefetch() 函数，并向它传递每页的行数。这有助于减少 Web 服务器与数据库之间的通信量。（John Lim 在以前引用的文章中更详细介绍了预取。）

与上面的 count_rows() 一样，此 paged_result() 还假设不绑定任何参数。可以在 ADOdb 和 PEAR::DB 库中找到解决此问题的实施。

最大页数

但上面的 paged_result() 函数使用的方法具有潜在的严重缺陷：通常情况下，所搜索的结果集的范围越大，该结果集的速度就越慢，因为它必须先找到 $end_row，然后滤掉 $start_row 之前的行。如果有一个大型结果集和高通信量站点，那么用户请求大量页码的效果无异于拒绝服务。

为避免发生此问题，可以考虑使用固定的最大页码，并拒绝为大于它的页码提供结果。对于容易验证的搜索函数，如果搜索无法找到几个页面之后的结果，则用户应明确地改进他们的搜索。或者，应提供可以帮助用户找到在几个页面中所需的内容的列排序功能。

综合全部

如果在单独的名为 pager_functions.php 的 PHP 脚本中包含以上所有函数，则使用它们浏览 Feedster 数据的代码可能如下所示：

<?php $conn = OCILogon('scott', 'tiger') or die ("Unable to connect to db");require_once 'pager_functions.php';$rows_per_page = 20;$url = 'feedster_top500.php'; // URL to this script$sql = 'SELECT * FROM feedster_top_blogs ORDER BY rank ASC'; // The unfiltered SELECT// Get the total page count from the number of rows$total_rows = count_rows($conn,$sql);$total_pages = total_pages($total_rows, $rows_per_page);// Make sure the page number is a sane valueif ( !isset($_GET['page']) ||!preg_match('/^[0-9]+$/',$_GET['page']) ||$_GET['page'] < 1 ) {    $_GET['page'] = 1;} else if ( $_GET['page'] > $total_pages ) {$_GET['page'] = $total_pages;}// Translate the page number into a starting row number$start_row = page_to_row($_GET['page'], $rows_per_page);// Filter to a single page of rows$stmt = & paged_result($conn, $sql, $start_row, $rows_per_page);?><table width="600"><caption>Feedster Top 500 Blogs [#<?php echo $_GET['page']; ?>]</caption><thead><tr><th>Rank</th><th>Blog</th><th>Inbound Links</th></tr></thead><tbody><?php while (OCIFetchinto($stmt,$row,OCI_ASSOC)) { ?><tr valign="top"><td align="right"><?php echo htmlspecialchars($row['RANK']); ?></td><td><a href="<?php echo htmlspecialchars($row['URL']); ?>"><?php echo htmlspecialchars($row['NAME']); ?>)</a></td><td align="right"><?php echo htmlspecialchars($row['LINKS']); ?></td></tr><?php } ?></tbody><tfoot><tr><td colspan="3" align="center"><?php echo draw_pager($url, $total_pages, $_GET['page']); ?></td></tr></tfoot></table>

pager 显示在该表底部的 <tfoot<//> 标记中，如以下屏幕截图所示。

 

结论

您现在应了解了实施分页结果集所需的元素以及潜在的性能问题。此外，您现在创建了可以针对您自己的应用程序进行扩展的示例代码。

http://www.oracle.com/technetwork/cn/articles/fuecks-paged-083520-zhs.html