Oracle自定义聚合函数实现字符串拼接

--定义函数
CREATE OR REPLACE FUNCTION f_catstring (p_str VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING t_stringconcat;
--定义类型
CREATE OR REPLACE TYPE t_stringconcat AS OBJECT (
str VARCHAR2 (1000),
oldstr VARCHAR2 (1000),
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_stringconcat)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT t_stringconcat,
VALUE IN VARCHAR2
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN t_stringconcat,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT t_stringconcat,
ctx2 IN t_stringconcat
)
RETURN NUMBER
);
--定义body
CREATE OR REPLACE TYPE BODY t_stringconcat
IS
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_stringconcat)
RETURN NUMBER
IS
BEGIN
--初始化
sctx := t_stringconcat(NULL, NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT t_stringconcat,
VALUE IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
--解决拼接多个字段重复的问题
IF NOT SELF.oldstr IS NULL AND SELF.oldstr = VALUE
THEN
NULL;
ELSE
SELF.str := SELF.str || VALUE || ', ';
END IF;

SELF.oldstr := VALUE;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN t_stringconcat,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := SUBSTR (SELF.str, 1, LENGTH (SELF.str) - 2);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT t_stringconcat,
ctx2 IN t_stringconcat
)
RETURN NUMBER
IS
BEGIN
NULL;
RETURN odciconst.success;
END;
END;
以前程序在oracle 9i上测试通过.