Oracle 聚合函数解决聚集连接字符串问题

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433250

需求：

给定数据表：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. );
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;
16. -- 2. 建立自定义聚合函数
17. CREATE OR REPLACE TYPE ConcatObj AS OBJECT
18. (
19. fieldValue VARCHAR2 (4000),
20. separator VARCHAR2 (100)
21. )
22. /
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. );
47. /
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;
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;
75. RETURN ODCIConst.Success;
76. END;
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;
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;
104. RETURN ODCIConst.Success;
105. END;
106. END;
108. /
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. /
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 中| =国| =人