您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP + Oracle 分頁查詢實戰：ROWNUM 三層巢狀查詢法完整解析

編輯：PHP 綜合知識

前言：Oracle 無 LIMIT/OFFSET 的歷史包袱

MySQL/PostgreSQL/SQL Server 2012+ 皆支援 `LIMIT n OFFSET m` 或 `OFFSET m ROWS FETCH NEXT n ROWS ONLY`。Oracle 12c 前**無標準分頁語法**，必須利用 `ROWNUM` 偽欄位配合巢狀查詢實現。本文解析經典「三層巢狀查詢法」並提供現代化 PDO 重構版本。

核心邏輯：三層 SELECT 巢狀結構

-- 第 1 層 (最內層)：基礎查詢 + 排序
SELECT PK_ID, FIELD1, FIELD2, FIELD3
FROM SAMPLE_TABLE
ORDER BY FIELD3

-- 第 2 層：套用 ROWNUM (注意：ROWNUM 在 ORDER BY 之前產生！)
SELECT PK_ID, FIELD1, FIELD2, FIELD3, ROWNUM AS ROW_NO
FROM ( 第1層查詢 )

-- 第 3 層 (最外層)：篩選頁碼範圍
SELECT PK_ID, FIELD1, FIELD2, FIELD3, ROW_NO
FROM ( 第2層查詢 )
WHERE ROW_NO BETWEEN :start AND :end

關鍵陷阱：`ROWNUM` 是在 `WHERE`/`ORDER BY` **之前** 分配的。若直接 `WHERE ROWNUM BETWEEN 11 AND 20` 會**永無結果**，因為第一筆 ROWNUM=1 不滿足 `>=11` 被過濾，後續筆永無機會得到 ROWNUM=11。故**必須**先在中間層將完整排序結果賦予 ROWNUM，外層再篩選。

完整 PHP PDO 實作 (含連線、總筆數、分頁連結)

<?php
// config.php
$dsn = 'oci:dbname=//192.168.3.202:1521/unicom;charset=AL32UTF8';
$user = 'cinmsiii'; $pass = 'cinmsiii';
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

// pagination.php
require 'config.php';
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 5;

// 1. 取得總筆數 (可快取)
$total = $pdo->query("SELECT COUNT(*) FROM SAMPLE_TABLE")->fetchColumn();
$totalPages = (int)ceil($total / $perPage);
$page = min($page, $totalPages ?: 1);

// 2. 計算範圍
$start = ($page - 1) * $perPage + 1;
$end   = $page * $perPage;

// 3. 三層巢狀分頁查詢 (使用具名參數)
$sql = "
    SELECT PK_ID, FIELD1, FIELD2, FIELD3, ROW_NO
    FROM (
        SELECT PK_ID, FIELD1, FIELD2, FIELD3, ROWNUM AS ROW_NO
        FROM (
            SELECT PK_ID, FIELD1, FIELD2, FIELD3
            FROM SAMPLE_TABLE
            ORDER BY FIELD3
        )
    )
    WHERE ROW_NO BETWEEN :start AND :end
";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':start', $start, PDO::PARAM_INT);
$stmt->bindValue(':end', $end, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();

// 4. 輸出 HTML (簡化版)
?>
<table><thead><tr><th>PK_ID</th><th>FIELD1</th><th>FIELD2</th><th>FIELD3</th><th>ROW_NO</th></tr></thead><tbody>
<?php foreach ($rows as $r): ?>
<tr>
    <td><?= $r['PK_ID'] ?></td>
    <td><?= htmlspecialchars($r['FIELD1']) ?></td>
    <td><?= htmlspecialchars($r['FIELD2']) ?></td>
    <td><?= htmlspecialchars($r['FIELD3']) ?></td>
    <td><?= $r['ROW_NO'] ?></td>
</tr>
<?php endforeach; ?>
</tbody></table>

<nav aria-label="Pagination">
<?php if ($page > 1): ?><a href="?page=<?=$page-1?>">上一頁</a><?php endif; ?>
<?php for ($i=1; $i<=$totalPages; $i++): ?>
    <?php if ($i == $page): ?><span class="current"><?=$i?></span>
    <?php else: ?><a href="?page=<?=$i?>"><?=$i?></a><?php endif; ?>
<?php endfor; ?>
<?php if ($page < $totalPages): ?><a href="?page=<?=$page+1?>">下一頁</a><?php endif; ?>
</nav>

Oracle 12c+ 現代寫法：OFFSET ... FETCH

Oracle 12c (12.1) 起支援 ANSI SQL 標準分頁語法，**效能更佳、語意清晰、無需巢狀**：

SELECT PK_ID, FIELD1, FIELD2, FIELD3
FROM SAMPLE_TABLE
ORDER BY FIELD3
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY

對應 PDO：

$offset = ($page - 1) * $perPage;
$sql = "SELECT ... FROM SAMPLE_TABLE ORDER BY FIELD3 OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);

效能優化建議

索引覆蓋：`ORDER BY` 欄位 + `WHERE` 篩選欄位建立複合索引，避免全表掃描 + 排序。
游標分頁：超大資料集 (百萬筆+) 用 `WHERE pk > :last_seen_id ORDER BY pk LIMIT n` (Keyset Pagination)，避免 `OFFSET` 深分頁效能崩塌。
總筆數快取**：`COUNT(*)` 昂貴，可用 Redis 快取 5-15 分鐘，或改用「估算總數」+「是否有下一頁」簡化邏輯。
連線池**：生產環境必用 OCI8 持久連線或 PHP-FPM + Oracle DRCP。