oracle中，wm_concat函数是一个聚合函数，和mysql中的group_concat函数类似，不过group_concat函数比较强大，可以定义分隔符和排序，当然所谓强大是相对的，这里假使我们不知道oracle中的over函数，也不知道listagg函数。
我们先来看看wm_concat函数能实现什么功能，通俗点==>列传行，如果不明白，请看下面截图(可以看到分隔符默认为','顺序也是杂乱的)
所以，接下来，我们开始重写wm_concat函数(需要注意和需要说明的地方放在代码注释中...)
(1) 因为需要进行排序，首先自定义一个可变数组
|
1
2
|
-- 定义可变数组，字符串类型，长度32767，存放列值CREATE OR REPLACE TYPE WYARRAY as TABLE OF VARCHAR(32767) ; |
(2)自定义排序函数、分隔符函数
|
1
2
3
4
5
6
7
8
9
10
11
12
|
-- 定义分隔符函数create or replace function delimiter(colValue in varchar2, delimiter in varchar2) return varchar2 is rtnValue varchar2(32767);begin rtnValue := colValue || ' delimiter=>' || delimiter || '; '; return rtnValue;end delimiter; |
|
1
2
3
4
5
6
7
8
9
10
11
12
|
-- 定义排序函数create or replace function orderby(colValue in varchar2, orderby in varchar2) return varchar2 is rtnValue varchar2(32767);begin rtnValue := colValue || ' orderby=>' || LOWER(orderby) || '; '; return rtnValue;end orderby; |
(3) 重定义oracle接口函数、以及接口函数的实现体(实现分隔符和排序的主要代码)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
-- 使用当前用户权限(使用authid current_user，定义type为用户当前用户的权限，举个例子：比如A用户他可以建立表，但是A用户在存储过程中如果建立表可能会提示权限不够，所以需要用authid current_user进行约束)create or replace type wy_wm_concat authid current_user as object(--拼接字符串,存放中间值，当然也可以定义为clob，clob会使用临时段，导致临时表空间迅速增大;--查看wmsys下的function可以发现Oracle10g到oracle11g自带的wm_concat函数的返回类型从clob变成varchar2 currStr VARCHAR2(32767),--分割字符串 delimiter VARCHAR2(64),--排序字符串(asc、desc) orderby VARCHAR2(64),-- 定义字符串数组 strArray WYARRAY,-- 初始化接口函数 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat) RETURN NUMBER,-- 迭代接口函数 MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT wy_wm_concat, colValue IN VARCHAR2) RETURN NUMBER,-- 并行时字符串合并的接口函数 MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat, next wy_wm_concat) RETURN NUMBER,-- oracle终止接口函数 MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN wy_wm_concat, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER) |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
create or replace type body wy_wm_concat is --定义函数的body --初始化函数 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat) RETURN NUMBER is begin init := wy_wm_concat('', ',', 'asc', WYARRAY()); return ODCICONST.Success; END; -- 字符串拼接，self 为当前聚集函数的指针,用来与前面的计算结果进行关联 MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT wy_wm_concat, colValue IN VARCHAR2) RETURN NUMBER is tempStr varchar(500); extendStr varchar(500); deStr varchar(100); deLen int default 0; segStr varchar(500); --定义一个二维数组 TYPE varArry IS VARRAY(2) OF VARCHAR2(200); tempArry varArry := varArry('', ''); begin if instr(colValue, ' ', 1) > 0 then tempStr := substr(colValue, 1, instr(colValue, ' ', 1) - 1); else tempStr := colValue; end if; --排序和分隔符 extendStr := REPLACE(colValue, tempStr || ' '); if instr(extendStr, ' ', 1) > 0 then tempArry(1) := substr(extendStr, 1, instr(extendStr, ' ', 1) - 1); tempArry(2) := substr(extendStr, instr(extendStr, ' ', 1)); for i in 1 .. tempArry.count loop -- 获取分隔符 if (tempArry(i) is not null) and (instr(tempArry(i), 'delimiter=>') > 0) THEN deStr := 'delimiter=>'; deLen := length(deStr); segStr := substr(trim(tempArry(i)), instr(trim(tempArry(i)), deStr) + deLen); self.delimiter := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1); END IF; -- 获取排序字符串 if tempArry(i) is not null and (instr(tempArry(i), 'orderby=>') > 0) THEN deStr := 'orderby=>'; deLen := length(deStr); segStr := substr(trim(tempArry(i)), instr(trim(tempArry(i)), deStr) + deLen); self.orderby := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1); END IF; end loop; end if; -- 存放入数组 self.strArray.extend; self.strArray(self.strArray.count) := tempStr; return ODCICONST.Success; END; --并行操作是用来合并两个聚集函数的两个不同的指针对应的结果 MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat, next wy_wm_concat) RETURN NUMBER is begin -- 将next数组中元素全部放入self指针对应的数组中 for i in 1 .. next.strArray.count loop self.strArray.extend; self.strArray(self.strArray.count) := next.strArray(i); end loop; return ODCICONST.Success; END; -- 终止函数，返回结果 MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN wy_wm_concat, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS temp_rtnValue varchar2(32767); BEGIN -- 排序 if INSTR(self.orderby, 'desc') > 0 THEN for x in (select column_value from Table(self.strArray) order by 1 DESC) loop temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value; end loop; ELSE for x in (select column_value from Table(self.strArray) order by 1 ASC) loop temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value; end loop; END IF; returnValue := ltrim(temp_rtnValue, self.delimiter); return ODCICONST.Success; END;END; |
(4)自定义聚集函数
|
1
2
3
|
-- 定义聚集函数(未开启并行计算功能)create or replace function wy_concat(colValue VARCHAR2) RETURN VARCHAR2 AGGREGATE USING wy_wm_concat; |
至此，主要的代码已经全部奉上，看看运行效果，如下截图：
①看看调用的默认情况(分隔符默认是逗号，排序默认是升序，在初始化函数中如此定义的)
②自定义分隔符(利用分隔符函数将分隔符定义为*)
③降序排序
④去重，为了可以使用wm_concat自带的去重函数，所以在自定义分隔符和排序函数时，实质是实用了字符串处理(如果你觉得处理字符串麻烦，可以自定义 type... as object ，在使用的时候可以很方便，不会用的童鞋可以私下问)
