INSERT查询中使用表主键作为其他表外键的实现方法

以下方案适配你的场景：从源表 TableX 抽取数据写入范式拆分后的三张表，通过序列生成新主键，同时维护三张表的外键关联关系，按实现复杂度和适配数据量排序如下：

方案1：PL/SQL 批量处理 + RETURNING 子句（适配十万级以内数据量，逻辑清晰原子性强） #

利用 Oracle 插入语句的 RETURNING 子句直接返回刚生成的序列主键，配合批量集合操作完成关联插入，全程事务原子性，要么全成功要么全回滚。

DECLARE
    -- 定义存储源表全字段+A表生成主键的记录类型
    TYPE src_rec IS RECORD (
        a_id      TABLE_A.COL_A%TYPE,
        a_colb    TABLE_A.COL_B%TYPE,
        a_colc    TABLE_A.COL_C%TYPE,
        a_cold    TABLE_A.COL_D%TYPE,
        y_col1    Table_Y.COL1%TYPE, -- 替换为Y表实际需要的字段
        y_col2    Table_Y.COL2%TYPE,
        z_col1    TABLE_Z.COL1%TYPE  -- 替换为Z表实际需要的字段
    );
    TYPE src_tab IS TABLE OF src_rec INDEX BY PLS_INTEGER;
    l_src_data src_tab;

    -- 定义存储Y表生成主键+关联字段的记录类型
    TYPE y_rec IS RECORD (
        y_id      Table_Y.PK_COL%TYPE, -- 替换为Y表主键字段名
        a_id      Table_Y.A_ID%TYPE,   -- 替换为Y表关联A表的外键字段名
        z_col1    TABLE_Z.COL1%TYPE
    );
    TYPE y_tab IS TABLE OF y_rec INDEX BY PLS_INTEGER;
    l_y_data y_tab;
BEGIN
    -- 第一步：从源表拉取全量需要字段，预生成A表主键
    SELECT 
        TABLE_A.SQ.nextval, 
        x.colb, x.colc, x.cold,
        x.y_col1, x.y_col2, x.z_col1
    BULK COLLECT INTO l_src_data
    FROM TableX x;

    -- 批量插入A表
    FORALL i IN 1..l_src_data.COUNT
        INSERT INTO TABLE_A(COL_A, COL_B, COL_C, COL_D)
        VALUES (l_src_data(i).a_id, l_src_data(i).a_colb, l_src_data(i).a_colc, l_src_data(i).a_cold);

    -- 批量插入Y表，返回生成的Y主键和关联Z需要的字段
    FORALL i IN 1..l_src_data.COUNT
        INSERT INTO Table_Y(A_ID, COL1, COL2)
        VALUES (l_src_data(i).a_id, l_src_data(i).y_col1, l_src_data(i).y_col2)
        RETURNING PK_COL, A_ID, l_src_data(i).z_col1 BULK COLLECT INTO l_y_data;

    -- 批量插入Z表
    FORALL i IN 1..l_y_data.COUNT
        INSERT INTO TABLE_Z(Y_ID, COL1)
        VALUES (l_y_data(i).y_id, l_y_data(i).z_col1);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

方案2：全局临时表存储关联映射（适配百万级以上大数据量，内存占用低） #

数据量过大时 PL/SQL 集合会占用过高内存，用全局临时表存储中间主键映射关系，性能更稳定。

• 第一步：创建临时表存储A表主键和源表关联字段

CREATE GLOBAL TEMPORARY TABLE TMP_A_MAPPING (
    A_ID    NUMBER,
    Y_COL1  VARCHAR2(100), -- 替换为实际字段类型
    Y_COL2  VARCHAR2(100),
    Z_COL1  VARCHAR2(100)
) ON COMMIT DELETE ROWS;

• 第二步：预生成A表主键存入临时表

INSERT INTO TMP_A_MAPPING(A_ID, Y_COL1, Y_COL2, Z_COL1)
SELECT TABLE_A.SQ.nextval, x.Y_COL1, x.Y_COL2, x.Z_COL1
FROM TableX x;

• 第三步：插入A表

INSERT INTO TABLE_A(COL_A, COL_B, COL_C, COL_D)
SELECT t.A_ID, x.COL_B, x.COL_C, x.COL_D
FROM TMP_A_MAPPING t
JOIN TableX x ON 【替换为源表和临时表的关联条件，建议直接把A表需要的字段也存入临时表省略关联】;

• 第四步：创建Y表映射临时表，插入Y表

CREATE GLOBAL TEMPORARY TABLE TMP_Y_MAPPING (
    Y_ID    NUMBER,
    A_ID    NUMBER,
    Z_COL1  VARCHAR2(100)
) ON COMMIT DELETE ROWS;

INSERT INTO Table_Y(A_ID, COL1, COL2)
SELECT t.A_ID, t.Y_COL1, t.Y_COL2
FROM TMP_A_MAPPING t
RETURNING PK_COL, A_ID, Z_COL1 BULK COLLECT INTO TMP_Y_MAPPING;

• 第五步：插入Z表

INSERT INTO TABLE_Z(Y_ID, COL1)
SELECT t.Y_ID, t.Z_COL1
FROM TMP_Y_MAPPING t;

COMMIT;

方案3：业务键关联（适配有全局唯一业务键的场景，纯SQL实现逻辑最简单） #

如果源表 TableX 存在全局唯一的业务键（比如订单号、统一社会信用代码等无重复字段），不需要返回主键，直接用业务键做关联映射即可。

• 第一步：给A表加临时业务键字段，插入A表时存入源表业务键

ALTER TABLE TABLE_A ADD SRC_BIZ_KEY VARCHAR2(100); -- 插入完成后可删除该字段

INSERT INTO TABLE_A(COL_A, COL_B, COL_C, COL_D, SRC_BIZ_KEY)
SELECT TABLE_A.SQ.nextval, x.colb, x.colc, x.cold, x.biz_key
FROM TableX x;

• 第二步：通过业务键关联A表主键插入Y表，同时存储业务键

ALTER TABLE Table_Y ADD SRC_BIZ_KEY VARCHAR2(100);

INSERT INTO Table_Y(A_ID, COL1, COL2, SRC_BIZ_KEY)
SELECT a.COL_A, x.y_col1, x.y_col2, x.biz_key
FROM TableX x
JOIN TABLE_A a ON x.biz_key = a.SRC_BIZ_KEY;

• 第三步：通过业务键关联Y表主键插入Z表

INSERT INTO TABLE_Z(Y_ID, COL1)
SELECT y.PK_COL, x.z_col1
FROM TableX x
JOIN Table_Y y ON x.biz_key = y.SRC_BIZ_KEY;

COMMIT;

通用注意事项 #

• 批量插入前建议调大序列缓存，比如执行 ALTER SEQUENCE TABLE_A.SQ CACHE 1000;，性能提升非常明显
• 所有操作务必放在同一个事务中执行，避免部分插入成功导致数据不一致
• Oracle 12c 及以上版本可以直接用 IDENTITY 自增主键代替序列，语法更简洁

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