Oracle实现多表列拼接去重为单行及解决ORA-01489报错的方法

原代码问题说明 #

• 逻辑错误：未拆分两个表中已用分号拼接的Name字段，直接关联表无法获取单个姓名维度的数据集，本身就得不到预期结果
• 报错原因：listagg 函数返回值默认是varchar2类型，最大长度为4000字节，拼接结果超出长度就会触发ORA-01489错误

方案1：拼接结果长度不超过4000字节，使用修正后的listagg实现 #

先拆分两个表的Name字段为单个姓名行，用union合并自动去重后再拼接，代码如下：

select listagg(name, ';') within group (order by name) as Names
from (
    -- 拆分表1的Name列
    select trim(regexp_substr(a.Name, '[^;]+', 1, level)) as name
    from Table1 a
    connect by regexp_substr(a.Name, '[^;]+', 1, level) is not null
    -- 合并表2拆分结果，union自动去重
    union
    -- 拆分表2的Name列
    select trim(regexp_substr(b.Name, '[^;]+', 1, level)) as name
    from Table2 b
    connect by regexp_substr(b.Name, '[^;]+', 1, level) is not null
);

如果表1/表2存在多行数据，拆分逻辑需要补充条件避免死循环和笛卡尔积，改造后的子查询如下：

select distinct trim(regexp_substr(a.Name, '[^;]+', 1, level)) as name
from Table1 a
connect by regexp_substr(a.Name, '[^;]+', 1, level) is not null
and prior a.id = a.id
and prior sys_guid() is not null

方案2：拼接结果长度超过4000字节，使用xmlagg规避长度限制 #

xmlagg 拼接结果支持clob类型，无4000字节限制，代码如下：

select rtrim(xmlagg(xmlelement(e, name, ';').extract('//text()') order by name).getclobval(), ';') as Names
from (
    select trim(regexp_substr(a.Name, '[^;]+', 1, level)) as name
    from Table1 a
    connect by regexp_substr(a.Name, '[^;]+', 1, level) is not null
    union
    select trim(regexp_substr(b.Name, '[^;]+', 1, level)) as name
    from Table2 b
    connect by regexp_substr(b.Name, '[^;]+', 1, level) is not null
);

代码说明：

• xmlelement 将每个姓名封装为XML节点，extract 提取节点文本内容
• getclobval() 将拼接后的XML结果转换为clob类型，突破varchar2长度限制
• rtrim 去掉末尾多余的分号分隔符

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