Oracle查詢結果字串聚合：兩種自訂函數實作與應用場景

需求場景

將多行資料聚合為單一字串，例如：

-- 原始資料
DEPARTMENT  ENAME
Marketing   Hartstein
Marketing   Fay
Purchasing  Raphaely
Purchasing  Khoo

-- 期望結果
DEPARTMENT  EMPLOYEES
Marketing   Hartstein,Fay
Purchasing  Raphaely,Khoo

方法一：靜態SQL函數 (固定查詢條件)

CREATE OR REPLACE FUNCTION concat_dept(p_dep IN VARCHAR2) RETURN VARCHAR2 IS
  l_str VARCHAR2(200) DEFAULT NULL;
  l_sep VARCHAR2(200) DEFAULT NULL;
BEGIN
  FOR rc IN (SELECT ename FROM d_employee WHERE department = p_dep) LOOP
    l_str := l_str || l_sep || rc.ename;
    l_sep := ',';
  END LOOP;
  RETURN l_str;
END;

查詢用法：

SELECT d.department, concat_dept(d.department) employees
FROM d_employee d
GROUP BY d.department;

方法二：動態SQL函數 (彈性指定表/欄位/條件)

CREATE OR REPLACE FUNCTION concat_dyn(
  p_key_name      IN VARCHAR2,  -- 條件欄位名
  p_key_val       IN VARCHAR2,  -- 條件值
  p_other_col     IN VARCHAR2,  -- 要聚合的欄位
  p_table_name    IN VARCHAR2   -- 來源表
) RETURN VARCHAR2 AS
  TYPE rc_type IS REF CURSOR;
  l_cur   rc_type;
  l_str   VARCHAR2(4000);
  l_sep   VARCHAR2(200);
  l_val   VARCHAR2(4000);
BEGIN
  OPEN l_cur FOR 'SELECT ' || p_other_col || ' FROM ' || p_table_name || 
                 ' WHERE ' || p_key_name || ' = :x' USING p_key_val;
  LOOP
    FETCH l_cur INTO l_val;
    EXIT WHEN l_cur%NOTFOUND;
    l_str := l_str || l_sep || l_val;
    l_sep := ',';
  END LOOP;
  CLOSE l_cur;
  RETURN l_str;
END;

查詢用法：

SELECT d.department, 
       concat_dyn('department', d.department, 'ename', 'd_employee')
FROM d_employee d
GROUP BY d.department;

補充：已知值清單時的DECODE替代方案

若聚合欄位值為固定有限集合(如'A','T','L')，可用DECODE/CASE配合MAX/WM_CONCAT(11g+)或LISTAGG(11gR2+)原生函數，效能更佳。

版本演進提醒

• Oracle 10g及以前：需自訂函數
• Oracle 11g：引入WM_CONCAT(非文件化)
• Oracle 11gR2+：標準化LISTAGG，LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename)
• Oracle 19c+：支援JSON_ARRAYAGG等進階聚合

建議：現代版本優先使用LISTAGG，僅在舊版本或需動態表名時使用自訂函數。