oracle聚合函数调用,Oracle聚合函数解决聚集连接字符串问题-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. 建立测试表和数据：

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_concat

IS

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 wt

GROUP BY wt.code;

code name

1 a|+=b|+=c

2 中|+=国|+=人