PLSQL插入与子查询和返回子句(Oracle)

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

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

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

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

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

Heres what oracle doc says:

以下是oracle doc说的:

Insert Statement

Insert语句

Returning Into

返回到

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

好吧，我认为只有价值观条款是不可能的。有另一个吗?

4 个解决方案

#1

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

不能使用从INSERT中返回的大容量收集。该方法可以使用更新和删除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;

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

您可以通过一些额外的步骤(执行FORALL INSERT using TREAT)让它工作，如本文所述:

returning with insert..select

返回与insert…select

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

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

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

#2

Unfortunately that's not possible. RETURNING is only available for INSERT...VALUES statements. See this Oracle forum thread for a discussion of this subject.

不幸的是,这是不可能的。返回只能用于插入…值语句。有关这个主题的讨论，请参见这个Oracle论坛帖子。

#3

Because the insert is based on a select, Oracle is assuming that you are permitting a multiple-row insert with that syntax. In that case, look at the multiple row version of the returning clause document as it demonstrates that you need to use BULK COLLECT to retrieve the value from all inserted rows into a collection of results.

由于插入基于select，所以Oracle假定您允许具有该语法的多行插入。在这种情况下，查看返回子句文档的多行版本，因为它演示了您需要使用批量收集来从所有插入的行中检索值。

After all, if your insert query creates two rows - which returned value would it put into an single variable?

毕竟，如果您的insert查询创建了两行——返回的值会被放入一个变量中吗?

EDIT - Turns out this doesn't work as I had thought.... darn it!

编辑-证明这并不工作,我原以为....真讨厌!

#4

-2

This isn't as easy as you may think, and certainly not as easy as it is using MySQL. Oracle doesn't keep track of the last inserts, in a way that you can ping back the result.

这并不像你想的那么简单，当然也不像使用MySQL那么简单。Oracle不跟踪最后的插入，以一种可以返回结果的方式。

You will need to work out some other way of doing this, you can do it using ROWID - but this has its pitfalls.

您将需要找到其他的方法来实现这一点，您可以使用ROWID来实现这一点——但是这有它的缺陷。

This link discussed the issue: http://forums.oracle.com/forums/thread.jspa?threadID=352627

这个链接讨论了这个问题:http://forums.oracle.com/forums/thread.jspa?threadID=352627。

#1