Oracle 彙總函式解決聚集連接字串問題

需求：
給定資料表：wm_test
code name
1    a
1    b
1    c
2    中
2    國
2    人

需要的結果（分隔字元可以由參數輸入）：
code name
1    a,b,c
2    中,國,人

這個問題在可以使用Oracle的wmsys.wm_concat 函數解決：
select wt.code, wm_concat(wt.name) names from wm_test wt group by wt.code;

但是這有三個問題：
1. wmsys.wm_concat 是10g才有的，以前的版本無法使用
2. wmsys.wm_concat 是ORACLE內建函式，沒有對外公布，也就是說，你可以使用，但是如果發生什麼問題ORACLE概不負責。最顯然的是ORACLE版本從10.2.0.4升級到10.2.0.5，只是一個小版本的變更，足以讓你的系統出現問題。

解決方案：
1. 升級到Oracle 11g Release 2，此版本引入了LISTAGG 函數，使得聚集連接字串變得很容易，並且允許使用我們指定串連串中的欄位順序。
2. 用自己定義的彙總函式替換wmsys.wm_concat

代碼如下：

-- 1. 建立測試表和資料：CREATE TABLE WM_TEST(  CODE  INTEGER,  NAME  VARCHAR2(20 BYTE));Insert into WM_TEST (CODE, NAME) Values (1, 'a');Insert into WM_TEST (CODE, NAME) Values (1, 'b');Insert into WM_TEST (CODE, NAME) Values (1, 'c');Insert into WM_TEST (CODE, NAME) Values (2, '中');Insert into WM_TEST (CODE, NAME) Values (2, '國');Insert into WM_TEST (CODE, NAME) Values (2, '人');COMMIT;-- 2. 建立自訂彙總函式CREATE OR REPLACE TYPE ConcatObj AS OBJECT(   fieldValue VARCHAR2 (4000),   separator VARCHAR2 (100))/CREATE OR REPLACE TYPE type_wm_concat   AS OBJECT(   l_join_str VARCHAR2 (32767 BYTE),                                -- 串連後的字串   l_flag VARCHAR2 (100 BYTE),                             -- 分隔字元，預設值可在body中定義   STATIC FUNCTION ODCIAggregateInitialize                              -- 初始化                                           (sctx IN OUT type_wm_concat)      RETURN NUMBER,   MEMBER FUNCTION ODCIAggregateIterate                          -- 迭代器，處理每行資料                                        (self    IN OUT type_wm_concat,                                         VALUE   IN     ConcatObj)      RETURN NUMBER,   MEMBER FUNCTION ODCIAggregateTerminate                         -- 迭代結束後處理代碼                                          (self       IN OUT type_wm_concat,                                           return_v      OUT VARCHAR2,                                           flags      IN     NUMBER)      RETURN NUMBER,   MEMBER FUNCTION ODCIAggregateMerge                                  -- 結果合并                                      (self   IN OUT type_wm_concat,                                       ctx2   IN     type_wm_concat)      RETURN NUMBER);/CREATE OR REPLACE TYPE BODY type_wm_concatIS   STATIC FUNCTION ODCIAggregateInitialize                              -- 初始化                                           (sctx IN OUT type_wm_concat)      RETURN NUMBER   IS   BEGIN      sctx := type_wm_concat (NULL, NULL);      RETURN ODCIConst.success;   END ODCIAggregateInitialize;   MEMBER FUNCTION ODCIAggregateIterate                          -- 迭代器，處理每行資料                                        (self    IN OUT type_wm_concat,                                         VALUE   IN     ConcatObj)      RETURN NUMBER   IS   BEGIN      IF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULL      THEN         self.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;      ELSIF VALUE.fieldValue IS NOT NULL      THEN         self.l_join_str := VALUE.fieldValue;         self.l_flag := VALUE.separator;      END IF;      RETURN ODCIConst.Success;   END;   MEMBER FUNCTION ODCIAggregateTerminate                         -- 迭代結束後處理代碼                                          (self       IN OUT type_wm_concat,                                           return_v      OUT VARCHAR2,                                           flags      IN     NUMBER)      RETURN NUMBER   IS   BEGIN      return_v := self.l_join_str;      RETURN ODCIConst.Success;   END;   MEMBER FUNCTION ODCIAggregateMerge (self   IN OUT type_wm_concat,                                       ctx2   IN     type_wm_concat)      RETURN NUMBER   IS   BEGIN      IF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULL      THEN         self.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;      ELSIF ctx2.l_join_str IS NOT NULL      THEN         self.l_join_str := ctx2.l_join_str;      END IF;      RETURN ODCIConst.Success;   END;END;/-- 3. 封裝為一個普通的SQL函數：CREATE OR REPLACE FUNCTION my_wm_concat (pi_str ConcatObj)   RETURN VARCHAR2   PARALLEL_ENABLE   AGGREGATE USING type_wm_concat;/-- 4. 測試：SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) names    FROM wm_test wtGROUP BY wt.code;

code name
1    a|+=b|+=c
2    中|+=國|+=人