Oracle 多行字串聚合完全指南：從 DECODE 到自定義聚合函數的 6 種方案對比

需求：將 TEST(COUNTRY, CITY) 表中同一國家的城市聚合為逗號分隔字串（中國: 台北,香港,上海）。Oracle 11gR2 前無原生 LISTAGG，本文詳細對比 6 種方案的靈活性、效能、難度，助您按場景選型。

方案對比總覽

方案詳解與代碼

1. 固定值 DECODE + MAX（最笨但有時最快）

SELECT country,
       MAX(DECODE(city,'台北',city||',',NULL)) ||
       MAX(DECODE(city,'香港',city||',',NULL)) ||
       MAX(DECODE(city,'上海',city||',',NULL)) AS cities
FROM test
GROUP BY country;

原理：DECODE 將非目標值轉為 NULL，MAX 忽略 NULL 保留目標值，利用群組特性拼接。缺點： 值集合變動需改 SQL，無法動態。

2. 固定表 PL/SQL 函數

CREATE OR REPLACE FUNCTION str_list(p_country VARCHAR2) RETURN VARCHAR2 IS
  v_str VARCHAR2(4000); v_sep VARCHAR2(2);
BEGIN
  FOR r IN (SELECT city FROM test WHERE country = p_country ORDER BY city) LOOP
    v_str := v_str || v_sep || r.city; v_sep := ', ';
  END LOOP;
  RETURN v_str;
END;
-- 使用
SELECT DISTINCT country, str_list(country) FROM test;
-- 或
SELECT country, str_list(country) FROM test GROUP BY country;

缺點： 每表每欄需寫一個函數，Context Switch 開銷大。

3. 動態 SQL 通用函數

CREATE OR REPLACE FUNCTION str_list2(
  p_key_name VARCHAR2, p_key_val VARCHAR2,  -- 分組鍵
  p_col_name VARCHAR2, p_tab_name VARCHAR2  -- 聚合欄位/表
) RETURN VARCHAR2 IS
  v_cur SYS_REFCURSOR; v_val VARCHAR2(4000); v_str VARCHAR2(4000); v_sep VARCHAR2(2);
BEGIN
  OPEN v_cur FOR 'SELECT '||p_col_name||' FROM '||p_tab_name||
                 ' WHERE '||p_key_name||' = :b1' USING p_key_val;
  LOOP FETCH v_cur INTO v_val; EXIT WHEN v_cur%NOTFOUND;
    v_str := v_str || v_sep || v_val; v_sep := ', ';
  END LOOP; CLOSE v_cur; RETURN v_str;
END;

靈活但動態 SQL 解析開銷大，防注入需驗證識別碼合法性。

4. 單一 SQL：SYS_CONNECT_BY_PATH + 分析函數

SELECT country, MAX(SUBSTR(city_path, 2)) AS cities
FROM (
  SELECT country,
         SYS_CONNECT_BY_PATH(city, ',') AS city_path,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY city) AS rn,
         country || ROW_NUMBER() OVER (PARTITION BY country ORDER BY city) AS rchild,
         country || (ROW_NUMBER() OVER (PARTITION BY country ORDER BY city) - 1) AS rfather
  FROM test
)
WHERE CONNECT_BY_ISLEAF = 1  -- 僅取葉子節點（完整路徑）
CONNECT BY PRIOR rchild = rfather
START WITH rfather LIKE '%0'
GROUP BY country;

利用階層查詢構建路徑，再取葉子節點。邏輯繞彎、效能隨資料量惡化。

5. 自定義聚合函數（王道）：ODCIAggregate 介面

-- 1. 定義型別實作介面
CREATE OR REPLACE TYPE strcat_type AS OBJECT (
  cat_string VARCHAR2(4000),
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT strcat_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT strcat_type, val VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN OUT strcat_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN OUT strcat_type, ret OUT VARCHAR2, flags NUMBER) RETURN NUMBER
);

-- 2. 型別體實作
CREATE OR REPLACE TYPE BODY strcat_type AS
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT strcat_type) RETURN NUMBER IS
  BEGIN ctx := strcat_type(NULL); RETURN ODCIConst.SUCCESS; END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT strcat_type, val VARCHAR2) RETURN NUMBER IS
  BEGIN self.cat_string := self.cat_string || ',' || val; RETURN ODCIConst.SUCCESS; END;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN OUT strcat_type) RETURN NUMBER IS
  BEGIN self.cat_string := self.cat_string || ctx2.cat_string; RETURN ODCIConst.SUCCESS; END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN OUT strcat_type, ret OUT VARCHAR2, flags NUMBER) RETURN NUMBER IS
  BEGIN ret := LTRIM(self.cat_string, ','); RETURN ODCIConst.SUCCESS; END;
END;

-- 3. 封裝函數
CREATE OR REPLACE FUNCTION strcat(input VARCHAR2) RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING strcat_type;

-- 4. 使用：如原生聚合函數般簡潔
SELECT country, strcat(city) FROM test GROUP BY country;

優勢： 並行執行、流式處理無 4000 字元限制（可用 CLOB）、語法原生、效能最優。

6. 現代最佳實務：LISTAGG (11gR2+) / WM_CONCAT (10g/11g 非官方)

-- 官方標準 (11gR2+)
SELECT country, LISTAGG(city, ',') WITHIN GROUP (ORDER BY city) AS cities
FROM test GROUP BY country;

-- 10g/11g 非官方但廣泛可用
SELECT id, WM_CONCAT(city) FROM test GROUP BY id;

選型建議

• Oracle 12c+： **直接用 LISTAGG**，必要時配合 ON OVERFLOW TRUNCATE 處理長度溢位。
• 11gR2-11g 且不可建物件： 方案 4 單一 SQL 或方案 3 動態函數。
• 10g/11g 生產核心系統： 投入開發**自定義聚合函數 (方案 5)**，長期收益最高。
• 極小固定值集： 方案 1 最省事。

注意： VARCHAR2 限 4000 字元 (12c MAX_STRING_SIZE=EXTENDED 可 32K)，超長聚合請改用 CLOB 型別實作 ODCIAggregate。