如何将一个表中的值插入到另一个表中，然后更新原始表? [英] How to insert values from one table into another and then update the original table?

问题描述

使用Oracle，有没有办法将一个表中的值插入到另一个表中，然后从插入的表中取出一个标识值并更新原来的列?

Using Oracle, is there a way to insert values from one table into another table, then take an identity value from the inserted table and update a column in the original?

TABLE_1 为空

ID    VALUE
-----------

来自 TABLE_2 的值 ...

Values from TABLE_2 ...

ID    VALUE
-----------
0     Val 1
0     Val 2
0     Val 3

...插入到 TABLE_1(带有标识列)

...get inserted into TABLE_1 (with an Identity column)

ID    VALUE
-----------
1     Val 1
2     Val 2
3     Val 3

然后用 ID 更新 TABLE_2

And then updates TABLE_2 with the IDs

ID    VALUE
-----------
1     Val 1
2     Val 2
3     Val 3

推荐答案

您需要为这样的需求获取程序.此解决方案使用 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.

这篇关于如何将一个表中的值插入到另一个表中，然后更新原始表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！