PL/SQL查询需求：拆分州编码字段并匹配州名称输出

现有输入表数据如下：

存在一张州信息对照表state_lookup，包含字段Code（州编码）和Name（州名称），例如AL | Alabama、IL | Illinois等。需要编写PL/SQL查询语句，将输入表中的逗号分隔州编码替换为对应的州名称，输出如下格式结果：

假设输入表名为person_states，字段分别为state_codes（逗号分隔的州编码）、first_name（名字）、last_name（姓氏）。可以通过拆分编码字符串→关联州表→聚合名称的思路实现，具体SQL语句如下：

SELECT
    LISTAGG(sl.name, ', ') WITHIN GROUP (ORDER BY sl.name) AS "州名称",
    ps.first_name AS "名字",
    ps.last_name AS "姓氏"
FROM
    person_states ps,
    -- 拆分逗号分隔的州编码为多行
    TABLE(
        CAST(
            MULTISET(
                SELECT REGEXP_SUBSTR(ps.state_codes, '[^,]+', 1, LEVEL)
                FROM DUAL
                CONNECT BY REGEXP_SUBSTR(ps.state_codes, '[^,]+', 1, LEVEL) IS NOT NULL
            ) AS SYS.ODCIVARCHAR2LIST
        )
    ) sc
-- 关联州对照表匹配名称
JOIN state_lookup sl 
    ON UPPER(sl.code) = UPPER(sc.COLUMN_VALUE) -- 忽略编码大小写差异
GROUP BY ps.first_name, ps.last_name, ps.state_codes
ORDER BY ps.first_name;

代码说明： #

1. 拆分编码：使用REGEXP_SUBSTR结合CONNECT BY循环，将单行的逗号分隔编码拆分为多行记录，通过MULTISET和SYS.ODCIVARCHAR2LIST转换为可关联的表类型。
2. 关联州表：将拆分后的编码与state_lookup表关联，获取对应的州名称，用UPPER统一大小写避免匹配失败。
3. 聚合名称：使用LISTAGG函数将多行州名称重新聚合为逗号分隔的字符串，按名称排序保证输出顺序一致。
4. 分组输出：按原始表的名字、姓氏分组，确保每条原始记录对应一条结果。

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