Приемы множественной вставки данных в Oracle

Существует ли в Oracle возможность одной командой ставить несколько строк в одну таблицу? А в несколько таблиц одновременно? При этом указав условия, по которым строка попадет в ту или иную таблицу? Такая возможность существует и называется множественная вставка.

Множественная вставка в одну таблицу

Одновременную вставку нескольких строк в одну таблицу можно при помощи команды INSERT ALL:

create table tab1
(
f1 number,
f2 varchar2(255)
)
/

Table created.

insert all
into tab1 (f1,f2) values (1,'One')
into tab1 (f1,f2) values (2,'Two')
into tab1 (f1,f2) values (3,'Three')
select * from dual
/

3 rows created.

Следует отметить обязательное присутствие в команде оператора SELECT. Эквивалентная команда с более традиционным синтаксисом выглядит так:

insert into tab1 (f1,f2)
select 1, 'One' from dual
union all
select 2, 'Two' from dual
union all
select 3, 'Three' from dual
/

3 rows created.

Синтаксис команды INSERT ALL выглядит естественней, чем данный INSERT SELECT.
Рассмотрим еще пример:

insert all
into tab1(f1,f2) values (num1,str1)
into tab1(f1,f2) values (num2,str2)
select 1 as num1, 'One' as str1,
2 as num2, 'Two' as str2
from dual
/

2 rows created.

В этом случае, извлекая 4 значения из DUAL, мы вставляем их в две строки целевой таблицы.

Множественная вставка в несколько таблиц одновременно

Если множественную вставку в одну таблицу можно заменить традиционным INSERT SELECT, то вставку в несколько таблиц одновременно можно выполнить только с помощью INSERT ALL:

create table tab2
(
f1 number,
f2 varchar2(255)
)
/

Table created.

insert all
into tab1 (f1,f2) values (1,'One')
into tab2 (f1,f2) values (2,'Two')
select * from dual
/

2 rows created.

Проверим результат:

select * from tab1
/

F1 F2
---------- ----------------------------------------------------
1 One

1 row selected.

select * from tab2
/

F1 F2
---------- ----------------------------------------------------
2 Two

1 row selected.

Условная множественная вставка

Для ситуаций, когда таблицу для вставки нужно определить в зависимости от содержимого строк, подойдет возможность условной вставки. Рассмотрим следующий пример:

create table tab1000
(
f number
)
/

Table created.

create table tab2000
(
f number
)
/

Table created.

В таблицу TAB1000 должны попадать строки, со значением до 1000, в таблицу TAB2000 – со значением от 1000 до 2000:

insert all
when num < 1000
then into tab1000
when num < 2000
then into tab2000
select 100 as num from dual
union all
select 500 as num from dual
union all
select 1200 as num from dual
union all
select 1700 as num from dual
/

6 rows created.

Однако результат выполнения команды отличается от ожидаемого — вставлено 6 строк, а не 4. Проверим содержимое таблиц:

select * from tab1000
/

F
----------
100
500

2 rows selected.

select * from tab2000
/

F
----------
100
500
1200
1700

4 rows selected.

В таблицу tab2000 ставилось 4 строки, так как все они подошли по условию < 2000. Правильным будет такой вариант:

insert all
when num < 1000
then into tab1000
when num >= 1000 and num < 2000
then into tab2000
select 100 as num from dual
union all
select 500 as num from dual
union all
select 1200 as num from dual
union all
select 1700 as num from dual
/

4 rows created.

Также можно было использовать команду INSERT FIRST вместо INSERT ALL:

insert first
when num < 1000
then into tab1000
when num < 2000
then into tab2000
select 100 as num from dual
union all
select 500 as num from dual
union all
select 1200 as num from dual
union all
select 1700 as num from dual
/

4 rows created.

При использовании INSERT FIRST строка будет вставлена в первую таблицу, попадающую под условие.
Существует возможность ставить строки, не попадающие ни под одно условие, указав в разделе ELSE целевую таблицу для таких строк:

create table tab_else
(
f number
)
/

Table created.

insert first
when num < 1000
then into tab1000
when num < 2000
then into tab2000
else into tab_else
select 100 as num from dual
union all
select 500 as num from dual
union all
select 1200 as num from dual
union all
select 1700 as num from dual
union all
select 3000 as num from dual
union all
select 5000 as num from dual
/

6 rows created.

select * from tab_else
/

F
----------
3000
5000

2 rows selected.

Подытожывая данный материал, хочу сказать, что использование команд INSERT ALL и INSERT FIRST позволит сократить размеры кода, а также увеличить его производительность, при решении типичных задач раскладывания строк по нескольким таблицам или в несколько таблиц одновременно.