Oracle拆分存储过程逗号分隔字符串参数插入表生成组合数据问题

AI 小北

2026-8-28

实现方案

你需要的是两个拆分后数据集的笛卡尔积（全量组合），只需要参照你拆分P_param3的逻辑先把P_Paramj2拆分为CatID集合，再将两个拆分结果交叉关联，拼接固定的P_Param1值即可。

可直接复用的存储过程内SQL

-- 插入业务表时直接用这段，替换实际业务表名即可
INSERT INTO 你的业务表名(proID, CatID, CatName)
SELECT
    P_Param1 AS proID,
    TO_NUMBER(t2.cat_id) AS CatID, -- 若CatID为字符串类型可去掉TO_NUMBER转换
    t3.cat_name AS CatName
FROM
    -- 拆分P_Paramj2提取CatID值
    (
        SELECT
            regexp_substr(P_Paramj2, '(val:)?(.*?)(,val:|$)', 1, level, NULL, 2) AS cat_id
        FROM dual
        CONNECT BY level <= regexp_count(P_Paramj2, 'val:')
    ) t2,
    -- 拆分P_param3提取CatName值（复用你已写好的拆分逻辑）
    (
        SELECT
            regexp_substr(P_param3, '(Name:)?(.*?)(,Name:|$)', 1, level, NULL, 2) AS cat_name
        FROM dual
        CONNECT BY level <= regexp_count(P_param3, 'Name:')
    ) t3;

常量测试版本

直接执行即可得到你给出的示例结果：

SELECT
    12 AS proID,
    t2.cat_id AS CatID,
    t3.cat_name AS CatName
FROM
    (
        SELECT
            regexp_substr('val:15,val:16', '(val:)?(.*?)(,val:|$)', 1, level, NULL, 2) AS cat_id
        FROM dual
        CONNECT BY level <= regexp_count('val:15,val:16', 'val:')
    ) t2,
    (
        SELECT
            regexp_substr('Name:check values,Name:bv,Name:cv', '(Name:)?(.*?)(,Name:|$)', 1, level, NULL, 2) AS cat_name
        FROM dual
        CONNECT BY level <= regexp_count('Name:check values,Name:bv,Name:cv', 'Name:')
    ) t3;