oracle下wm_concat源码（ps:如果有些版本的oracle不支持此函数，只要执行下下面的语句即可）

最新推荐文章于 2025-11-23 12:09:07 发布

原创最新推荐文章于 2025-11-23 12:09:07 发布 · 3.6k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

oracle

本文介绍了一个使用Oracle数据库自定义类型实现的wm_concat函数，该函数能够聚合字符串并用逗号分隔。文章详细展示了wm_concat函数的创建过程，包括初始化、迭代、终止及合并等步骤。

--创建wm_concat函数
CREATE OR REPLACE TYPE WM_CONCAT_IMPL
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR CLOB,--VARCHAR2(32767),

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER

);

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN 0;
END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN 0;
END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT CLOB,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN 0;
END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN 0;
END;

END;

CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2)
RETURN clob AGGREGATE USING WM_CONCAT_IMPL ;