Приветствую всех! В этой статье мы разберем операцию MERGE.
Для начала взглянем синтаксис:
MERGE <Основная таблица>
USING <Таблица или запрос источника>
ON <Условия объединения>
[WHEN MATCHED [AND <Доп. условие>]
THEN <UPDATE или DELETE>]
[WHEN NOT MATCHED [AND <Доп. условие>]
THEN <INSERT>]
[WHEN NOT MATCHED BY SOURCE [AND <Доп. условие>]
THEN <UPDATE или DELETE>] [...n]
[OUTPUT]
Оператор MERGE является очень мощным средством для преобразования данных, поскольку обладает функциональной возможностью, позволяющей ему проверять данные и выяснять, нуждается ли действительно данная строка в обновлении. Например, предположим, что требуется загрузить таблицу данными из какого-то источника, и необходимо, чтобы во время этой загрузки данные о клиенте вставлялись в таблицу только в том случае, если этот клиент является новым. То есть нужно, чтобы данные о клиентах, которые уже присутствуют в таблице, повторно в нее загружались, но чтобы там, где требуется, информация о клиентах обновлялась полученными новыми сведениями.
Оператор MERGE на самом деле представляет собой операцию UPDATE-ELSE-INSERT, выполняемую в рамках одного единственного SQL-оператора. Того же самого результата можно добиться и без применения оператора MERGE просто путем выполнения операции, состоящей из двух этапов: обновления всех строк, идентификатор клиента в которых совпадает с одним из тех, что уже существуют в таблице, и вставки строк, идентификатор клиента в которых не совпадает ни с одним из тех, что уже существуют в таблице. Приведенные ниже пример иллюстрирует применение описанного метода.
Этап обновления:
UPDATE catalog c SET
(catalog_name, catalog_desc, catalog_category, catalog_price) =
SELECT (catalog_name, catalog_desc, catalog_category, catalog_price)
FROM catalog_data d
WHERE c.catalog_id=d.catalog_id;
Этап вставки:
INSERT INTO catalog c
SELECT * FROM catalog_data d
WHERE c.catalog_id NOT IN (select catalog_id from catalog_data);
Всю предыдущую работу можно было бы выполнить и с помощью длинного блока кода PL/SQL. Процедуры PL/SQL должны были бы сопоставлять каждую строку с таблицей для выяснения, не существует ли она уже там, и на основании результатов этой проверки далее выполнять код либо для ее вставки, либо для ее обновления.
Как при применении SQL, так и при применении PL/SQL, для выполнения обновления и вставки неизбежно приходится иметь дело с непрактичной многочисленной обработкой одних и тех же данных. Оба этих метода являются довольно трудоемкими и отнимают много времени.
Оператор MERGE, иногда также называемый оператором обновления и вставки (upsert), из-за того, что он позволяет выполнять как обновление, так и вставку единственным SQL-оператором, представляет собой гораздо более эффективный способ для выполнения традиционных многоэтапных операций. Его применение почти подобно применению логики if-then-else. Далее показан пример выполнения процесса обновления и вставки с помощью оператора MERGE. В этом примере оператор MERGE, по сути, инструктирует Oracle обновлять строку, если таковая уже существует в таблице, и вставлять новую, если подобной строки в таблице еще нет.
MERGE INTO target t
USING source s
ON (t.product_id=s.product_id)
WHEN MATCHED THEN UPDATE SET
t.price=s.price, t.discount=s.discount
WHEN NOT MATCHED THEN INSERT
(product_id, product_code, product_desc,
product_price, product_discount)
VALUES
(s.product_id, s.product_code, s.product_desc,
s.product_price, s.product_discount);
Конструкция WHEN MATCHED THEN UPDATE SET определяет то, будет выполняться операция UPDATE или операция INSERT. При применении приведенного выше оператора обновление строки в целевой таблице будет происходить в том случае, если такая строка в ней существует. Если же такой строки нет, Oracle будет вставлять в целевую таблицу новую строку.
Помимо прямых операций вставки и обновления также можно выполнять и условные операции обновления и вставки, и при желании даже удалять некоторые строки, как описано в следующих разделах.
Условные операторы UPDATE и INSERT
Вместо безусловной вставки и обновления всех строк таблицы может возникать необходимость сделать так, чтобы данные вставлялись и обновлялись только при соблюдении определенных условий. Оператор MERGE позволяет обеспечивать такое условное поведение, потому что теперь Oracle разрешает использовать в находящихся внутри него конструкциях UPDATE и INSERT конструкцию WHERE и указывать в ней условие, при котором должна выполняться операция обновления или вставки данных, как показано ниже (где следует также обратить внимание на конструкцию USING).
MERGE INTO products p
/* Целевая таблица */
USING product_changes s /* Исходная таблица */
ON (p.prod_id = s.prod_id)
/* Условие поиска/соединения */
WHEN MATCHED THEN UPDATE /* Выполнять обновление, если условие соединения соблюдено */
SET p.prod_list_price = s.prod_new_price
WHERE p.prod_status <> 'EXPIRED'
WHEN NOT MATCHED THEN
/* Условное обновление */
INSERT /* Выполнять вставку, если условие соединения не соблюдено */
SET p.prod_list_price = s.prod_new_price
WHERE s.prod_status <> 'EXPIRED' /* Условная вставка */
Обратите внимание, что Oracle будет пропускать операцию INSERT или UPDATE в случае не удовлетворения оператором указанного в WHERE условия. То есть, как операции INSERT, так и операции UPDATE будут происходить только в том случае, если продукт не является просроченным (WHERE s.prod_status <> "EXPIRED").
Конструкция DELETE в операторе MERGE
Теперь Oracle позволяет указывать вместе с оператором MERGE необязательную конструкцию DELETE. Использовать эту конструкцию отдельно от оператора MERGE, однако, нельзя, так же как и конструкцию UPDATE или INSERT. Оператор DELETE нужно обязательно помещать внутрь оператора UPDATE. Это означает, что оператор DELETE не является глобальной конструкцией, а скорее работает в отношении только тех данных, на которые распространяется действие содержащейся в операторе MERGE конструкции UPDATE. Ниже показан пример добавления конструкции DELETE внутри конструкции UPDATE.
MERGE INTO products p
USING product_changes s ON (p.prod_id = s.prod_id)
WHEN MATCHED THEN UPDATE
SET p.prod_list_price = s.prod_new_price, p.prod_status = s.prod_new_status
DELETE WHERE (p.prod_status = 'OLD_ITEM')
WHEN NOT MATCHED THEN INSERT
(prod_id, prod_list_price, prod_status)
VALUES (s.prod_id, s.prod_new_price, s.prod_new_status);
Этот оператор будет сначала обновлять столбцы prod_list_price и prod_status таблицы product всякий раз, когда условие соединения будет оказываться истинным.
В данном случае условие соединения (p.prod_id = s.prod_id) предусматривает соединение двух таблиц: product (которая является исходной) и product_changes (которая является целевой).
Ниже перечислены два аспекта, о которые следует помнить при использовании оператора DELETE.
Действие конструкции DELETE распространяется только на те строки, которые были обновлены оператором MERGE. Оператор MERGE будет удалять только те строки, которые охватывает условие соединения, указанное в конструкции ON.
В приведенном примере при применении показанного оператора MERGE сначала в действие будет вступать конструкция UPDATE и, возможно, приводить к установке некоторых из значений prod_new_status в expired. После этого в действие будет вступать конструкция DELETE и приводить к удалению всех строк, значение prod_new_status которых было установлено в expired конструкцией UPDATE. Никакие другие строки со значением expired она удалять не будет, если только они не являются частью условия, определенного в конструкции ON.
Если статья была Вам полезна, ставьте пальцы вверх и подписывайтесь. Оставляйте свои пожелания и вопросы в комментариях, с удовольствием отвечу.
#it #sql #обучение #курс #программирование