Merge - przypadek synchronizacja danych

Instrukcja MERGE umożliwia za jednym razem wstawianie, aktualizowanie i usuwanie wierszy w tabeli docelowej. Opiszę na poniższym przykładzie synchronizację danych przy użyciu tej instrukcji.

Konfiguracja

Tworzymy dwie tabele. Pierwsza tabela stanowi źródło informacji i jest bieżącą baza pracowników. Dane w tej tabeli mogą ulegać zmianie, pracownicy mogą być dodawani lub usuwani. Natomiast druga tabela jest typową tabelą raportową, która powinna się synchronizować do listy pracowników z tabeli źródłowej.

-- tworzenie tabel
create table emp_source (
      emp_id     number not null
    , first_name varchar2(64)
    , last_name  varchar2(64)
    , mail       varchar2(255)
    , hire_date  date
    , office     varchar2(64)
    , constraint emp_source_pk primary key ( emp_id ) enable
);

create table emp_dest (
      emp_id   number not null
    , office   varchar2(64)
    , result_1 number
    , result_2 number
    , constraint emp_dest_pk primary key ( emp_id ) enable
);
-- zasilenie tabel przykładowymi danymi
insert all
into emp_source values (1010, 'Basil','Hanagan', 'basil.hanagan@company.com', to_date('1996-05-24','YYYY-MM-DD'),'Consulting')
into emp_source values (1020, 'Anneliese','Namdar','anneliese.namdar@company.com',to_date('2001-04-09','YYYY-MM-DD'),'Consulting')
into emp_source values (1030, 'Vernita','Kulik','vernita.kulik@company.com',to_date('2002-04-01','YYYY-MM-DD'),'Consulting')
into emp_source values (1040, 'Sabine','Bonaguidi','sabine.bonaguidi@company.com',to_date('1983-01-29','YYYY-MM-DD'),'Administration')
into emp_source values (1050, 'Beata','Ladieu','beata.ladieu@company.com',to_date('1983-08-15','YYYY-MM-DD'),'Administration')
into emp_source values (1060, 'Annmarie','Esmay','annmarie.esmay@company.com',to_date('1982-12-06','YYYY-MM-DD'),'Administration')
into emp_source values (1070, 'Gisele','Limthong','gisele.limthong@company.com',to_date('1987-09-27','YYYY-MM-DD'),'Research')
into emp_source values (1080, 'Ty','Flournay','ty.flournay@company.com',to_date('1991-04-12','YYYY-MM-DD'),'Research')
into emp_source values (1090, 'Wynona','Tenda','wynona.tenda@company.com',to_date('1986-08-10','YYYY-MM-DD'),'Research')
into emp_source values (2000, 'Beau','Deerman','beau.deerman@company.com',to_date('1987-08-07','YYYY-MM-DD'),'HR')
into emp_source values (2010, 'Antoine','Applonie','antoine.applonie@company.com',to_date('1982-08-30','YYYY-MM-DD'),'HR')

into emp_dest values (1010, 1, 2 )
into emp_dest values (1020, 1, 2 )
into emp_dest values (1030, 1, 2 )
into emp_dest values (1070, 1, 2 )
into emp_dest values (1080, 1, 2 )
into emp_dest values (1090, 1, 2 )

select 1 from dual;
-- zatwierdzamy zmiany
commit;
-- wyniki
select * from emp_source;
select * from emp_dest;

Wstawianie, aktualizowanie

Abu synchronizować dane w tabeli emp_dest musimy zaktualizować istniejące rekordy oraz dodać nowe. Uruchamiamy typowy skrypt z MERGE.

merge into emp_dest d
using emp_source s on (s.emp_id = d.emp_id)
when matched then 
    update set
    d.office = s.office
when not matched then
    insert values (s.emp_id, s.office, null, null)
; 

select * from emp_dest;

Super, dane zostały zaktualizowane, nowe rekordy dodane. Niestety to nie jest pełna synchronizacja. Co będzie jeżeli z tabeli źródłowej emp_source zostanie usunięty jakiś rekord? Czy możemy się zsynchronizować dane przy pomocy instrukcji MERGE?

Jeżeli popatrzymy na definicję, to klauzula DELETE, dotyczy wierszy w tabeli docelowej, które są aktualizowane przez operację scalania.

Super, czyli musimy odpowiednio zmodyfikować nasze zapytanie tak, aby wiedzieć które wiersze są tylko w tabeli docelowej i nie występują w tabeli źródłowej (ponieważ mamy je usunąć). Musimy wobec tego połączyć dane z obu tabel, oznaczyć je w jakiś sposób oraz wybrać te które maja być zaktualizowane i usunięte.

select emp_id    emp_id
     , any_value(office) keep (dense_rank last order by source_id)  as office
     , max(source_id) as  source_id
from ( select emp_id
            , office
            , 1 as source_id
       from emp_dest
       union all
       select emp_id
            , office
            , 2 as source_id
       from emp_source
)
group by emp_id

O co chodzi w tym zapytaniu?

Może najpierw dla czytelności zaktualizujmy jeden rekord i usuńmy inny rekord z tabeli źródłowej

update emp_source set office = 'Science' where emp_id = 2010;

delete emp_source where emp_id = 1090;

i wykonajmy nasz powyższy SELECT:

W SQL oznaczyłem numerami źródła danych (1 - tabela docelowa emp_dest, 2 - tabela źródłowa emp_source). W uzyskanym wyniku zapytania rekordy z SOURCE_ID=2 oznaczają, że rekord o danym EMP_ID jest w obu tabelach, natomiast z SOURCE_ID=1 oznaczają, że rekord o tym EMP_ID jest tylko w tabeli emp_dest. W kolumnie OFFICE mamy tylko wartości z tabeli emp_source (decyduje o tym KEEP). Czyli mamy to o co nam chodziło.

Pełna instrukcja do synchronizacji danych:

merge into emp_dest d
using (
    select emp_id    emp_id
         , any_value(office) keep (dense_rank last order by source_id)  as office
         , max(source_id) as  source_id
    from ( select emp_id
                , office
                , 1 as source_id
           from emp_dest
           union all
           select emp_id
                , office
                , 2 as source_id
           from emp_source
    )
    group by emp_id
    ) s
on (s.emp_id = d.emp_id)
when matched then 
    update set d.office = s.office
    delete where s.source_id = 1    
when not matched then
    insert values (s.emp_id, s.office, null, null)

W którego wyniku otrzymujemy zsynchronizowane wyniki wg identyfikatora EMP_ID.

Wnioski

Mam nadzieję, że tym przykładem przybliżyłem jak wykorzystać MERGE do aktualizacji, wstawiania oraz usuwania danych. Sposób prosty i szybki. Należy zapamiętać, że klauzula DELETE przetwarza tylko te rekordy, które zostały zaktualizowane w klauzuli UPDATE.