Oracle 12.1不使用XMLAGG/LISTAGG实现列值转逗号分隔CLOB方案咨询

以下两种方案可匹配你的需求，可根据实际场景选择：

方案1：分组取前N条拼接（截断多余内容加...标识） #

该方案性能最优，完全规避长字符串拼接的溢出和性能问题，逻辑为仅取每个分组前N条值拼接，超出部分用...标识，因拼接内容长度可控，不会触发LISTAGG的4000字符限制：

SELECT 
  id,
  TO_CLOB(
    LISTAGG(name, ',') WITHIN GROUP (ORDER BY name)
    || CASE WHEN total_cnt > 5 THEN ',...' ELSE '' END
  ) AS concatenated_names
FROM (
  SELECT 
    id,
    name,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) AS rn,
    COUNT(*) OVER (PARTITION BY id) AS total_cnt
  FROM your_table -- 替换为实际表名
) t
WHERE rn <= 5 -- 可调整为你需要的截断行数
GROUP BY id, total_cnt;

可自行调整ORDER BY规则自定义取数优先级，修改数值5即可调整截断阈值。

方案2：自定义聚合函数实现无长度限制CLOB拼接 #

该方案完全不依赖LISTAGG、XMLAGG，底层直接操作CLOB类型，无长度限制，内存开销远低于XMLAGG方案，不会触发PGA溢出报错：

步骤1：创建聚合用对象类型 #

CREATE OR REPLACE TYPE clob_concat_typ AS OBJECT (
  result_clob CLOB,
  separator VARCHAR2(10),
  
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_concat_typ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_concat_typ, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_concat_typ, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_concat_typ, ctx2 IN clob_concat_typ) RETURN NUMBER
);
/

步骤2：实现对象主体逻辑 #

CREATE OR REPLACE TYPE BODY clob_concat_typ IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_concat_typ) RETURN NUMBER IS
  BEGIN
    sctx := clob_concat_typ(EMPTY_CLOB(), ',');
    DBMS_LOB.CREATETEMPORARY(sctx.result_clob, TRUE);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_concat_typ, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF DBMS_LOB.GETLENGTH(self.result_clob) > 0 THEN
      DBMS_LOB.WRITEAPPEND(self.result_clob, LENGTH(self.separator), self.separator);
    END IF;
    DBMS_LOB.WRITEAPPEND(self.result_clob, LENGTH(value), value);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_concat_typ, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := self.result_clob;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_concat_typ, ctx2 IN clob_concat_typ) RETURN NUMBER IS
  BEGIN
    IF DBMS_LOB.GETLENGTH(ctx2.result_clob) > 0 THEN
      IF DBMS_LOB.GETLENGTH(self.result_clob) > 0 THEN
        DBMS_LOB.WRITEAPPEND(self.result_clob, LENGTH(self.separator), self.separator);
      END IF;
      DBMS_LOB.WRITEAPPEND(self.result_clob, DBMS_LOB.GETLENGTH(ctx2.result_clob), ctx2.result_clob);
    END IF;
    RETURN ODCIConst.Success;
  END;
END;
/

步骤3：创建自定义聚合函数 #

CREATE OR REPLACE FUNCTION clob_concat(input VARCHAR2) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING clob_concat_typ;
/

步骤4：调用函数实现分组拼接 #

SELECT 
  id,
  clob_concat(name ORDER BY name) AS concatenated_names
FROM your_table -- 替换为实际表名
GROUP BY id;

内容的提问来源于stack exchange，提问作者sameer59