萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle PL/SQL 封裝實現分組分頁查詢:動態 SQL + REF CURSOR 完整範例

一、封裝規格

CREATE OR REPLACE PACKAGE p_pagewithgroup IS
  TYPE type_cur IS REF CURSOR;  -- 遊標變數型別

  PROCEDURE UP_GetRecordWithGroupByPage (
    tblName        IN  VARCHAR2,  -- 表名
    PageSize       IN  NUMBER,    -- 頁大小
    PageIndex      IN  NUMBER,    -- 當前頁 (1-based)
    IsReCount      OUT NUMBER,    -- 總筆數
    strWhere       IN  VARCHAR2,  -- WHERE 條件 (不含 WHERE 關鍵字)
    strColums      IN  VARCHAR2,  -- 內層 SELECT 欄位
    strSelectColums IN VARCHAR2,  -- 外層 SELECT 欄位
    strGroup       IN  VARCHAR2,  -- GROUP BY 條件
    strOrder       IN  VARCHAR2,  -- ORDER BY 條件
    v_cur          OUT type_cur   -- 回傳遊標
  );
END p_pagewithgroup;
/
SHOW ERRORS

二、封裝主體實作

CREATE OR REPLACE PACKAGE BODY p_pagewithgroup IS
  PROCEDURE UP_GetRecordWithGroupByPage ( ... ) AS
    v_sql       VARCHAR2(8000);
    v_count     NUMBER;
    v_row_start NUMBER;
    v_row_end   NUMBER;
  BEGIN
    -- 1. 計算總筆數 (含 GROUP BY)
    v_sql := 'SELECT COUNT(1) FROM ' || tblName;
    IF strWhere IS NOT NULL AND strWhere <> '' THEN
      v_sql := v_sql || ' WHERE ' || strWhere;
    END IF;
    IF strGroup IS NOT NULL AND strGroup <> '' THEN
      v_sql := v_sql || ' GROUP BY ' || strGroup;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Count SQL: ' || v_sql);
    EXECUTE IMMEDIATE v_sql INTO v_count;
    IsReCount := v_count;

    -- 2. 分頁查詢 (ROWNUM 雙層嵌套)
    v_row_start := (PageIndex - 1) * PageSize + 1;
    v_row_end   := PageIndex * PageSize;

    v_sql := 'SELECT ' || strSelectColums || ' FROM (' ||
             'SELECT t.*, ROWNUM AS rn FROM (' ||
             'SELECT ' || strColums || ' FROM ' || tblName;
    IF strWhere IS NOT NULL AND strWhere <> '' THEN
      v_sql := v_sql || ' WHERE ' || strWhere;
    END IF;
    IF strGroup IS NOT NULL AND strGroup <> '' THEN
      v_sql := v_sql || ' GROUP BY ' || strGroup;
    END IF;
    IF strOrder IS NOT NULL AND strOrder <> '' THEN
      v_sql := v_sql || ' ORDER BY ' || strOrder;
    END IF;
    v_sql := v_sql || ') t) WHERE rn BETWEEN ' || v_row_start || ' AND ' || v_row_end;

    DBMS_OUTPUT.PUT_LINE('Page SQL: ' || v_sql);
    OPEN v_cur FOR v_sql;
  END UP_GetRecordWithGroupByPage;
END p_pagewithgroup;
/
SHOW ERRORS

三、測試呼叫腳本

SET SERVEROUTPUT ON SIZE 100000
DECLARE
  l_count NUMBER;
  l_cur   p_pagewithgroup.type_cur;
  l_dummy VARCHAR2(4000);
BEGIN
  p_pagewithgroup.UP_GetRecordWithGroupByPage(
    'T_DIVISION',  -- 表名
    5,             -- PageSize
    1,             -- PageIndex
    l_count,       -- OUT 總筆數
    '1=1',         -- WHERE
    '*',           -- 內層欄位
    '*',           -- 外層欄位
    NULL,          -- GROUP BY
    NULL,          -- ORDER BY
    l_cur          -- OUT 遊標
  );
  DBMS_OUTPUT.PUT_LINE('Total: ' || l_count);
  
  -- 讀取遊標示範
  LOOP
    FETCH l_cur INTO l_dummy;
    EXIT WHEN l_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_dummy);
  END LOOP;
  CLOSE l_cur;
END;
/
```

四、關鍵設計考量

議題處理方式
SQL 注入風險參數直接拼接,生產環境**必須**加入輸入驗證/白名單,或改用 DBMS_ASSERT 套件
效能雙層 ROWNUM 為 10g/11g 標準分頁寫法;12c+ 建議改用 OFFSET ... FETCH
GROUP BY 分頁總筆數查詢必須包含 GROUP BY,否則計數錯誤
遊標生命週期呼叫端負責 FETCH/CLOSE,避免遊標洩漏
大數據量優化改用 ROW_NUMBER() OVER (ORDER BY ...) + 索引,避免全表排序

五、12c+ 現代化寫法對比

SELECT * FROM (
  SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY ord DESC) rn
  FROM tbl WHERE cond
) WHERE rn BETWEEN :start AND :end
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;  -- 12c 簡化語法

此封裝展示了 Oracle 動態 SQL、REF CURSOR、分頁邏輯封裝的經典模式,適合 10g/11g 環境;新版本建議直接使用 ANSI SQL 分頁語法簡化開發。

copyright © 萬盛學電腦網 all rights reserved