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 分頁語法簡化開發。