本文介绍如何在Oracle开发环境中通过创建自定义类型实现wm_concat函数的功能，帮助开发者解决数据拼接需求，提升开发效率。
oracle xe 是简版的oracle 开发中经常用到， 没有 wm_concat 函数 开发中如果需要此函数 ，可以通过如下方法添加：
CREATE OR REPLACE TYPE wm_concat_impl
AUTHID CURRENT_USER
AS OBJECT (
curr_str 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 VARCHAR2,
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 odciconst.success;
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 odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN wm_concat_impl,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := curr_str;
RETURN odciconst.success;
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 odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING wm_concat_impl;
/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
