如何在Oracle PL/SQL中将集合拆分值添加到另一集合

需求说明 #

现有TABLE OF VARCHAR2类型集合col1，存储内容如下：

aaa
bbb,ccc
ddd,eee;fff
ggg|hhh

需将其中所有值按,、;、|三种分隔符拆分，将单个值存入另一同类型集合col2，最终col2内容为：

aaa
bbb
ccc
ddd
eee
fff
ggg
hhh

PL/SQL实现方案 #

1. 定义集合类型（若未预先定义） #

CREATE OR REPLACE TYPE str_table IS TABLE OF VARCHAR2(100);
/

2. 拆分逻辑代码 #

DECLARE
    col1 str_table := str_table('aaa', 'bbb,ccc', 'ddd,eee;fff', 'ggg|hhh');
    col2 str_table := str_table();
BEGIN
    -- 遍历col1中每个元素
    FOR idx IN col1.FIRST .. col1.LAST LOOP
        -- 通过正则表达式拆分当前元素，遍历所有拆分结果
        FOR split_rec IN (
            SELECT REGEXP_SUBSTR(col1(idx), '[^,;|]+', 1, LEVEL) AS single_val
            FROM DUAL
            CONNECT BY REGEXP_SUBSTR(col1(idx), '[^,;|]+', 1, LEVEL) IS NOT NULL
        ) LOOP
            col2.EXTEND;
            col2(col2.LAST) := split_rec.single_val;
        END LOOP;
    END LOOP;

    -- 可选：输出col2内容验证结果
    FOR res_idx IN col2.FIRST .. col2.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(col2(res_idx));
    END LOOP;
END;
/

关键逻辑说明 #

• 正则表达式：[^,;|]+匹配任意不包含,、;、|的连续字符，实现多分隔符拆分。
• CONNECT BY层级查询：通过LEVEL变量遍历当前字符串的所有拆分项，直到没有匹配结果为止。
• 集合操作：使用EXTEND扩展col2长度，将拆分后的单个值依次存入。

SQL层面实现（集合转表拆分） #

若需在SQL中直接处理集合，可将集合转换为表后拆分：

SELECT REGEXP_SUBSTR(t.column_value, '[^,;|]+', 1, LEVEL) AS single_val
FROM TABLE(col1) t
CONNECT BY REGEXP_SUBSTR(t.column_value, '[^,;|]+', 1, LEVEL) IS NOT NULL
  AND PRIOR t.column_value = t.column_value
  AND PRIOR SYS_GUID() IS NOT NULL;

• PRIOR关键字用于避免层级查询产生笛卡尔积，确保每个原始字符串独立拆分
• TABLE(col1)将集合转换为关系表结构，便于SQL处理

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