Oracle中INSERT子句能否使用CASE？如何批量插入关联数据并生成排序号

问题原因说明 #

1. INSERT的VALUES子句仅适合插入单条固定数据，你需要插入的是new_details和lookup关联后的多条数据集，应该使用INSERT INTO ... SELECT语法
2. CASE表达式可以在INSERT关联的查询逻辑中正常使用，完全满足你的赋值需求
3. 每个e_id独立的1-5递增序号，可以用Oracle窗口函数ROW_NUMBER()实现

正确实现代码 #

INSERT INTO limestone (limestone_id, e_id, ref_type_id, limestone_prg_id, sort_order)
SELECT 
    limestone_sq.nextval,
    nd.e_id,
    l.ref_typ_id,
    CASE l.ref_typ_id
        WHEN 33 THEN 3
        WHEN 34 THEN 3
        WHEN 35 THEN 3
        WHEN 36 THEN 2
        WHEN 37 THEN 1
    END AS limestone_prg_id,
    ROW_NUMBER() OVER(PARTITION BY nd.e_id ORDER BY l.ref_typ_id ASC) AS sort_order
FROM new_details nd
CROSS JOIN lookup l
WHERE l.ref_typ = 'status';

逻辑说明 #

• 用CROSS JOIN实现new_details的每个e_id和lookup的5条status记录全匹配，最终生成2*5=10条待插入数据，符合业务要求
• ROW_NUMBER() OVER(PARTITION BY nd.e_id ORDER BY l.ref_typ_id ASC)：按e_id分组，每组内按ref_typ_id从小到大排序，生成1到5的独立序号，就是你需要的sort_order
• CASE表达式直接在SELECT字段中对ref_typ_id判断，赋值对应的limestone_prg_id
• 序列limestone_sq.nextval会为每条插入记录生成唯一主键

插入完成后可执行SELECT * FROM limestone ORDER BY e_id, sort_order;验证结果，和你预期的输出一致。

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