PHP 操作 Oracle 實現高效分頁查詢 (OCI8 / PDO_OCI)

Oracle 分頁特殊性

Oracle 無 MySQL LIMIT、SQL Server OFFSET/FETCH (2012+) 這類簡單語法。傳統方案依賴 ROWNUM 嵌套查詢，Oracle 12c+ 引入標準 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY。

方案一：傳統 ROWNUM 雙層嵌套 (Oracle 11g 及以下通用)

<?php
// 配置
$conn = oci_connect('user', 'pass', 'localhost/XE', 'AL32UTF8');
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 15;
$offset = ($page - 1) * $perPage;

// 1. 獲取總記錄數
$cntSql = "SELECT COUNT(*) FROM xqhtest WHERE id < 50";
$stmt = oci_parse($conn, $cntSql);
oci_execute($stmt);
oci_fetch($stmt);
$total = oci_result($stmt, 1);
$totalPages = ceil($total / $perPage);

// 2. 分頁查詢 (關鍵：ROWNUM 必須在最外層)
$sql = "SELECT * FROM (
            SELECT a.*, ROWNUM rn FROM (
                SELECT id, name FROM xqhtest WHERE id < 50 ORDER BY id DESC
            ) a WHERE ROWNUM <= :maxRow
        ) WHERE rn > :minRow";

$stmt = oci_parse($conn, $sql);
$maxRow = $offset + $perPage;
$minRow = $offset;
oci_bind_by_name($stmt, ':maxRow', $maxRow);
oci_bind_by_name($stmt, ':minRow', $minRow);
oci_execute($stmt);

while ($row = oci_fetch_assoc($stmt)) {
    echo "ID: {$row['ID']} | Name: {$row['NAME']}
";
}

// 3. 生成分頁鏈接
for ($i = 1; $i <= $totalPages; $i++) {
    $active = $i === $page ? ' class="active"' : '';
    echo "$i ";
}
?>

方案二：Oracle 12c+ 標準語法 (更簡潔、效能更好)

$sql = "SELECT id, name FROM xqhtest 
        WHERE id < 50 
        ORDER BY id DESC
        OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY";

$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':offset', $offset);
oci_bind_by_name($stmt, ':limit', $perPage);
oci_execute($stmt);

方案三：使用 PDO_OCI (現代化、可移植)

<?php
$pdo = new PDO('oci:dbname=localhost/XE;charset=AL32UTF8', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

// 總數
$total = $pdo->query("SELECT COUNT(*) FROM xqhtest WHERE id < 50")->fetchColumn();
$pages = ceil($total / $perPage);

// 分頁 (12c+ 語法)
$stmt = $pdo->prepare("SELECT id, name FROM xqhtest WHERE id < 50 ORDER BY id DESC 
                       OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->execute();

foreach ($stmt as $row) {
    echo "{$row['ID']} - {$row['NAME']}
";
}
?>

效能優化關鍵點

1. 索引覆蓋：WHERE、ORDER BY 欄位建立複合索引。
2. 避免 SELECT *：僅查詢需要的欄位，減少網絡傳輸與緩衝區壓力。
3. 游標共享：使用綁定變量 (:offset, :limit) 而非拼接，利用 Cursor Sharing。
4. 大數據深分頁：Offset 極大時效能驟降，改用「游標分頁」：WHERE id > :lastSeenId ORDER BY id LIMIT 15。

常見坑位

• ROWNUM 在 ORDER BY 前賦值，故必須先排序再加 ROWNUM (子查詢)。
• OCI8 預設獲取模式為 OCI_NUM，建議顯式 oci_fetch_assoc() 或設置 oci_set_prefetch()。
• 字符集必須統一為 AL32UTF8，避免中文亂碼。

根據 Oracle 版本選擇方案：11g 用 ROWNUM 嵌套，12c+ 直接用標準 OFFSET/FETCH，新項目優先 PDO_OCI 統一資料庫訪問層。