Рис. 5.7. Результат работы запроса UPDATE OR INSERT

В случае если бы исполнителя с кодом 1 не существовало в таблице Executor, в нее была бы вставлена новая строка.

Предложение MATCHING используется для указания столбцов в таблице, значения в которых следует сопоставить с соответствующими значениями в списке VALUES. По умолчанию сопоставляются значения в столбцах первичных ключей.

Примечание. Если предложение MATCHING не используется, то в целевой таблице должен обязательно существовать первичный ключ.

Например, предыдущий запрос с предложением MATCHING может быть записан как

UPDATE OR INSERT INTO Executor

VALUES (1,ПЕТРОВ А.С.) MATCHING (ExecutorCD);,

что подразумевается по умолчанию.

Рассмотрим пример, когда может быть полезным использование предложения MATCHING. Например, требуется внести информацию о ремонтной заявке с кодом 25, поданной 17 декабря 2001 года абонентом с номером лицевого счета 005488 и выполненной 22 декабря 2001 года. Запрос может выглядеть следующим образом:

UPDATE OR INSERT INTO Request (RequestCD, AccountCD, IncomingDate, ExecutionDate) VALUES (25,005488,17.12.2001,22.12.2001);. В процессе выполнения запроса происходит сравнение значения 25 со значениями столбца RequestCD (первичный ключ) в таблице Request. Совпадения не обнаруживается, поэтому происходит вставка новой строки в таблицу Request. Фрагмент таблицы Request после выполнения запроса представлен на рис. 5.8.

Рис. 5.8. Последняя запись в таблице Request после добавления

Предположим теперь, что вставляются те же данные, но надо учесть, что если информация о заявке абонента с лицевым счетом 005488 от 17 декабря 2001 года уже зарегистрирована, то следует обновить соответствующую строку. Тогда следует использовать следующий запрос:

UPDATE OR INSERT INTO Request (RequestCD, AccountCD, IncomingDate, ExecutionDate) VALUES (25,005488,17.12.2001,22.12.2001)

MATCHING (AccountCD, IncomingDate);.

Фрагмент таблицы Request после выполнения запроса представлен на рис.

5.9.

Рис. 5.9. Обновленная первая строка в таблице Request

В процессе выполнения данного запроса значения первичных ключей не сравниваются, так как в предложении MATCHING явно указано, что надо сравнить значения в столбцах AccountCD и IncomingDate с соответствующими значениями из списка VALUES. Обнаруживается совпадение в первой строке таблицы Request и происходит ее обновление.

В списке значений предложения VALUES могут быть указаны не константы, а переменные, если запрос используется в модуле процедурного SQL .

Следует также отметить, что для выполнения данного запроса у пользователя должны быть права как на вставку, так и на изменение записей в таблице или представлении (управление доступом к ресурсам будет подробно описано далее).

5.4. Слияние данных

Для выбора строк из одной таблицы с целью обновления или вставки строк в другую таблицу используется запрос MERGE. Выбор действия - обновить строку или добавить ее - определяется в зависимости от условия. Запрос MERGE фактически представляет собой объединение запросов INSERT и UPDATE и позволяет избежать их многократного использования.

Запрос имеет следующий синтаксис:

MERGE

INTO <целевая таблица> [ [AS] псевдоним ]

USING <исходная таблица> [ [AS] псевдоним ] ON <условие соединения> [ WHEN MATCHED THEN

UPDATE SET столбец1 = <выражение1> [, столбец2 = <выражение2>] ... [ WHEN NOT MATCHED THEN INSERT [ ( <список столбцов> ) ]

VALUES ({константа1 переменная1} [, {константа2 переменная2}] ... ) ];,

<целевая таблица>:: = { базовая таблица представление},

<исходная таблица>:: = { базовая таблица представление <производная таблица>}.

Запрос MERGE работает следующим образом. Выбирается первая строка из исходной таблицы и проверяется, существует ли такая строка целевой таблицы, что на ней становится истинным <условие соединения>. Если да - то производится обновление этой строки целевой таблицы согласно запросу UPDATE, указанному в предложении WHEN MATCHED. Если таких строк целевой таблицы несколько, все они обновляются. Если для текущей строки исходной таблицы <условие соединения> возвращает FALSE для всех строк целевой таблицы, то в целевую таблицу вставляется строка согласно запросу INSERT, указанному в предложении WHEN NOT MATCHED. В одном запросе могут быть определены предложения WHEN MATCHED и WHEN NOT MATCHED одновременно или одно из них (обязательно).

Описанная выше процедура повторяется для всех строк исходной таблицы.

Допустим, требуется выбрать абонентов, проживающих на улице с кодом 7, и у всех поданных ими ремонтных заявок изменить код исполнителя на 5. Если у выбранного абонента нет зарегистрированных заявок, следует внести информацию в таблицу ремонтных заявок. Запрос на слияние данных будет выглядеть следующим образом:

MERGE

INTO Request R

USING (SELECT * FROM Abonent WHERE StreetCD = 7) Ab ON (R.AccountCD = Ab.AccountCD)

WHEN MATCHED THEN

UPDATE SET ExecutorCD = 5 WHEN NOT MATCHED THEN

INSERT (RequestCD, AccountCD, ExecutorCD)

VALUES (25, Ab.AccountCD, 5);.

Фрагмент таблицы Request после выполнения запроса представлен на рис.

5.10.

Рис. 5.10. Обновленные и добавленные строки в таблице Request

Производной таблицей, определенной в предложении USING, возвращаются 2 строки, содержащие информацию об абонентах с номерами лицевых счетов 136159 и 443690, проживающих на улице с кодом 7. Так как у абонента со счетом 136159 зарегистрирована ремонтная заявка с кодом 10, то значение столбца ExecutorCD в данной строке изменяется на 5. У абонента со счетом