PLSQL Вставить в с подзапросом и возвращающим предложением (Oracle)

Я не могу понять правильный синтаксис для следующего псевдо-sql:

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

Я хотел бы вставить что-то со значениями подзапроса. После вставки мне нужен новый сгенерированный идентификатор.

Вот что говорит оракул:

Вставить выражение

Возвращение в

ОК, я думаю, что это невозможно только при условии значения... Есть ли альтернатива?

Ответ 1

Вы не можете использовать СОХРАНЕНИЕ BULK BULK из 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;

Вы можете заставить его работать с несколькими дополнительными шагами (делая FORALL INSERT, используя TREAT) как описано в этой статье:

возврат с помощью insert..select

использовать пример, который они создают, и применить его к тестовой таблице 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

К сожалению, это невозможно. RETURNING доступен только для операторов INSERT... VALUES. См. этот поток форума Oracle для обсуждения этой темы.

Ответ 3

Поскольку вставка основана на выборе, Oracle предполагает, что вы разрешаете вставки с несколькими строками с этим синтаксисом. В этом случае рассмотрите версию с несколькими строками документа предложения возврата, поскольку он показывает, что вам нужно использовать BULK COLLECT для извлечения значения из всех вставленных строк в коллекцию результатов.

В конце концов, если ваш запрос на вставку создает две строки - какое возвращаемое значение будет помещено в одну переменную?

РЕДАКТИРОВАТЬ - Оказывается, это не работает, как я думал... штопать!

Ответ 4

Это не так просто, как вы думаете, и, конечно, не так просто, как при использовании MySQL. Oracle не отслеживает последние вставки, чтобы вы могли отсканировать результат.

Вам нужно будет выработать другой способ сделать это, вы можете сделать это с помощью ROWID, но у этого есть свои подводные камни.

В этой ссылке обсуждалась проблема: http://forums.oracle.com/forums/thread.jspa?threadID=352627