如何在PLSQL中按规则拆分逗号分隔字符串为多行结构化结果

纯SQL查询实现 #

无需编写存储过程，直接通过递归CTE + 正则函数即可查询得到结构化结果：

WITH input_source AS (
    -- 此处可替换为实际的字符串来源，也可改为绑定变量
    SELECT 'M1: TestString1, M2: TestString2, M3: TestString3, M4: TestString4, M5: TestString5, M6: TestString6, M7: TestString7' AS raw_str
    FROM dual
),
split_kv_pairs AS (
    -- 按逗号拆分得到每一组独立的键值对
    SELECT
        LEVEL AS seq,
        REGEXP_SUBSTR(raw_str, '[^,]+', 1, LEVEL) AS kv_item
    FROM input_source
    CONNECT BY LEVEL <= REGEXP_COUNT(raw_str, ',') + 1
)
-- 按冒号拆分键值对得到两列结果
SELECT
    TRIM(REGEXP_SUBSTR(kv_item, '[^:]+', 1, 1)) AS code,
    TRIM(REGEXP_SUBSTR(kv_item, '[^:]+', 1, 2)) AS String
FROM split_kv_pairs;

PLSQL块遍历输出实现 #

如果需要在PLSQL逻辑中处理结果，可参考如下匿名块写法：

DECLARE
    v_raw_str VARCHAR2(1000) := 'M1: TestString1, M2: TestString2, M3: TestString3, M4: TestString4, M5: TestString5, M6: TestString6, M7: TestString7';
BEGIN
    -- 遍历查询结果输出
    FOR res IN (
        WITH split_kv_pairs AS (
            SELECT
                LEVEL AS seq,
                REGEXP_SUBSTR(v_raw_str, '[^,]+', 1, LEVEL) AS kv_item
            FROM dual
            CONNECT BY LEVEL <= REGEXP_COUNT(v_raw_str, ',') + 1
        )
        SELECT
            TRIM(REGEXP_SUBSTR(kv_item, '[^:]+', 1, 1)) AS code,
            TRIM(SUBSTR(kv_item, INSTR(kv_item, ':') + 1)) AS String
        FROM split_kv_pairs
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('code: ' || res.code || ' | String: ' || res.String);
    END LOOP;
END;
/

注意：上述PLSQL块中拆分String列使用了SUBSTR + INSTR的写法，适配String值本身包含冒号的场景，比纯正则写法兼容性更强。

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