您需要为这样的需求获取程序.此解决方案使用 SELECT ... FOR UPDATE 锁定源表，以防止另一个会话获取我们想要提供新 ID 的记录.它还为我们提供了 WHERE CURRENT OF 语法，以便于识别我们需要更新的记录.

You need to get procedural for such a requirement. This solution uses SELECT ... FOR UPDATE which locks the source table, to prevent another session nabbing the record we want to give the new ID. It also gives us the WHERE CURRENT OF syntax, which makes it easy to identify the record we need to update.

此解决方案假设存在用于填充标识列的序列.我们还可以使用其他选项(包括 12C 中的自动增量)，但 RETURNING 子句是获取新值的关键.

This solution supposes the existence of a sequence for populating the identity column. There are other options available to us (including auto-increments in 12C) but the RETURNING clause is the key to snagging the new value.

declare
    cursor c2 is
        select * from table2
        for update of id;
    r2 c2%rowtype;
    new_id t1.id%type;
begin
    open c2;
    loop
        fetch c2 in r2;
        exit when c2%notfound;
        /* new record */
        insert into t1
        values (t1_seq.nextval, t2.value)
        returning t1.id into new_id;
        /* update existing record with id*/
        update t2
        set id = new_id
        where current of c2;
    end loop;
    commit;
end;
/

此解决方案是 Row-By-Row"，这是确保将新 T1.ID 应用到 T2 中正确行的最简单方法.如果T1 很小和/或这是一个可能没问题的开关练习.但如果担心性能，则可以进行调整.

This solution is Row-By-Row" it is the easiest way to make sure that the new T1.ID gets applied to the correct row in T2. If T1 is small and/or this is a on-off exercise that's probably fine. But if performance is a concern there are tunings available.