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

Функция

Определение оператора слияния (синхронизации) приемника и источника данных.

В некоторых информационных системах встречается потребность в передаче строк из таблицы, созданной или обновленной при выполнении транзакции (транзакционной или исходной таблицы), в некоторую основную (целевую) таблицу БД. Исходная таблица может содержать как обновленные варианты строк, существующие в целевой таблице, так и новые строки, которые должны быть добавлены в целевую таблицу.

С помощью традиционных операторов манипулирования данными содержимое исходной таблицы можно перенести в целевую таблицу за два шага:

  1. выполнить оператор UPDATE для всех строк целевой таблицы, для которых имеются модифицированные «двойники» в исходной таблице;

  2. выполнить оператор INSERT для добавления в целевую таблицу тех строк исходной таблицы, для которых в целевой таблице нет «двойников».

Оператор MERGE реализует эти функции за один шаг.

Спецификация

         
< слияние данных >::=
< условие слияния >::=
< слияние с сопоставлением >::=
< слияние без сопоставления >::=
WHEN NOT MATCHED THEN вставка строки
< корректировка строки >::=
< список столбцов целевой таблицы >::=
< имя столбца целевой таблицы >::=
< список значений >::=
< элемент списка >::=

Синтаксические правила

   
  1. < RAL >, < WAL > – уровни доступа на чтение и запись (см. документ «СУБД ЛИНТЕР. Администрирование комплекса средств защиты данных», раздел «Уровни доступа»).

    Примечание

    Поддерживается только в СУБД ЛИНТЕР БАСТИОН.

  2. < Целевая таблица > – имя объекта БД (базовая таблица или обновляемое представление), который должен быть синхронизирован с < исходной таблицей > по заданному < условию слияния >.

  3. < Целевая таблица > не может быть удаленной таблицей, таблицей «в памяти», временной таблицей, циклической таблицей или реплицируемой таблицей.

  4. < Псевдоним > задает альтернативное имя, используемое для указания ссылок на целевую или исходную таблицу.

  5. < Исходная таблица > – имя объекта БД (базовая/временная/глобальная/удаленная таблица (за исключением циклической), подзапрос, любое представление), который является источником данных для синхронизации (слияния) с < целевой таблицей >.

  6. Оператор MERGE может иметь только одну конструкцию < слияние с сопоставлением >.

  7. Оператор MERGE может иметь только одну конструкцию < слияние без сопоставления >.

  8. Должно быть указано либо < слияние сопоставление >, либо < слияние без сопоставления >, либо обе конструкции одновременно (в любом порядке).

  9. < Значимое выражение > может включать в себя имена столбцов как исходной, так и целевой таблицы.

