行列转换之字符串拼接（三）、自定义聚合函数

字符串拼接（String Aggregation Techniques）是数据处理时经常需要用到一个技术，比如需要按时间顺序拼装一个快递的运输记录，或者将流程中各个环节的处理人拼装为一个字符串。

Oracle中有多种方法来实现这个功能，这里罗列几种，详细用法可以参考下面的文章：

• WM_CONCAT函数
• LISTAGG函数
• 自定义聚合函数

本文介绍第三种：自定义聚合函数。由于WM_CONCAT和LISTAGG有各自的缺陷，有时候无法满足实际需求，此时可以自己创造一个函数。

显然已经有很多人碰到了这样的问题，TOM大神写过一个string_agg函数用逗号分隔返回Varchar类型，根据他的函数我改写了一个clob_agg，返回的是clob类型

CLOB_AGG函数实现

CREATE OR REPLACE TYPE t_clob_agg AS OBJECT
(
  g_CLOB  clob,
  delimer   varchar2(1),
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_clob_agg)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_clob_agg,
                                       value  IN      clob )
     RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg,
                                         returnValue  OUT  clob,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_clob_agg,
                                     ctx2  IN      t_clob_agg)
    RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_clob_agg IS
--  delimer varchar2(1):='#';
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_clob_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_clob_agg(NULL,'#');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_clob_agg,
                                       value  IN      clob )
    RETURN NUMBER IS
  BEGIN
    SELF.g_CLOB := self.g_CLOB ||delimer || value;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg,
                                         returnValue  OUT  CLOB,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
    LEN_DELIMER NUMBER;
    N_AMOUNT    NUMBER;
    N_LOC       NUMBER;
  BEGIN
    LEN_DELIMER:=LENGTH(delimer);
    IF DBMS_LOB.substr(SELF.g_CLOB,LEN_DELIMER,1)=delimer THEN
      N_AMOUNT:=DBMS_LOB.getlength(SELF.g_CLOB)-LEN_DELIMER;
      N_LOC:=LEN_DELIMER+1;
    END IF;
    IF DBMS_LOB.substr(SELF.g_CLOB,LEN_DELIMER,DBMS_LOB.getlength(SELF.g_CLOB)-LEN_DELIMER+1)=delimer THEN
      N_AMOUNT:=N_AMOUNT-LEN_DELIMER;
    END IF;
    returnValue := DBMS_LOB.substr(SELF.g_CLOB,N_AMOUNT,N_LOC);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_clob_agg,
                                     ctx2  IN      t_clob_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_CLOB := SELF.g_CLOB || delimer || ctx2.g_CLOB;
    RETURN ODCIConst.Success;
  END;
END;
/

CREATE OR REPLACE FUNCTION clob_agg (p_input CLOB)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING t_clob_agg;
/

用法和问题

这个函数实际上跟wm_concat的功能几乎相同，只是分隔符我换成了”#”，可以看到：
1.自定义函数CLOB_AGG支持distinct和排序

2.由于自定义聚合函数只能定义1个参数，因此，该函数没法像listagg一样将分隔符作为函数参数输入。
作为替代方法，在上面的代码中，我定义了一个delimer参数，并初始化为#字符，如果需要用其他字符分隔，需要修改下面第2行代码后重新编译函数或定义另一个函数。

delimer varchar2(1),
sctx := t_clob_agg(NULL,’#’);

有网友提出可以通过varray(2)数组将分隔符一起传入，下次可以自己试试。