PLSQL插入带有子查询和返回子句

本文介绍了PLSQL插入带有子查询和返回子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我不知道以下伪 sql 的正确语法:

I can't figure out the correct syntax for the following pseudo-sql:

INSERT INTO some_table (column1, column2) SELECT col1_value, col2_value FROM other_table WHERE ... RETURNING id INTO local_var;

我想用子查询的值插入一些东西.插入后我需要新生成的 id.

I would like to insert something with the values of a subquery. After inserting I need the new generated id.

这是 oracle 文档所说的:

Heres what oracle doc says:

插入声明

回归

好的，我认为仅使用 values 子句是不可能的...有其他选择吗?

OK i think it is not possible only with the values clause... Is there an alternative?

推荐答案

您不能使用 INSERT 中的 RETURNING BULK COLLECT.但是，此方法可用于更新和删除:

You cannot use the RETURNING BULK COLLECT from an INSERT. This methodology can work with updates and deletes howeveer:

create table test2(aa number) / insert into test2(aa) select level from dual connect by level<100 / set serveroutput on declare TYPE t_Numbers IS TABLE OF test2.aa%TYPE INDEX BY BINARY_INTEGER; v_Numbers t_Numbers; v_count number; begin update test2 set aa = aa+1 returning aa bulk collect into v_Numbers; for v_count in 1..v_Numbers.count loop dbms_output.put_line('v_Numbers := ' || v_Numbers(v_count)); end loop; end;

您可以通过几个额外的步骤使其工作(使用 TREAT 进行 FORALL INSERT)如本文所述:

You can get it to work with a few extra steps (doing a FORALL INSERT utilizing TREAT) as described in this article:

用 insert..select 返回

T

利用他们创建的示例并将其应用于 test2 测试表

to utilize the example they create and apply it to test2 test table

CREATE or replace TYPE ot AS OBJECT ( aa number); / CREATE TYPE ntt AS TABLE OF ot; / set serveroutput on DECLARE nt_passed_in ntt; nt_to_return ntt; FUNCTION pretend_parameter RETURN ntt IS nt ntt; BEGIN SELECT ot(level) BULK COLLECT INTO nt FROM dual CONNECT BY level <= 5; RETURN nt; END pretend_parameter; BEGIN nt_passed_in := pretend_parameter(); FORALL i IN 1 .. nt_passed_in.COUNT INSERT INTO test2(aa) VALUES ( TREAT(nt_passed_in(i) AS ot).aa ) RETURNING ot(aa) BULK COLLECT INTO nt_to_return; FOR i IN 1 .. nt_to_return.COUNT LOOP DBMS_OUTPUT.PUT_LINE( 'Sequence value = [' || TO_CHAR(nt_to_return(i).aa) || ']' ); END LOOP; END; /