使用 INSERT...SELECT 返回的替代方法

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

这基本上是本能地导致编写这个查询。

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

由于某些正当理由，这是不允许的。

有替代方案吗？

您正在使用insert into ... select from构造。因此，您的语句可能会插入不止一行，这意味着您的 RETURNING 子句将返回不止一行。因此，您需要使用 BULK COLLECT 语法来填充收藏新钥匙。

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

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 命令未正确结束

好吧，那很糟糕。

不幸的是，虽然 RETURNING BULK COLLECT INTO 适用于更新和删除，但它不适用于插入（或合并）。我确信 Oracle 内核的内部架构中有非常合理的原因，但这应该可以工作，但如果不工作则最烦人。

不管怎样，正如 @PonderStibbons 指出的，有一个解决方法：FORALL 构造。

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;
/

Here is LiveSQL 演示（需要免费 OTN 登录） https://livesql.oracle.com/apex/livesql/s/gdwpwconq1k0lj56rp4ms3731.