/привет/мир/etc
Непериодические заметки о программировании
воскресенье, 8 декабря 2013 г.
Oracle SQL MERGE
Синтаксис команды MERGE :
Что делает и чего не делает MERGE
Сделаю изменения в таблице-источнике, удалив, изменив и добавив по одной строке. После этого таблица books_copy уже не содержит актуальной копии и нуждается в обновлении:
Теперь для того, чтобы привести books_copy в соответствие с books необходимо:
Команда MERGE модифицирует и добавит строки в books_copy (пп. 1 и 2):
Однако, MERGE не может удалить из целевой таблицы строки, которые отсутствуют в источнике (п.3)!
Сделаю это отдельной командой DELETE и проверю, что теперь содержимое таблиц одинаково:
Что происходит «за кулисами»
А для того, чтобы в одном запросе выбрать вставляемые, модифицируемые, а также удаляемые строки, нужно воспользоваться полным внешним соединением, source FULL OUTER JOIN target :
К сожалению, MERGE этого не делает (и потому называется MERGE, а не SYNC).
Не нужно модифицировать все строки
Модификация всех строк целевой таблицы, сопоставленных с источником, в общем случае не нужна и затратна. Однако, в рассмотренном примере происходит именно это. Если работать таблицей, содержащей миллионы строк, то СУБД будет делать много ненужной работы.
Вывод: не нужно включать в условие ON иную логику, кроме логики сопоставления строк целевой таблицы и источника.
Иногда полезно модифицировать все строки?
Что мы имеем на данный момент?
Согласно предложенной выше логике, после выполнения команды MERGE с DELETE в books_copy должны произойти следующие изменения:
В заключение, удаляю использованные в экспериментах таблицы:
Объяснение оператора MERGE в SQL
Простой пример пояснит использование MERGE Statement.
Пример:
Предположим, есть две таблицы:
Задача состоит в том, чтобы обновить информацию о продуктах в PRODUCT_LIST в соответствии с UPDATED_LIST.
Решение
Теперь, чтобы объяснить этот пример лучше, давайте разделим пример на шаги.
Следовательно, в TARGET необходимо выполнить три операции в соответствии с вышеуказанными несоответствиями. Они есть:
Note: Refer this post for the syntax of MERGE statement.
SQL-запрос для выполнения вышеупомянутых операций с помощью оператора MERGE :
/* Selecting the Target and the Source */
MERGE PRODUCT_LIST AS TARGET
USING UPDATE_LIST AS SOURCE
/* 1. Performing the UPDATE operation */
/* If the P_ID is same,
check for change in P_NAME or P_PRICE */
ON (TARGET.P_ID = SOURCE.P_ID)
AND TARGET.P_NAME <> SOURCE.P_NAME
OR TARGET.P_PRICE <> SOURCE.P_PRICE
/* Update the records in TARGET */
SET TARGET.P_NAME = SOURCE.P_NAME,
/* 2. Performing the INSERT operation */
/* When no records are matched with TARGET table
Then insert the records in the target table */
WHEN NOT MATCHED BY TARGET
THEN INSERT (P_ID, P_NAME, P_PRICE)
VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
/* 3. Performing the DELETE operation */
/* When no records are matched with SOURCE table
Then delete the records from the target table */
WHEN NOT MATCHED BY SOURCE
Таким образом, таким образом все мы можем выполнить все эти три основных оператора в SQL вместе с помощью оператора MERGE.
Примечание. Любое имя, отличное от target и source, может использоваться в синтаксисе MERGE. Они используются только для того, чтобы дать вам лучшее объяснение.
Пожалуйста, пишите комментарии, если вы обнаружите что-то неправильное, или вы хотите поделиться дополнительной информацией по обсуждаемой выше теме.
MERGE
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.
Oracle Database does not implement fine-grained access control during MERGE statements. If you are using the fine-grained access control feature on the target table or tables, use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.
Description of the illustration merge.gif
Description of the illustration merge_update_clause.gif
Description of the illustration merge_insert_clause.gif
Description of the illustration where_clause.gif
Description of the illustration error_logging_clause.gif
Use the INTO clause to specify the target table or view you are updating or inserting into. In order to merge data into a view, the view must be updatable. Please refer to «Notes on Updatable Views» for more information.
Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery.
Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.
The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.
Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.
Restrictions on the merge_update_clause This clause is subject to the following restrictions:
You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.
The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.
Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Oracle Database skips the insert operation for all rows for which the condition is not true.
Restriction on Merging into a View You cannot specify DEFAULT when updating a view.
The error_logging_clause has the same behavior in a MERGE statement as in an INSERT statement. Please refer to the INSERT statement error_logging_clause for more information.
Что такое merge sql
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.
Use the INTO clause to specify the target table or view you are updating or inserting into. In order to merge data into a view, the view must be updatable. Refer to «Notes on Updatable Views» for more information.
Restriction on Target Views
You cannot specify a target view on which an INSTEAD OF trigger has been defined.
Use the USING clause to specify the source table or view you are updating or inserting from.
Restriction on USING Clause
You cannot use a subquery within the SELECT list of the USING clause.
Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.
Restrictions on the ON Clause
The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.
Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.
Restrictions on the merge_update_clause
This clause is subject to the following restrictions:
You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.
The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.
Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Oracle Database skips the insert operation for all rows for which the condition is not true.
Restriction on the merge_insert_clause
You cannot specify DEFAULT when inserting into a view.
The error_logging_clause has the same behavior in a MERGE statement as in an INSERT statement. Refer to the INSERT statement error_logging_clause for more information.
Merging into a Table: Example
Conditional Insert and Update: Example
The following example conditionally inserts and updates table data by using the MERGE statement.
The following statements create two tables named people_source and people_target and populate them with names:
The following statement compares the contents of people_target and people_source by using the person_id column. The values in the people_target table are updated when there is a match in the people_source table:
The following statements display the contents of the people_target table and perform a rollback:
This statement compares the contents of the people_target and people_source tables by using the person_id column. The values in the people_target table are updated only when there is no match in the people_source table:
The following statements display the contents of the people_target table and perform a rollback:
The following statement compares the contents of the people_target and people_source tables by using the person_id column and conditionally inserts and updates data in the people_target table. For each matching row in the people_source table, the values in the people_target table are updated by using the values from the people_source table. Any unmatched rows from the people_source table are added to the people_target table:
The following statements display the contents of the people_target table and perform a rollback:
The following statement compares the people_target and people_source tables by using the person_id column. When the person_id matches, the corresponding rows in the people_target table are updated by using values from the people_source table. The DELETE clause removes all the values in people_target where title is вЂMrs.’. When the person_id does not match, the rows from the people_source table are added to the people_target table. The WHERE clause ensures that only values that have title as вЂMr’ are added to the people_target table:
The following statements display the contents of the people_target table and perform a rollback:
Что такое merge sql
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.
Use the INTO clause to specify the target table or view you are updating or inserting into. In order to merge data into a view, the view must be updatable. Refer to «Notes on Updatable Views» for more information.
Restriction on Target Views
You cannot specify a target view on which an INSTEAD OF trigger has been defined.
Use the USING clause to specify the source you are updating or inserting from.
The merge_update_clause specifies the new column values of the target table or view. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.
Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.
Restrictions on the merge_update_clause
This clause is subject to the following restrictions:
You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.
The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.
Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source columns. Oracle Database skips the insert operation for all rows for which the condition is not true.
Restriction on the merge_insert_clause
You cannot specify DEFAULT when inserting into a view.
The error_logging_clause has the same behavior in a MERGE statement as in an INSERT statement. Refer to the INSERT statement error_logging_clause for more information.
Merging into a Table: Example
Conditional Insert and Update: Example
The following example conditionally inserts and updates table data by using the MERGE statement.
The following statements create two tables named people_source and people_target and populate them with names:
The following statement compares the contents of people_target and people_source by using the person_id column. The values in the people_target table are updated when there is a match in the people_source table:
The following statements display the contents of the people_target table and perform a rollback:
This statement compares the contents of the people_target and people_source tables by using the person_id column. The values in the people_target table are updated only when there is no match in the people_source table:
The following statements display the contents of the people_target table and perform a rollback:
The following statement compares the contents of the people_target and people_source tables by using the person_id column and conditionally inserts and updates data in the people_target table. For each matching row in the people_source table, the values in the people_target table are updated by using the values from the people_source table. Any unmatched rows from the people_source table are added to the people_target table:
The following statements display the contents of the people_target table and perform a rollback:
The following statement compares the people_target and people_source tables by using the person_id column. When the person_id matches, the corresponding rows in the people_target table are updated by using values from the people_source table. The DELETE clause removes all the values in people_target where title is вЂMrs.’. When the person_id does not match, the rows from the people_source table are added to the people_target table. The WHERE clause ensures that only values that have title as вЂMr’ are added to the people_target table:
The following statements display the contents of the people_target table and perform a rollback:
Dealing with Inputs from an Application
Usually applications have to check for the existence of a row first in order to decide whether to INSERT a new row, or UPDATE an already existing one. The MERGE statement eliminates the need for such a check by allowing the use of bind variables inside the USING statement as a source.
The following statements demonstrate the use of bind variables to insert a new row into the people_target table:
The following statements display the contents of the people_target table and perform a rollback:
The following statements display the contents of the people_target table and perform a rollback:
