使用INSERT ... SELECT进行返回的替代方法 [英] Alternative to RETURNING with INSERT...SELECT

问题描述

在这种情况下，需要通过从另一个表复制一些列并从此插入中返回生成的键来插入到表中.使用Oracle数据库.

There's this scenario which involves inserting into a table by copying some columns from another table and returning the generated key out of this insert. Using Oracle Database.

基本上是本能地编写此查询.

Which basically by instinct result to writing this query.

INSERT INTO TBL_XXX
SELECT COLA, COLB, COLC FROM TBL_YYY
RETURNING COLA INTO COL_RES

出于某些正当理由而不允许使用.

Which is not allowed for some valid reason.

还有其他替代方法吗?

推荐答案

您正在使用insert into ... select from构造.因此，您的语句可能会插入多行，这意味着您的RETURNING子句将返回多行.因此，您需要使用BULK COLLECT语法填充新密钥的集合.

You're using the insert into ... select from construct. So potentially your statement will insert more than one row, which means your RETURNING clause will return more than one row. Consequently you need to use the BULK COLLECT syntax to populate a collection of new keys.

所以我们尝试这样的事情...

So we try something like this ...

declare
    /* NB: define this collection using the appropriate name  */
    type new_keys is table of table_xxx.cola%type;
    col_res new_keys;
begin
    INSERT INTO TBL_XXX
    SELECT COLA * 10, COLB, COLC FROM TBL_YYY
    RETURNING table_xxx.COLA bulk collect INTO COL_RES;
end;
/

...只能得到:

ORA-06550:第8行，第15列:
PL/SQL:ORA-00933:SQL命令未正确结束

ORA-06550: line 8, column 15:
PL/SQL: ORA-00933: SQL command not properly ended

糟透了.

不幸的是，虽然返回大容量收集时可用于更新和删除，但不能用于插入(或合并).我确信Oracle内核的内部体系结构中有非常合理的理由，但这应该行之有效，而且并不是最令人讨厌的事情.

Unfortunately, while RETURNING BULK COLLECT INTO works with updates and deletes it does not work with inserts (or merges come to that). I'm sure there are very sound reasons in the internal architecture of the Oracle kernel but this ought to work, and that it doesn't is most annoying.

无论如何，正如@PonderStibbons指出的，有一种解决方法:FORALL构造.

Anyway, as @PonderStibbons pointed out there is a workaround: the FORALL construct.

declare
    type new_rows is table of tbl_xxx%rowtype;
    rec_xxx new_rows;
    type new_keys is table of tbl_xxx.cola%type;
    col_xxx new_keys;
begin
    select cola * 10, colb, colc 
    bulk collect into rec_xxx
    from tbl_yyy;

    forall idx in 1 .. rec_xxx.count()
        insert into tbl_xxx
        values rec_xxx(idx)
        returning tbl_xxx.cola bulk collect into col_xxx
    ;

    for idx in 1 .. rec_xxx.count() loop
        dbms_output.put_line('tbl_xxx.cola = ' || col_xxx(idx));
   end loop;
end;
/

这是一个LiveSQL演示(需要免费OTN登录).

这篇关于使用INSERT ... SELECT进行返回的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！