Альтернатива ВОЗВРАЩЕНИЮ с INSERT...SELECT

Question

Есть такой сценарий, который включает вставку в таблицу путем копирования некоторых столбцов из другой таблицы и возврата сгенерированного ключа из этой вставки. Использование базы данных Oracle.

Который в основном инстинктивно приводит к написанию этого запроса.

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

Что не допускается по какой-либо уважительной причине.

Есть ли альтернатива этому?

Answer 1

Вы используете 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;
/

Вот демонстрация LiveSQL (требуется бесплатный вход в OTN).