Общие правила

  1. Выполнение MERGE требует привилегии UPDATE на обновляемую таблицу при наличии конструкции WHEN MATСHED и привилегии INSERT на обновляемую таблицу при наличии конструкции WHEN NOT MATСHED, а также привилегии SELECT на все используемые таблицы.

  2. Алгоритм выполнения оператора.

    Пусть T1 – < исходная таблица >, а T2 – < целевая таблица >

    Тогда алгоритм выполнения оператора определяется следующим образом:

    • строки таблицы T1 просматриваются в некотором порядке. Пусть R1 – очередная строка T1. Для этой строки вычисляется < условие слияния > например, синхронизация заказов на определенную дату

      T1.< N_заказа >=T2.< N_заказа > AND T2.< дата >='28.04.2010';
    • если значением < условия слияния > является true, т. е. в < целевой таблице > есть строка с данным номером заказа за указанную дату, то:

      • если в операторе содержится раздел < слияние с сопоставлением >:

        • в этой строке корректируются значения тех столбцов, которые указаны в < списке столбцов > раздела < корректировка строки >, т.е., например, вносятся сведения об изменении заказа. В < списке столбцов > указываются имена столбцов таблицы T1, т.е. что строка R2 будет модифицироваться на основе значений столбцов строки R1;

        • в противном случае строка R1 игнорируется;

    • если значением < условия слияния > является false, т. е. в < целевой таблице > нет строки с данным номером заказа на указанную дату, то:

      • если в операторе содержится раздел < слияние без сопоставления >:

        • в таблицу T2 вставляется строка, специфицируемая списком выражений раздела < вставка строки >. В < списке столбцов > указываются имена столбцов таблицы T1, т.е. строка, заново вставляемая в таблицу T2, будет формироваться на основе значений столбцов строки R1);

        • в противном случае строка R1 игнорируется.

  3. Для заданного < условия слияния > каждой строке таблицы T1 должна соответствовать не более чем одна строка таблицы T2. В противном случае генерируется код завершения 76 («Получено результирующее множество из нескольких записей (ожидалась одна запись)»). Таким образом, на одну строку < исходной таблицы > допускается только одна операция вставки или обновления.

  4. < Условие слияния > определяет критерии совпадения строк целевой и исходной таблиц. Задается с помощью конструкции < логическое выражение >.

    Пусть исходная таблица содержит список автовладельцев, заменивших свой автомобиль и его цвет в течение 1970 года. Необходимо внести соответствующие изменения в таблицу AUTO.
    create or replace table upgrade_auto (id int, mod_auto char (20), color char (10));
    
    insert into upgrade_auto (id, mod_auto, color) values (10, 'FORD', 'BLACK');
    insert into upgrade_auto (id, mod_auto, color) values (5000, 'MASDA', 'RED');
    
    MERGE INTO AUTO
    USING upgrade_auto as src ON (auto.personid=src.id and auto.year=70)
     WHEN MATCHED THEN UPDATE SET auto.model=src.mod_auto, auto.color=src.color;
  5. В случае указания < слияния с сопоставлением > выполняется корректировка значениями из < исходной таблицы > всех строк < целевой таблицы >, которые удовлетворяют < условию слияния >.

  6. Обновляемые столбцы в < целевой таблице > не должны быть автогенерируемыми столбцами (последовательностями, autoinc, autoinc range и т.п.).

  7. Если при добавлении строк < исходная таблица > содержит значения для вставки в автогенерируемые столбцы < целевой таблицы >, то фиксируется ошибочный код завершения, точно так же, как и при выполнении соответствующего INSERT. Чтобы избежать этого, можно, как и для INSERT, либо вообще не задавать явно значения автогенерируемых столбцов, либо задавать такие значения, которые не вызовут этих проблем (большие максимального добавлявшегося значения AUTOINC, не попадающие в диапазоны AUTOINC RANGE и т.п.).

  8. < Корректировка строки > задает список имен столбцов < целевой таблицы >, которые должны быть обновлены, и значений, которые необходимо использовать для их обновления.

    Обновить список автовладельцев, которые в порядке обмена старых авто на новые заменили свои автомобили. Таблица upgrade_auto содержит идентификаторы таких автовладельцев.
    
    MERGE INTO AUTO
    USING upgrade_auto as src ON (auto.personid=src.id and)
     WHEN MATCHED THEN UPDATE SET auto.model='Калина', auto.make='АвтоВАЗ';
  9. В случае указания < слияния без сопоставления > выполняется вставка всех строк из < исходной таблицы > в < целевую таблицу >, для которых не выполнено < условие слияния >.

  10. < Вставка строки > задает < список столбцов > < целевой таблицы >, в которые должны быть вставлены значения из < списка значений > < исходной таблицы >.

    Добавить в таблицу AUTO информацию о новых автовладельцах.
    
    MERGE INTO AUTO
    USING upgrade_auto as src ON (auto.personid=src.id)
     WHEN NOT MATCHED THEN INSERT (auto.personid, auto.model, auto.color) VALUES (src.id, src.mod_auto, src.color);
  11. На все операции вставки или обновления, указанные применительно к целевой таблице распространяются все ограничения, определенные для этой таблицы, включая все каскадные ограничения ссылочной целостности.

  12. Для каждой операции вставки и обновления запускаются все соответствующие триггеры, определенные для целевой таблицы. Очередность запуска триггеров устанавливается ядром СУБД.

  13. Если в < целевой таблице > определены триггеры INSTEAD OF UPDATE или INSTEAD OF INSERT, то операции обновления или добавления строк не выполняются. Вместо этого запускаются соответствующие триггеры.

  14. Чтобы полученные результаты были гарантированно верными, должны быть правильно указаны условия поиска, которые используются для сопоставления исходных и целевых строк. Рекомендуется придерживаться следующих правил:

    • укажите в предложении ON < условие слияния > только те условия поиска, которые определяют критерий совпадения данных в исходных и целевых таблицах. То есть необходимо указать только те столбцы целевой таблицы, которые сравниваются с соответствующими столбцами исходной таблицы;

    • не включайте сравнения с другими значениями, такими, как константа;

    • для фильтрации данных используйте представления.

