Oracle中如何为外键为空的表A记录创建表B关联记录并更新外键？

现有表结构及数据 #

表A

表B

预期结果 #

表A

表B

实现方案 #

在Oracle中可以通过PL/SQL块或纯SQL语句完成需求，以下是两种可靠实现方式：

方式一：PL/SQL块（原子操作，推荐）

该方式保证插入表B和更新表A的操作原子性，避免中间状态出错：

DECLARE
    TYPE id_mapping IS RECORD (
        a_id A.ID%TYPE,
        new_b_id B.ID%TYPE
    );
    TYPE mapping_list IS TABLE OF id_mapping;
    v_mappings mapping_list;
BEGIN
    -- 为表A中B_ID为空的记录在表B插入新行，同时记录映射关系
    INSERT INTO B (ID, FIELD)
        SELECT seq_b_id.NEXTVAL, NULL
        FROM A
        WHERE A.B_ID IS NULL
    RETURNING (SELECT ID FROM A WHERE A.B_ID IS NULL), ID BULK COLLECT INTO v_mappings;

    -- 根据映射更新表A的外键
    FORALL i IN 1..v_mappings.COUNT
        UPDATE A
        SET B_ID = v_mappings(i).new_b_id
        WHERE ID = v_mappings(i).a_id;

    COMMIT;
END;
/

注：seq_b_id是表B用于生成ID的序列，需确保该序列当前值不小于表B的最大ID。若未创建序列，可先执行：

CREATE SEQUENCE seq_b_id START WITH (SELECT MAX(ID)+1 FROM B);

方式二：纯SQL实现（Oracle 12c+）

通过临时表存储映射关系，分两步完成操作：

-- 创建临时表存储ID映射
CREATE GLOBAL TEMPORARY TABLE temp_a_b_mapping (
    a_id NUMBER,
    new_b_id NUMBER
) ON COMMIT DELETE ROWS;

-- 插入表B并记录映射
INSERT INTO B (ID, FIELD)
    SELECT seq_b_id.NEXTVAL, NULL
    FROM A
    WHERE A.B_ID IS NULL
RETURNING (SELECT ID FROM A WHERE A.B_ID IS NULL), ID INTO temp_a_b_mapping;

-- 更新表A的外键
UPDATE A
SET B_ID = (SELECT new_b_id FROM temp_a_b_mapping WHERE temp_a_b_mapping.a_id = A.ID)
WHERE A.B_ID IS NULL;

COMMIT;

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