CREATE OR REPLACE TYPE
typ_concatenate_impl AS OBJECT
(
retstr VARCHAR2(30000),--拼凑使用的中间字符串
SEPARATORFLAGVARCHAR2(64), --分隔符,默认用自由定义|,可以修改此处
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(sctx IN OUT
typ_concatenate_impl) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT typ_concatenate_impl,
value IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN typ_concatenate_impl,
returnvalue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT typ_concatenate_impl,
ctx2 IN typ_concatenate_impl) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY
typ_concatenate_impl IS
--自定义聚集函数初始化操作
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(sctx IN OUT
typ_concatenate_impl) RETURN NUMBER IS
BEGIN
sctx := typ_concatenate_impl('',',');
RETURN ODCICONST.SUCCESS;
END;
--定义函数的功能,实现字符串拼接
MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT typ_concatenate_impl,
value IN VARCHAR2) RETURN NUMBER IS
BEGIN
self.retstr := self.retstr || value||self.SEPARATORFLAG;
RETURN ODCICONST.SUCCESS;
END;
--定义终止聚集函数的处理,返回聚集函数处理的结果
MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN typ_concatenate_impl,
returnvalue OUT VARCHAR2, FLAGS IN NUMBER)
RETURN NUMBER IS
BEGIN
IF returnvalue IS NOT NULL THEN
returnvalue :=
SUBSTR(self.retstr,1,LENGTH(self.retstr)-1);
ELSE
returnvalue := self.retstr;
END IF;
RETURN ODCICONST.SUCCESS;
END;
--用来合并两个聚集函数的两个不同的指针对应的结果,此处默认即可
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;
/