Oracle 字串彙總函數演進：9i 自訂型別 vs 10g WM_CONCAT

跨版本實現 GROUP BY 字串串接的最佳方案選擇

將分組內多行字串合併為單一字串是常見報表需求。Oracle 9i 需自訂彙總型別，10g 引入 WM_CONCAT，11gR2+ 標準化 LISTAGG。本文詳細對比三代方案語法、效能與適用場景。

三代方案對照

9i 自訂彙總型別完整實作

-- 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, value IN 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, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
);
/

-- 2. 建立型別主體
CREATE OR REPLACE TYPE BODY strcat_type IS
  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, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    self.cat_string := self.cat_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN OUT strcat_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := LTRIM(RTRIM(self.cat_string, ','), ',');
    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;
END;
/

-- 3. 建立彙總函數
CREATE OR REPLACE FUNCTION strcat(input VARCHAR2) RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING strcat_type;
/

使用範例

-- 9i/10g 通用查詢
SELECT t2.kdm_mdid_pk, t2.kdm_title,
       strcat(t3.subject_mc_content) AS message  -- 9i 自訂函數
       -- WMSYS.WM_CONCAT(t3.subject_mc_content)  -- 10g 內建
FROM t_knodoc_rel_subjects t1
JOIN t_kno_doc_metadata t2 ON t1.krs_kdm_mdid_fk = t2.kdm_mdid_pk
JOIN t_subject_multilingual_content t3 ON t1.krs_subid_fk = t3.subject_mc_id_pk
GROUP BY t2.kdm_mdid_pk, t2.kdm_title;

11gR2+ LISTAGG 最佳實踐

SELECT t2.kdm_mdid_pk, t2.kdm_title,
       LISTAGG(t3.subject_mc_content, ',') 
         WITHIN GROUP (ORDER BY t3.subject_mc_id_pk) AS message
FROM ...
GROUP BY t2.kdm_mdid_pk, t2.kdm_title;

-- 長字串截斷處理 (12cR2+)
LISTAGG(t3.subject_mc_content, ',') 
  WITHIN GROUP (ORDER BY t3.subject_mc_id_pk)
  ON OVERFLOW TRUNCATE '...' WITH COUNT

遷移建議

• 9i 環境：維持自訂型別，或升級資料庫
• 10g/11gR1：WM_CONCAT 簡單可用，但注意 4000 字元限制與非官方支援風險
• 11gR2+：強制遷移至 LISTAGG，標準 SQL、效能最佳、功能最完整
• 跨版本相容程式碼：封裝 PL/SQL 函數內部判斷版本呼叫對應實作

效能優化提示

• 大量資料彙總前先過濾/聚合減少輸入行數
• 確保分組欄位有索引支援
• 平行查詢提示：/*+ PARALLEL(4) */ 加速彙總

選對版本對應的彙總方案，能在開發效率與執行效能間取得最佳平衡。