自定义集函数GROUP_CONCAT，报错存在集函数

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:DM Database Server 64 V8
【操作系统】：Linux
【CPU】:4核2.5Hz
【问题描述】*：
写了一个自定义类型

CREATE OR REPLACE TYPE "wzjf_zlzf_uat".GroupConcatType AS OBJECT (
	expr VARCHAR2(4000),
	delim VARCHAR2(10),
	--初始化
	STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT GroupConcatType)
		RETURN NUMBER,
	--迭代计算
	MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GroupConcatType,
		value IN VARCHAR) RETURN NUMBER,
	--合并
	MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GroupConcatType, 
		ctx2 IN GroupConcatType) RETURN NUMBER,
	--终止
	MEMBER FUNCTION ODCIAggregateTerminate(self IN GroupConcatType, 
		returnValue OUT VARCHAR, flags IN NUMBER) RETURN NUMBER
);

类型体

CREATE OR REPLACE TYPE BODY "wzjf_zlzf_uat".GroupConcatType IS
	--初始化
	STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT GroupConcatType) 
		RETURN NUMBER IS
    BEGIN
    	print 'ODCIAggregateInitialize';
        sctx := GroupConcatType('', ',');
        RETURN ODCIConst.SUCCESS;
    END;
    --迭代
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GroupConcatType, value IN VARCHAR2) RETURN NUMBER IS
    BEGIN
    	print 'ODCIAggregateIterate';
    	print self.expr;
    	print 'value';
    	print value;
        IF self.expr IS NULL OR self.expr = '' THEN
            self.expr := value;
        ELSE
            self.expr := self.expr || self.delim || value;
        END IF;
        RETURN ODCIConst.SUCCESS;
    END;
 	--合并
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GroupConcatType, ctx2 IN GroupConcatType) RETURN NUMBER IS
    BEGIN
    	print 'ODCIAggregateMerge';
    	print self.expr;
    	print 'ctx2';
    	print ctx2.expr;
        IF self.expr IS NULL OR self.expr = '' THEN
            self.expr := ctx2.expr;
        ELSIF ctx2.expr IS NOT NULL AND ctx2.expr != '' THEN
            self.expr := self.expr || self.delim || ctx2.expr;
        END IF;
        RETURN ODCIConst.SUCCESS;
    END;
 	--终止
    MEMBER FUNCTION ODCIAggregateTerminate(self IN GroupConcatType, 
    	returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
       	print 'ODCIAggregateTerminate';
        returnValue := self.expr;
        RETURN ODCIConst.SUCCESS;
    END;
END;

又写了一个函数

CREATE OR REPLACE FUNCTION "wzjf_zlzf_uat"."GROUP_CONCAT" (
	expr IN VARCHAR2,
	delim IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2
AGGREGATE USING "wzjf_zlzf_uat".GroupConcatType;

这么调用报：存在集函数

SELECT group_key, GROUP_CONCAT(code_name, ',')
FROM "xxx"
where group_key = 'repair_status'
GROUP BY group_key;

这么调用报：非法的参数数据

SELECT group_key, GROUP_CONCAT(code_name)
FROM "xxx"
where group_key = 'repair_status'
GROUP BY group_key;

回答 0

暂无回答

开心就好

只是想在不改变GROUP_CONCAT函数名的前提，实现简单的字符串合并起来，但又不想改变系统函数名。

发布于 2025/01/26 11:16

帅小宝

自定义聚合函数不支持多参数传值，跟oracle一样的（语法限制），需转换下写法，使用复合参数类型。


/*
自定义聚合函数实现：group_concat
*/




create or replace type params is table of varchar2(4000);


CREATE OR REPLACE TYPE GroupConcatType AS OBJECT (
    cat_str varchar2(30000), -- 拼接字串
    delim varchar(50),
     
	--初始化
	STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT GroupConcatType)
		RETURN NUMBER,
	--迭代计算
	MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GroupConcatType,
		value IN params) RETURN NUMBER,
	--合并
	MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GroupConcatType, 
		ctx2 IN GroupConcatType) RETURN NUMBER,
	--终止
	MEMBER FUNCTION ODCIAggregateTerminate(self IN GroupConcatType, 
		returnValue OUT VARCHAR, flags IN NUMBER) RETURN NUMBER
);


CREATE OR REPLACE TYPE BODY GroupConcatType IS
	--初始化
	STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT GroupConcatType) 
		RETURN NUMBER IS
    BEGIN
        sctx := GroupConcatType(null, ',');
        RETURN ODCIConst.SUCCESS;
    END;
    --迭代
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GroupConcatType, value IN params) RETURN NUMBER 
    IS
--      l_delim VARCHAR(50) := ',';
    BEGIN
		IF ( value.COUNT >= 2 ) THEN
		   self.delim := value(2);
		END IF;

        IF self.cat_str IS NULL OR self.cat_str = '' THEN
            self.cat_str := value(1);
        ELSE
            self.cat_str := self.cat_str || self.delim || value(1);
        END IF;
        RETURN ODCIConst.SUCCESS;
    END;
 	--合并
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GroupConcatType, ctx2 IN GroupConcatType) RETURN NUMBER 
    IS
    BEGIN
         self.cat_str := self.cat_str || ctx2.cat_str;
        RETURN ODCIConst.SUCCESS;
    END;
 	--终止
    MEMBER FUNCTION ODCIAggregateTerminate(self IN GroupConcatType, 
    	returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := ltrim(self.cat_str, self.delim);
        RETURN ODCIConst.SUCCESS;
    END;
END;


CREATE OR REPLACE FUNCTION GROUP_CONCAT (
	expr IN params
)
RETURN VARCHAR2
AGGREGATE USING GroupConcatType;



select GROUP_CONCAT(params(c1))
from (
 select 1 c1 union all
 select 2 union all
 select 3 union all
 select 4 union all
 select 5
);



select GROUP_CONCAT(params(c1, '#'))
from (
 select 1 c1 union all
 select 2 union all
 select 3 union all
 select 4 union all
 select 5
);


select a,b, GROUP_CONCAT(params(c, '#'))
from (
 select 1 a, 1 b, 'a' c union all
 select 1 a, 1 b, 'b' union all
 select 1 a, 1 b, 'c' union all
 select 2 a, 2 b, 'd1' union all
 select 2 a, 2 b, 'd2' union all
 select 1 a, 3 b, 'ecc' union all
 select 1 a, 3 b, 'wvu' union all
 select 1 a, 3 b, 'opq' union all
 select 1 a, 3 b, 'xyz' 
)
group by a,b;

如下实现效果：

发布于 2025/01/26 11:23