解决Oracle高版本 ORA-00904: “WM_CONCAT“: 标识符无效

若使用wm_concat函数较少，可用listagg替代

SELECT listagg(name_, ',') WITHIN GROUP (ORDER BY name_) AS name_list FROM test_data;

在19c中手动创建wm_concat函数

1. 创建一个类型来存储中间结果
首先，需要创建一个对象类型和一个与之关联的集合类型，用于在聚合过程中存储中间结果。

-- 创建一个对象类型，用于存储字符串
CREATE OR REPLACE TYPE str_agg_type AS OBJECT
(
    -- 用于存储聚合的字符串
    str VARCHAR2(32767),
    -- 构造函数，初始化字符串为空
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT str_agg_type) RETURN NUMBER,
    -- 处理每一行数据，将其添加到聚合字符串中
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT str_agg_type, value IN VARCHAR2) RETURN NUMBER,
    -- 合并两个部分聚合结果
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT str_agg_type, ctx2 IN str_agg_type) RETURN NUMBER,
    -- 完成聚合操作，返回最终结果
    MEMBER FUNCTION ODCIAggregateTerminate(self IN str_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
);
/

-- 实现对象类型的方法
CREATE OR REPLACE TYPE BODY str_agg_type IS
    -- 初始化聚合上下文
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT str_agg_type) RETURN NUMBER IS
    BEGIN
        sctx := str_agg_type(NULL);
        RETURN ODCIConst.Success;
    END;

    -- 迭代处理每一行数据
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT str_agg_type, value IN VARCHAR2) RETURN NUMBER IS
    BEGIN
        IF self.str IS NOT NULL THEN
            self.str := self.str || ',' || value;
        ELSE
            self.str := value;
        END IF;
        RETURN ODCIConst.Success;
    END;

    -- 合并两个部分聚合结果
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT str_agg_type, ctx2 IN str_agg_type) RETURN NUMBER IS
    BEGIN
        IF ctx2.str IS NOT NULL THEN
            IF self.str IS NOT NULL THEN
                self.str := self.str || ',' || ctx2.str;
            ELSE
                self.str := ctx2.str;
            END IF;
        END IF;
        RETURN ODCIConst.Success;
    END;

    -- 完成聚合操作，返回最终结果
    MEMBER FUNCTION ODCIAggregateTerminate(self IN str_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := self.str;
        RETURN ODCIConst.Success;
    END;
END;
/

2. 创建自定义聚合函数

-- 创建自定义聚合函数
CREATE OR REPLACE FUNCTION wm_concat(input VARCHAR2) RETURN VARCHAR2
    PARALLEL_ENABLE AGGREGATE USING str_agg_type;
/

-- 创建一个测试表
CREATE TABLE test_table (
    id NUMBER,
    name VARCHAR2(50)
);

-- 插入一些测试数据
INSERT INTO test_table VALUES (1, 'Alice');
INSERT INTO test_table VALUES (1, 'Bob');
INSERT INTO test_table VALUES (2, 'Charlie');
INSERT INTO test_table VALUES (2, 'David');

-- 提交事务
COMMIT;

-- 使用自定义的 wm_concat 函数进行测试
SELECT id, wm_concat(name)
FROM test_table
GROUP BY id;

注意：本文归作者所有，未经作者允许，不得转载