如何在Oracle中抽象重复使用的SQL实现逻辑？

我有如下查询语句：

SELECT SUBSTR(replace(JSON_ARRAYAGG(colm returning clob),'"',''),2,length(replace(JSON_ARRAYAGG(colm returning clob),'"',''))-2)
from
(select 'a' as colm from dual
union select 'b' as colm from dual
);

其中的核心计算逻辑：

SUBSTR(replace(JSON_ARRAYAGG(colm returning clob),'"',''),2,length(replace(JSON_ARRAYAGG(colm returning clob),'"',''))-2)

该逻辑需要在多处业务处理中重复使用，我希望隐藏实现细节，仅通过传入列名即可调用该逻辑。已知可创建用户自定义ODCI类型，想咨询是否可以通过函数等方式实现该逻辑的抽象，以简化重复代码编写。

当然可以通过自定义聚合函数实现这个逻辑的抽象——你的核心逻辑本质是将多行字符串聚合为逗号分隔的字符串，基于ODCI类型创建自定义聚合函数是最优方案，既隐藏实现细节，又能直接传入列名调用，还比原逻辑更高效（避免了重复调用JSON_ARRAYAGG和多次字符串替换）。具体实现步骤如下：

1. 创建ODCI聚合类型 #

首先定义用于聚合计算的类型规范和类型体：

-- 类型规范
CREATE OR REPLACE TYPE str_list_agg_type AS OBJECT
(
  result CLOB,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT str_list_agg_type) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT str_list_agg_type, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN str_list_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT str_list_agg_type, ctx2 IN str_list_agg_type) RETURN NUMBER
);
/

-- 类型体
CREATE OR REPLACE TYPE BODY str_list_agg_type IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT str_list_agg_type) RETURN NUMBER IS
  BEGIN
    sctx := str_list_agg_type(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT str_list_agg_type, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF self.result IS NULL THEN
      self.result := value;
    ELSE
      self.result := self.result || ',' || value;
    END IF;
    RETURN ODCIConst.Success;
  END;

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

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT str_list_agg_type, ctx2 IN str_list_agg_type) RETURN NUMBER IS
  BEGIN
    IF self.result IS NULL THEN
      self.result := ctx2.result;
    ELSIF ctx2.result IS NOT NULL THEN
      self.result := self.result || ',' || ctx2.result;
    END IF;
    RETURN ODCIConst.Success;
  END;
END;
/

2. 创建自定义聚合函数 #

基于上述ODCI类型创建可以直接调用的聚合函数：

CREATE OR REPLACE FUNCTION str_list_agg(p_col VARCHAR2) RETURN CLOB
AGGREGATE USING str_list_agg_type;
/

3. 调用自定义函数 #

现在你只需传入列名即可实现原逻辑的效果，代码大幅简化：

SELECT str_list_agg(colm)
FROM (
  SELECT 'a' AS colm FROM dual
  UNION SELECT 'b' AS colm FROM dual
);

该查询会直接返回a,b，和原逻辑的输出完全一致，同时性能更优。

补充说明 #

如果必须基于JSON_ARRAYAGG的原有逻辑封装（比如需要兼容特定场景），可以创建一个包装函数，但注意JSON_ARRAYAGG是聚合函数，无法在普通单行函数中直接调用，因此自定义聚合函数仍是最佳选择。此外，上述自定义函数支持CLOB类型拼接，适合处理大数据量的字符串聚合场景。

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