能否通过单次PL/SQL操作完成CNTRCT_NTFYUSER表的指定行插入？

当前CNTRCT_NTFYUSER表数据 #

插入需求 #

• 为所有ExistingUser#插入NOTIFY_TYPE为'E'和'R'的2行数据；
• 为每个CNTRCT_ID分别插入OPRID为'NewUser1'和'NewUser2'、NOTIFY_TYPE为'E'和'R'的2行数据。

目标表数据 #

解决方案：一次PL/SQL插入操作实现 #

可以通过单条INSERT语句结合子查询和集合生成完成需求，无需多次执行：

INSERT INTO CNTRCT_NTFYUSER (CNTRCT_ID, VERSION_NBR, NOTIFY_TYPE, OPRID)
-- 为现有用户添加E/R类型行
SELECT 
    c.CNTRCT_ID, 
    c.VERSION_NBR, 
    nt.NOTIFY_TYPE, 
    c.OPRID
FROM CNTRCT_NTFYUSER c
CROSS JOIN (
    SELECT 'E' AS NOTIFY_TYPE FROM DUAL
    UNION ALL
    SELECT 'R' AS NOTIFY_TYPE FROM DUAL
) nt
WHERE c.NOTIFY_TYPE = 'A'
UNION ALL
-- 为每个合同添加NewUser1/NewUser2的E/R类型行
SELECT 
    c.CNTRCT_ID, 
    c.VERSION_NBR, 
    nt.NOTIFY_TYPE, 
    nu.OPRID
FROM (
    SELECT DISTINCT CNTRCT_ID, VERSION_NBR 
    FROM CNTRCT_NTFYUSER
) c
CROSS JOIN (
    SELECT 'E' AS NOTIFY_TYPE FROM DUAL
    UNION ALL
    SELECT 'R' AS NOTIFY_TYPE FROM DUAL
) nt
CROSS JOIN (
    SELECT 'NewUser1' AS OPRID FROM DUAL
    UNION ALL
    SELECT 'NewUser2' AS OPRID FROM DUAL
) nu;

COMMIT;

逻辑说明 #

1. 第一部分：通过CROSS JOIN将原表中每个A类型行对应的用户，与'E'、'R'类型拼接，生成对应插入行；
2. 第二部分：先提取所有唯一的合同ID和版本号，再与'E'/'R'类型、'NewUser1'/'NewUser2'交叉连接，生成每个合同的新用户行；
3. 整个操作通过单条INSERT语句完成，一次执行即可满足所有插入要求。

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