17,090
社区成员
发帖
与我相关
我的任务
分享
with t1 as(select 1 id,test1 value from dual union all select 1 id,test2 value from dual ) select id,fun_concat(value) value from t1
CREATE OR REPLACE TYPE string_sum_obj AS OBJECT (
--聚合函数的实质就是一个对象
sum_string VARCHAR2 (4000),
STATIC FUNCTION odciaggregateinitialize (v_self IN OUT string_sum_obj)
RETURN NUMBER,
--对象初始化
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT string_sum_obj,
VALUE IN VARCHAR2
)
RETURN NUMBER,
--聚合函数的迭代方法(这是最重要的方法)
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT string_sum_obj,
v_next IN string_sum_obj
)
RETURN NUMBER,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
MEMBER FUNCTION odciaggregateterminate (
SELF IN string_sum_obj,
return_value OUT VARCHAR2,
v_flags IN NUMBER
)
RETURN NUMBER
--终止聚集函数的处理,返回聚集函数处理的结果.
);
/
CREATE OR REPLACE TYPE BODY string_sum_obj
IS
STATIC FUNCTION odciaggregateinitialize (v_self IN OUT string_sum_obj)
RETURN NUMBER
IS
BEGIN
v_self := string_sum_obj (NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT string_sum_obj,
VALUE IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
/* 连接,解决逗号分隔第一个字母是逗号的问题 */
IF NOT SELF.sum_string IS NULL
THEN
SELF.sum_string := SELF.sum_string || ',' || VALUE;
ELSE
SELF.sum_string := VALUE;
END IF;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT string_sum_obj,
v_next IN string_sum_obj
)
RETURN NUMBER
IS
BEGIN
/* 连接 */
SELF.sum_string := SELF.sum_string || v_next.sum_string;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN string_sum_obj,
return_value OUT VARCHAR2,
v_flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
return_value := SELF.sum_string;
RETURN odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION fun_concat (VALUE VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
USING string_sum_obj;
/
CREATE OR REPLACE TYPE "T_LINK" AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE (ACTX IN OUT T_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
)
CREATE OR REPLACE TYPE BODY T_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE (ACTX IN OUT T_LINK) RETURN NUMBER IS
BEGIN
ACTX := T_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF SELF.STR IS NULL THEN
SELF.STR := VALUE;
ELSE
SELF.STR := SELF.STR || ',' || VALUE;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
CREATE OR REPLACE FUNCTION F_LINK
(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING T_LINK;