MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER IS

BEGIN

RETURN ODCICONST.SUCCESS;

END;

END;

/

-- 创建自定义函数

CREATE OR REPLACE FUNCTION f_concatenate_str(i_str VARCHAR2) RETURN VARCHAR2

AGGREGATE USING typ_concatenate_impl;

/

创建测试表和数据，并进行测试

CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));

INSERT INTO TEST VALUES (1, AAA);

INSERT INTO TEST VALUES (2, BBB);

INSERT INTO TEST VALUES (1, ABC);

INSERT INTO TEST VALUES (3, CCC);

INSERT INTO TEST VALUES (2, DDD);

COMMIT;

查看执行后的结果，并与WMSYS.WM_CONCAT 函数执行效果对照。

SQL> SELECT id,f_concatenate_str(name) name FROM test GROUP BY id;

ID NAME

---------- ------------------------------------------------------------------

1 AAA,ABC,

2 BBB,DDD,

3 CCC,

SQL> SELECT id,wmsys.wm_concat(name) name FROM test GROUP BY id;

ID NAME

---------- ------------------------------------------------------------------

1 AAA,ABC

2 BBB,DDD

3 CCC

SQL> SELECT id,f_concatenate_str(name) OVER (PARTITION BY id) name FROM test;

ID NAME

---------- ------------------------------------------------------------------

1 AAA,ABC,

1 AAA,ABC,

2 DDD,BBB,

2 DDD,BBB,

3 CCC,

SQL> SELECT id,wmsys.wm_concat(name) OVER (PARTITION BY id) name FROM te