INSERT ALL

📖 Описание

Oracle оператор INSERT ALL используется для добавления нескольких строк с помощью одного оператора INSERT. Строки могут быть вставлены в одну таблицу или несколько таблиц с использованием только одной команды SQL.


🧩 Синтаксис

Синтаксис оператора INSERT ALL в Oracle/PLSQL:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT 1 FROM dual;

mytable

Таблица в которую вставляются записи

column1, column2, column_n

Столбцы таблицы для вставки значений.

expr1, expr2, ... expr_n

Значения для столбцов в таблице.


В безусловном INSERT ALL каждая секция INTO выполняется для каждой строки завершающего SELECT. DUAL здесь даёт одну строку: три INTO означают три вставки. Если источник даст две строки, эти же три INTO будут выполнены шесть раз.

💻 Примеры

Подготовка

Создайте две пустые учебные таблицы. Опыты независимы: каждый завершается возвратом к SAVEPOINT. Автоматическая фиксация должна быть выключена; между опытами не выполняйте DDL или COMMIT.

CREATE TABLE demo_all_studios (
  studio_id NUMBER PRIMARY KEY,
  studio_name VARCHAR2(50) NOT NULL
);
CREATE TABLE demo_all_tutors (
  tutor_id NUMBER PRIMARY KEY,
  tutor_name VARCHAR2(50) NOT NULL,
  city VARCHAR2(40)
);
SAVEPOINT insert_all_start;

Вставка в одну таблицу

INSERT ALL
  INTO demo_all_studios (studio_id, studio_name) VALUES (101, 'Clay Room')
  INTO demo_all_studios (studio_id, studio_name) VALUES (102, 'Paper House')
  INTO demo_all_studios (studio_id, studio_name) VALUES (103, 'Wood Lab')
SELECT 1 FROM dual;

SELECT studio_id, studio_name FROM demo_all_studios ORDER BY studio_id;
STUDIO_ID STUDIO_NAME
101 Clay Room
102 Paper House
103 Wood Lab
ROLLBACK TO insert_all_start;

Те же строки можно добавить тремя отдельными INSERT. Это альтернативный способ; не выполняйте оба варианта подряд без отката:

INSERT INTO demo_all_studios (studio_id, studio_name) VALUES (101, 'Clay Room');
INSERT INTO demo_all_studios (studio_id, studio_name) VALUES (102, 'Paper House');
INSERT INTO demo_all_studios (studio_id, studio_name) VALUES (103, 'Wood Lab');
ROLLBACK TO insert_all_start;

По границе атомарности варианты различаются: INSERT ALL — один оператор. Без специальных механизмов обработки ошибок нарушение ограничения откатывает его целиком; при трёх отдельных INSERT ранее успешные операторы остаются в транзакции.

Вставка в несколько таблиц

INSERT ALL
  INTO demo_all_studios (studio_id, studio_name) VALUES (201, 'Canvas Studio')
  INTO demo_all_studios (studio_id, studio_name) VALUES (202, 'Mosaic Room')
  INTO demo_all_tutors (tutor_id, tutor_name, city) VALUES (71, 'Aziza', 'Tashkent')
SELECT 1 FROM dual;

SELECT COUNT(*) AS studio_count FROM demo_all_studios;
-- 2
SELECT COUNT(*) AS tutor_count FROM demo_all_tutors;
-- 1
ROLLBACK TO insert_all_start;

Альтернативная запись тремя операторами:

INSERT INTO demo_all_studios (studio_id, studio_name) VALUES (201, 'Canvas Studio');
INSERT INTO demo_all_studios (studio_id, studio_name) VALUES (202, 'Mosaic Room');
INSERT INTO demo_all_tutors (tutor_id, tutor_name, city) VALUES (71, 'Aziza', 'Tashkent');
ROLLBACK TO insert_all_start;

Условная вставка

INSERT ALL проверяет все WHEN: одна исходная строка может попасть в несколько целей.

INSERT ALL
  WHEN studio_id = 101 THEN
    INTO demo_all_studios (studio_id, studio_name)
    VALUES (studio_id, studio_name)
  WHEN tutor_id >= 71 THEN
    INTO demo_all_tutors (tutor_id, tutor_name, city)
    VALUES (tutor_id, tutor_name, city)
SELECT 101 AS studio_id, 'Clay Room' AS studio_name,
       71 AS tutor_id, 'Aziza' AS tutor_name, 'Tashkent' AS city
FROM dual
UNION ALL
SELECT 102, 'Paper House', 72, 'Nodira', 'Samarkand' FROM dual;

SELECT studio_id, studio_name FROM demo_all_studios ORDER BY studio_id;
-- 101 / Clay Room
SELECT tutor_id, tutor_name, city FROM demo_all_tutors ORDER BY tutor_id;
-- 71 / Aziza / Tashkent
-- 72 / Nodira / Samarkand
ROLLBACK TO insert_all_start;

INSERT FIRST остановился бы на первом истинном WHEN для каждой строки. В Oracle 19c у многотабличной вставки есть ограничения, в частности на представления и удалённые таблицы. Имена в VALUES и WHEN берутся из списка SELECT источника.

См. документацию Oracle: INSERT.