Oracle SQL：从父表拆分数据批量插入子表并保留外键

需求概述 #

从Fruit父表读取数据，将每行数据拆分为两行插入Fruit_Sub子表：

• 固定插入3个苹果的记录
• 剩余数量作为香蕉的记录
• 保留fruit_id作为外键关联

Fruit表结构 #

fruit_id, num_fruit

Fruit表示例数据

fruit_id    num_fruit
----------------------
   1            5
   2            9
   3            4

Fruit_Sub表结构 #

Fruit_Sub_id, Fruit_id_FK, fruit_name, num_sub_fruit

期望插入Fruit_Sub的结果

fruit_sub_id    fruit_id_fk   fruit_name    num_sub_fruit
-----------------------------------------------------
   1                1           apples      3
   2                1           bananas     2
   3                2           apples      3
   4                2           bananas     6
   5                3           apples      3
   6                3           bananas     1

解决方案 #

方法1：使用UNION ALL拆分每行数据 #

这是最直观的实现方式，将父表每行数据拆分为苹果、香蕉两条记录后合并插入：

INSERT INTO Fruit_Sub (Fruit_Sub_id, Fruit_id_FK, fruit_name, num_sub_fruit)
SELECT
    -- 用序列生成自增主键，需提前创建序列：CREATE SEQUENCE Fruit_Sub_seq START WITH 1 INCREMENT BY 1;
    Fruit_Sub_seq.NEXTVAL,
    f.fruit_id,
    s.fruit_name,
    CASE s.fruit_name
        WHEN 'apples' THEN 3
        WHEN 'bananas' THEN f.num_fruit - 3
    END AS num_sub_fruit
FROM Fruit f
CROSS JOIN (
    SELECT 'apples' AS fruit_name FROM DUAL
    UNION ALL
    SELECT 'bananas' AS fruit_name FROM DUAL
) s;

说明

• 通过CROSS JOIN将父表每行与固定的两个水果名称行关联，生成两行拆分数据
• 用CASE语句分别计算苹果（固定3）和香蕉（总数量减3）的数量
• 主键Fruit_Sub_id使用序列生成，这是生产环境的最优选择，避免并发插入时的主键冲突

方法2：使用CONNECT BY生成多行 #

如果未来需要扩展更多水果类型，这种方式更灵活：

INSERT INTO Fruit_Sub (Fruit_Sub_id, Fruit_id_FK, fruit_name, num_sub_fruit)
SELECT
    Fruit_Sub_seq.NEXTVAL,
    f.fruit_id,
    CASE LEVEL
        WHEN 1 THEN 'apples'
        WHEN 2 THEN 'bananas'
    END AS fruit_name,
    CASE LEVEL
        WHEN 1 THEN 3
        WHEN 2 THEN f.num_fruit - 3
    END AS num_sub_fruit
FROM Fruit f
CONNECT BY LEVEL <= 2
    AND PRIOR fruit_id = fruit_id
    AND PRIOR SYS_GUID() IS NOT NULL; -- 防止递归关联导致的数据重复

说明

• CONNECT BY LEVEL <=2为父表每行生成两条记录
• PRIOR SYS_GUID() IS NOT NULL用于避免Oracle将父表行进行递归关联，确保每行仅生成固定的两行拆分数据

注意事项 #

• 若num_fruit可能小于3，可将香蕉数量处理为非负数：GREATEST(f.num_fruit -3, 0)，或添加过滤条件WHERE f.num_fruit >=3避免无效数据
• 若使用Oracle 12c及以上版本，也可将Fruit_Sub_id设为IDENTITY列（Fruit_Sub_id NUMBER GENERATED ALWAYS AS IDENTITY），无需手动维护序列

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