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 источника.