Многотабличные операторы вставки в Oracle
Мы все это знаем: если бы с вершины горы спустился длиннобородый пророк, несущий десять заповедей программирования Oracle, одна из них могла бы звучать так: Ты можешь выбирать из многих таблиц, но ты может обновлять, удалять или вставлять только в одну таблицу за раз. Верно? Ну, не совсем так. Потому что на самом деле вы можете добавлять данные в несколько таблиц
Мы все это знаем: если бы с вершины горы спустился длиннобородый пророк, несущий десять заповедей программирования Oracle, одна из них могла бы читаться так:
Вы можете выбирать из многих таблиц, но вы можете обновлять, удалять или вставлять только в одну таблицу за раз.
Верно? Ну, не совсем так. Потому что вы можете добавлять данные в несколько таблиц с помощью одного оператора вставки.
По моему опыту, несмотря на то, что вставка нескольких таблиц стала возможной в Oracle начиная с версии 9i, это редко используемая функция за пределами кругов фанатов ETL (и нет, я не хочу, чтобы вы присылали мне электронные письма, рассказывающие мне, как все вы знаете — от вашего дедушки до того бездомного парня с вашей улицы — использует многостоловые вставки все время.
)
Вставка в несколько таблиц — это средство, с помощью которого мы можем получать данные из одного источника и распространять их на несколько целей.
Преимущества многостоловых вставок
И вот несколько причин, по которым вы можете использовать эту функцию более регулярно.
- Есть изречение Тома Кайта: никогда не используйте несколько операторов, чтобы сделать то, чего можно достичь с помощью одного. (Я думаю, это Том Кайт, я слышал это; это мог быть какой-то другой мудрый человек.) Одно очевидное преимущество многотабличных вставок заключается в том, что с помощью одного оператора вы можете выполнить процесс, который в противном случае занял бы у вас много времени. многочисленные заявления.
- Из этого вытекает второе, менее очевидное преимущество: при многотабличной вставке ваш код более эффективен, поскольку он включает всего один цикл обращения к серверу, а не многочисленные обращения.
- В-третьих, как только вы освоитесь с синтаксисом (который мы обсудим далее), многотабличные вставки легко читать и легко — аккуратнее — писать.
Многостоловые вставки также имеют некоторые ограничения; однако мы вернемся к ним позже. Давайте сначала посмотрим на синтаксис.
Безусловная вставка в несколько таблиц
На самом деле существует два типа операторов вставки в несколько таблиц: условная и безусловная. Сначала рассмотрим последний, а к условным операторам множественной вставки мы вернемся чуть позже.
Есть несколько вещей, на которые я хотел бы обратить ваше внимание:
- Ключевое слово ALL обязательно для безусловных операторов вставки в несколько таблиц.
- Вы можете иметь сколько угодно пар вставки в пункт и значения пункта по вашему требованию. (Если бы у вас был только один, это не была бы вставка
- Исходный подзапрос является обязательным.
Пример, вероятно, был бы полезен на данном этапе, не так ли?
ВСТАВИТЬ ВСЕ Значения INTO table1 (col1, col2, col3) (source_table. INTO table2 (col1, col2, col4) значения (source_table.col1, source_table.col2, исходная_таблица.col4) Значения INTO table3(col1, col5, col6) (source_table.col1, source_table.col5, source_table.col6) SELECT * FROM source_table; |
col1, source_table.col2, source_table.col3)Приведенный выше оператор возьмет соответствующие столбцы из source_table и вставит их в table1, table2 и table3. Без многотабличных вставок потребовалось бы три отдельных оператора вставки.
И это для безусловных многотабличных вставок; красиво и прямолинейно.
Да, кстати, если вы находите это требование к исходному подзапросу немного ограничивающим, вот вам небольшой приятный трюк: выберите из двойного.
ВСТАВИТЬ ВСЕ В значения таблицы 1 (столбец 1, столбец 2, столбец 3) (1, ‘Мексика’, sysdate + 1) Значения INTO table3 (col1, col4, col6) (3, ‘Норвегия’, sysdate + 3) SELECT * FROM dual; |
Условные многотабличные вставки
Условные многотабличные вставки умнее своих безусловных собратьев и, на первый взгляд, выглядят намного сложнее.
Поверьте мне, это не так. Только представьте, что оператор Insert и оператор Case напились вместе, и девять месяцев спустя у них родился ребенок; это, вероятно, будет очень похоже на условную вставку нескольких таблиц. Вот синтаксис:
Давайте сразу рассмотрим пример. Представьте, что мы создали 3 новые таблицы — Top_earners, mid_earners и low_earners — и мы хотели заполнить их из стандартной таблицы EMP, основываясь на том, к какой налоговой категории относится каждый сотрудник.
1 2 3 4 5 6 7 8 | ВСТАВИТЬ ВСЕ WHEN (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal < 3000 AND sal >= 1500) ЗАТЕМ В mid_earners (emp_id, emp_name, emp_sal) ЗНАЧЕНИЯ (empno, ename, sal) WHEN (sal < 1500) THEN INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) SELECT * FROM emp; |
Еще раз взглянув на наше выражение, я понимаю, что мы можем немного изменить его, используя предложение ELSE.
1 2 3 4 5 6 7 8 | ВСТАВИТЬ ВСЕ КОГДА (sal >= 3000) ТО INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal < 3000 AND sal >= 1500) THEN INTO mid_earners (emp_id, emp_name, emp_sal) VALUES (e мпно, энаме, сал ) ELSE INTO low_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) SELECT * FROM emp; |
Последний фрагмент мозаики, о котором нам нужно поговорить, это ключевое слово FIRST, которое можно использовать в сочетании с операторами условной вставки в несколько таблиц (но не с безусловной вставкой в несколько таблиц).
Если вы начнете оператор с INSERT FIRST вместо INSERT ALL, Oracle проанализирует каждое условие WHEN (по порядку, сверху) и, как только найдет условие, которое разрешается в TRUE, выполнит эту вставку и не будет анализировать какие-либо последующих условий WHEN.
Это будет сделано для каждой строки, возвращаемой исходным подзапросом.
Понятно? Нет? Я так не думал. Позвольте мне посмотреть, смогу ли я сделать это немного яснее, переписав наш пример EMP, используя INSERT FIRST. Помните, что мы хотим разделить наших сотрудников в зависимости от того, сколько денег они зарабатывают.
1 2 3 4 5 6 7 8 | ВСТАВЬТЕ ПЕРВЫМ КОГДА (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, ename, sal) WHEN (sal >= 1500 ) THEN INTO mid_earners (emp_id, emp_name, emp_sal) ЗНАЧЕНИЯ (empno, ename, sal) КОГДА (sal > 0 ) THEN INTO low_earners (emp_id, emp_name, emp_sal) ЗНАЧЕНИЯ (empno, ename, sal) ВЫБЕРИТЕ * ИЗ emp; |
Приведенное выше утверждение не имело бы смысла с INSERT ALL. Кинг, зарплата которого составляет 5000, в итоге оказался бы вставленным в все три таблицы (поскольку 5000 больше 3000, а 5000 больше 1500, а 5000 больше, чем ничего).
Однако с INSERT FIRST Oracle выполняет оператор вставки, связанный с первым условием, которое истинно, и игнорирует все последующие. Итак, когда мы доберемся до Блейка, который зарабатывает 2850, первое условие станет ложным и будет проигнорировано, второе условие станет истинным, и поэтому его данные будут вставлены в 9.0013 mid_earners
Ограничения
Я же говорил, что есть несколько ограничений, о которых вам следует помнить, не так ли? К счастью, в них нет ничего серьезного. Однако:
- Вы не должны использовать последовательности в многотабличных операторах вставки.
- Вы не можете использовать вставку нескольких таблиц для записи в удаленную таблицу.
- Вы можете использовать многотабличные вставки только со таблицами. Не представления, материализованные или иные.
- Сумма всех столбцов в различных предложениях вставки в многотабличный оператор вставки не должна превышать 999.
