Oracle 儲存過程實現分頁查詢的完整範例

前言

在 Oracle 資料庫開發中，分頁查詢是常見需求。除了在應用層處理，透過儲存過程搭配遊標（Ref Cursor）同樣能高效完成分頁邏輯。本文將完整示範如何建立套件、儲存過程與測試程式碼，幫助你快速掌握此技巧。

核心步驟

1. 建立套件定義遊標類型

先建立一個套件，定義 REF CURSOR 型別，供儲存過程回傳結果集使用。

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE Test_CURSOR IS REF CURSOR;
END TESTPACKAGE;

2. 撰寫分頁儲存過程

儲存過程接收起始與結束行號，利用巢狀子查詢與 ROWNUM 過濾目標區間，最後透過 OUT 參數回傳遊標。

CREATE OR REPLACE PROCEDURE TESTC (
  p_CURSOR OUT TESTPACKAGE.Test_CURSOR,
  lowerNum IN NUMBER,
  higherNum IN NUMBER
) IS
BEGIN
  OPEN p_CURSOR FOR
    SELECT * FROM (
      SELECT row_.*, ROWNUM rownum_
      FROM (SELECT id, title, status FROM HYQTEST) row_
      WHERE ROWNUM <= higherNum
    )
    WHERE rownum_ > lowerNum;
END TESTC;

3. PL/SQL 測試腳本

在 PL/SQL 環境中宣告變數、呼叫儲存過程、迴圈抓取遊標資料並輸出，驗證分頁效果。

DECLARE
  lowerNum  INTEGER := 1;
  higherNum INTEGER := 10;
  id        VARCHAR2(10);
  title     VARCHAR2(500);
  status    NUMBER;
  rownum_   INTEGER;
  c         TESTPACKAGE.Test_CURSOR;
BEGIN
  TESTC(c, lowerNum, higherNum);
  LOOP
    FETCH c INTO id, title, status, rownum_;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('行號=' || rownum_ || ' ID=' || id || ' 標題=' || title || ' 狀態=' || status);
  END LOOP;
  CLOSE c;
END;

重點說明

• 採用雙層 ROWNUM 寫法：內層限制最大行號，外層過濾最小行號，效能優於單層 BETWEEN 寫法。
• 儲存過程封裝分頁邏輯，應用端只需傳入頁碼與頁大小即可，降低 SQL 注入風險並利於維護。
• 測試時記得開啟 DBMS_OUTPUT 以查看列印結果。