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

代码如下：

[sql]  view plain  copy

1. -- 1. 建立测试表和数据：  
2. CREATE TABLE WM_TEST  
3. (  
4.   CODE  INTEGER,  
5.   NAME  VARCHAR2(20 BYTE)  
6. );  
7.   
8. Insert into WM_TEST (CODE, NAME) Values (1, 'a');  
9. Insert into WM_TEST (CODE, NAME) Values (1, 'b');  
10. Insert into WM_TEST (CODE, NAME) Values (1, 'c');  
11. Insert into WM_TEST (CODE, NAME) Values (2, '中');  
12. Insert into WM_TEST (CODE, NAME) Values (2, '国');  
13. Insert into WM_TEST (CODE, NAME) Values (2, '人');  
14. COMMIT;  
15.   
16. -- 2. 建立自定义聚合函数  
17. CREATE OR REPLACE TYPE ConcatObj AS OBJECT  
18. (  
19.    fieldValue VARCHAR2 (4000),  
20.    separator VARCHAR2 (100)  
21. )  
22. /  
23.   
24. CREATE OR REPLACE TYPE type_wm_concat  
25.    AS OBJECT  
26. (  
27.    l_join_str VARCHAR2 (32767 BYTE),                                -- 连接后的字符串  
28.    l_flag VARCHAR2 (100 BYTE),                             -- 分隔符，默认值可在body中定义  
29.    STATIC FUNCTION ODCIAggregateInitialize                              -- 初始化  
30.                                            (sctx IN OUT type_wm_concat)  
31.       RETURN NUMBER,  
32.    MEMBER FUNCTION ODCIAggregateIterate                          -- 迭代器，处理每行数据  
33.                                         (self    IN OUT type_wm_concat,  
34.                                          VALUE   IN     ConcatObj)  
35.       RETURN NUMBER,  
36.    MEMBER FUNCTION ODCIAggregateTerminate                         -- 迭代结束后处理代码  
37.                                           (self       IN OUT type_wm_concat,  
38.                                            return_v      OUT VARCHAR2,  
39.                                            flags      IN     NUMBER)  
40.       RETURN NUMBER,  
41.    MEMBER FUNCTION ODCIAggregateMerge                                  -- 结果合并  
42.                                       (self   IN OUT type_wm_concat,  
43.                                        ctx2   IN     type_wm_concat)  
44.       RETURN NUMBER  
45. );  
46.   
47. /  
48.   
49. CREATE OR REPLACE TYPE BODY type_wm_concat  
50. IS  
51.    STATIC FUNCTION ODCIAggregateInitialize                              -- 初始化  
52.                                            (sctx IN OUT type_wm_concat)  
53.       RETURN NUMBER  
54.    IS  
55.    BEGIN  
56.       sctx := type_wm_concat (NULL, NULL);  
57.       RETURN ODCIConst.success;  
58.    END ODCIAggregateInitialize;  
59.   
60.    MEMBER FUNCTION ODCIAggregateIterate                          -- 迭代器，处理每行数据  
61.                                         (self    IN OUT type_wm_concat,  
62.                                          VALUE   IN     ConcatObj)  
63.       RETURN NUMBER  
64.    IS  
65.    BEGIN  
66.       IF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULL  
67.       THEN  
68.          self.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;  
69.       ELSIF VALUE.fieldValue IS NOT NULL  
70.       THEN  
71.          self.l_join_str := VALUE.fieldValue;  
72.          self.l_flag := VALUE.separator;  
73.       END IF;  
74.   
75.       RETURN ODCIConst.Success;  
76.    END;  
77.   
78.   
79.    MEMBER FUNCTION ODCIAggregateTerminate                         -- 迭代结束后处理代码  
80.                                           (self       IN OUT type_wm_concat,  
81.                                            return_v      OUT VARCHAR2,  
82.                                            flags      IN     NUMBER)  
83.       RETURN NUMBER  
84.    IS  
85.    BEGIN  
86.       return_v := self.l_join_str;  
87.       RETURN ODCIConst.Success;  
88.    END;  
89.   
90.   
91.    MEMBER FUNCTION ODCIAggregateMerge (self   IN OUT type_wm_concat,  
92.                                        ctx2   IN     type_wm_concat)  
93.       RETURN NUMBER  
94.    IS  
95.    BEGIN  
96.       IF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULL  
97.       THEN  
98.          self.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;  
99.       ELSIF ctx2.l_join_str IS NOT NULL  
100.       THEN  
101.          self.l_join_str := ctx2.l_join_str;  
102.       END IF;  
103.   
104.       RETURN ODCIConst.Success;  
105.    END;  
106. END;  
107.   
108. /  
109.   
110. -- 3. 封装为一个普通的SQL函数：  
111. CREATE OR REPLACE FUNCTION my_wm_concat (pi_str ConcatObj)  
112.    RETURN VARCHAR2  
113.    PARALLEL_ENABLE  
114.    AGGREGATE USING type_wm_concat;  
115. /  
116.   
117. -- 4. 测试：  
118. SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) names  
119.     FROM wm_test wt  
120. GROUP BY wt.code;  

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