Примеры

  1. Одновременная корректировка и вставка записей.

    MERGE INTO AUTO
    USING upgrade_auto as src ON (auto.personid=src.id and)
     WHEN MATCHED THEN UPDATE SET auto.model='Калина', auto.make='АвтоВАЗ'
     WHEN NOT MATCHED THEN INSERT (auto.personid, auto.model, auto.color) VALUES (src.id, src.mod_auto, src.color);
  2. Использование вместо исходной таблицы подзапросов.

    2.1 Обновить информацию только о пользователе c идентификатором 10 и 11 (без создания исходной таблицы).

    MERGE INTO AUTO
    USING (SELECT 10 as id, 'HONDA'as mod_auto, 'GREEN'as color
            UNION 
           SELECT 11 as id, 'BEANTLY' as mod_auto, 'RED'as color ) as src
      ON (auto.personid=src.id)
    WHEN MATCHED THEN UPDATE SET auto.model='Калина', auto.make='АвтоВАЗ'
    WHEN NOT MATCHED THEN INSERT (auto.personid, auto.model, auto.color) VALUES (src.id, src.mod_auto, src.color);

    2.2 Все владельцы автомобилей марки MUSTANG BOSS 351 белого цвета, проживающие в городе SAN FRANCISCO, перекрасили свои автомобили в черный цвет. Отразить этот факт в таблице AUTO.

    a) составляем представление – список всех жителей города SAN FRANCISCO, владеющих автомобилем марки MUSTANG BOSS 351 белого цвета:

    create or replace view ListAutoUser as 
    select person.personid, person.city, auto.model  from auto, person
     where person.personid=auto.personid
      and person.city='SAN FRANCISCO'
      and auto.model='MUSTANG BOSS 351'
      and auto.color='WHITE';

    б) вносим изменение в таблицу AUTO

    MERGE INTO AUTO
    USING (select personid as id  from ListAutoUser) as src
       ON (auto.personid=src.id)
     WHEN MATCHED THEN UPDATE SET auto.color='BLACK';
  3. Пусть есть таблица T с двумя целочисленными столбцами I и J:

     I           J
     -           -
    |          1|          1|
    |          2|          1|
    |          3|          3|
    |          4|          4|

    Выполняем слияние этой таблицы с собой:

    merge into T using T as A on T.I = A.J
     when matched then update set T.J = T.J + 1
     when not matched then insert (T.I, T.J) values (0, 0);

    Результат:

     I           J
     -           -
    |          1|          3|
    |          2|          1|
    |          3|          4|
    |          4|          5|

    Добавление новых строк не произошло, т.к. для каждой строки исходной таблицы A (неважно, что она та же, что и целевая таблица) нашлась строка из T, в которой T.I = A.J. Именно поэтому при выполнении запроса всегда выполняется конструкция when matched then, т.е. всегда только корректировка.

    К первой строке таблицы значение прибавлено дважды, т.к. для двух строк таблицы A (первая и вторая строка) в T нашлась одна строка (первая), для которой T.I = A.J, поэтому для этой первой строки замена производилась дважды.

  4. Добавление в таблицу новых строк или корректировка существующих без использования исходной таблицы.

    create or replace table "Count of children" (FIO char(15), total int);
    
    MERGE INTO "Count of children" AS child
    USING (VALUES('Иванов И. И.' FIO, 1 total)) AS details ON (child.FIO = details.FIO)
     WHEN MATCHED THEN UPDATE SET child.total = child.total + details.total
     WHEN NOT MATCHED THEN INSERT (child.FIO, child.total) VALUES (details.FIO, details.total);
  5. В разделе INSERT, UPDATE имя целевой таблицы перед именем столбцов можно не указывать. Выше приведенный запрос можно написать так:

    MERGE INTO "Count of children" AS child
    USING (VALUES('Иванов И. И.' FIO, 1 total)) AS details ON (child.FIO = details.FIO)
     WHEN MATCHED THEN UPDATE SET total = total + details.total
     WHEN NOT MATCHED THEN INSERT (FIO, total) VALUES (details.FIO, details.total);