Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 24: слияние данных

2.3.4. Пример 24: слияние данных

1Поэзия [OLD]

2Программирование [OLD]

3Психология [OLD]

4Наука [OLD]

5Классика [OLD]

6Фантастика [OLD]

Иещё 94 строки со случайными именами жанров из «Большой библиотеки»

Решение 2.3.4.a{195}.

Во всех трёх СУБД на поле g_name таблицы genres построен уникальный индекс, чтобы исключить возможность появления одноимённых жанров.

Эту задачу можно решить, последовательно выполнив три INSERT-запроса, отслеживая их результаты (для вставки жанра «Классика» запрос завершится ошибкой). Но можно реализовать и более элегантное решение.

MySQL поддерживает оператор REPLACE, который работает аналогично INSERT, но учитывает значения первичного ключа и уникальных индексов, добавляя новую строку, если совпадений нет, и обновляя имеющуюся, если совпадение есть.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 195/545

Пример 24: слияние данных

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

К сожалению, MS SQL Server и Oracle не поддерживают оператор REPLACE,

но аналогичного поведения можно добиться с помощью оператора MERGE.

MS SQL Решение 2.3.4.a

1MERGE INTO [genres]

2USING ( VALUES (N'Философия'),

5ON [genres].[g_name] = [new_genres].[g_name]

6WHEN NOT MATCHED BY TARGET THEN

7INSERT ([g_name])

8VALUES ([new_genres].[g_name]);

Вэтом запросе строки 2-4 представляют собой нетривиальный способ динамического создания таблицы, т.к. оператор MERGE не может получать «на вход» ни-

чего, кроме таблиц и условия их слияния.

Строка 5 описывает проверяемое условие (совпадение имени нового жанра с именем уже существующего), а строки 6-8 предписывают выполнить вставку данных в целевую таблицу только в том случае, когда условие не выполнилось (т.е. дублирования нет).

Обратите внимание на то, что в конце строки 8 присутствует символ ;. MS SQL Server требует его наличия в конце оператора MERGE.

Oracle Решение 2.3.4.a

1MERGE INTO "genres"

2USING (SELECT ( N'Философия' ) AS "g_name"

4UNION

5SELECT ( N'Детектив' ) AS "g_name"

7UNION

8SELECT ( N'Классика' ) AS "g_name"

10ON ( "genres"."g_name" = "new_genres"."g_name" )

11WHEN NOT MATCHED THEN

12INSERT ("g_name")

13VALUES ("new_genres"."g_name")

Решение для Oracle построено по той же логике, что и решение для MS SQL Server. Отличие состоит только в способе динамического формирования таблицы (строки 2-9) из новых данных.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 196/545

Пример 24: слияние данных

Решение 2.3.4.b{195}.

Для решения этой задачи в MySQL вам нужно установить соединение с СУБД от имени пользователя, имеющего права на работу с обеими базами данных (предположим, что «Библиотека» у вас называется `library`, а «Большая библиотека»

— `huge_library`).

Далее остаётся воспользоваться вполне классическим INSERT ... SELECT (позволяет вставить в таблицу данные, полученные в результате выполнения запроса; строки 1-8), а условие задачи о добавлении слова « [OLD]» реализовать через специальный синтаксис ON DUPLICATE KEY UPDATE (строки 9-11), предписывающий MySQL выполнять обновление записи в целевой таблице, если возникает ситуация дублирования по первичному ключу или уникальному индексу между уже существующими и добавляемыми данными.

6SELECT `g_id`,

7`g_name`

8 FROM `huge_library`.`genres`

9ON DUPLICATE KEY

10UPDATE `library`.`genres`.`g_name` =

11CONCAT(`library`.`genres`.`g_name`, ' [OLD]')

Для решения этой задачи в MS SQL Server (как и в случае с MySQL) вам нужно установить соединение с СУБД от имени пользователя, имеющего права на работу с обеими базами данных (предположим, что «Библиотека» у вас называется

[library], а «Большая библиотека» — [huge_library]).

MS SQL Server не поддерживает ON DUPLICATE KEY UPDATE, но аналогичного поведения можно добиться с использованием MERGE.

MS SQL Решение 2.3.4.b

1-- Разрешение вставки явно переданных значений в IDENTITY-поле:

2SET IDENTITY_INSERT [genres] ON;

6USING [huge_library].[dbo].[genres] AS [source]

7ON [destination].[g_id] = [source].[g_id]

8WHEN MATCHED THEN

9UPDATE SET [destination].[g_name] =

11WHEN NOT MATCHED THEN

12INSERT ([g_id],

13[g_name])

14VALUES ([source].[g_id],

15[source].[g_name]);

16

17-- Запрет вставки явно переданных значений в IDENTITY-поле:

18SET IDENTITY_INSERT [genres] OFF;

Поскольку поле g_id является IDENTITY-полем, нужно явно разрешить вставку туда данных (строка 2) перед выполнением вставки, а затем запретить (строка 18).

Строки 5-7 запроса описывают таблицу-источник, таблицу-приёмник и проверяемое условие совпадения значений.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 197/545

Пример 24: слияние данных

Строки 8-10 и 11-15 описывают, соответственно, реакцию на совпадение (выполнить обновление) и несовпадение (выполнить вставку) первичных ключей таб- лицы-источника и таблицы-приёмника.

Для решения этой задачи в Oracle, (как и в случае с MySQL и MS SQL Server) вам нужно установить соединение с СУБД от имени пользователя, имеющего права на работу с обеими базами данных (предположим, что «Библиотека» у вас называ-

ется "library", а «Большая библиотека» — "huge_library").

Oracle как и MS SQL Server не поддерживает ON DUPLICATE KEY UPDATE,

но аналогичного поведения можно добиться с использованием MERGE.

Решение для Oracle аналогично решению для MS SQL Server, за исключением необходимости отключать (строка 2) перед вставкой данных триггер, отвечающий за автоинкремент первичного ключа, и снова включать его (строка 18) после вставки.

Oracle Решение 2.3.4.b

1-- Отключение триггера, обеспечивающего автоинкремент первичного ключа:

2ALTER TRIGGER "library"."TRG_genres_g_id" DISABLE;

3

4-- Слияние данных:

5MERGE INTO "library"."genres" "destination"

6USING "hude_library"."genres" "source"

7ON ("destination"."g_id" = "source"."g_id")

8WHEN MATCHED THEN

9UPDATE SET "destination"."g_name" =

10CONCAT("destination"."g_name", N' [OLD]')

11WHEN NOT MATCHED THEN

12INSERT ("g_id",

13"g_name")

14VALUES ("source"."g_id",

15"source"."g_name");

16

17-- Включение триггера, обеспечивающего автоинкремент первичного ключа:

18ALTER TRIGGER "library"."TRG_genres_g_id" ENABLE;

Если по неким причинам вы не можете соединиться с СУБД от имени пользователя, имеющего доступ к обеим интересующим вас схемам, можно пойти по следующему пути (такие варианты не были рассмотрены для MySQL и MS SQL Server, т.к. в этих СУБД нет поддержки некоторых возможностей, а сложность обходных решений выходит за рамки данной книги — на порядки проще создать пользователя с правами доступа к обеим базам данных (схемам)).

Строки 6-23 представленного ниже большого набора запросов аналогичны только что рассмотренному решению, а весь остальной код (строки 1-4, 25-37) нужен для того, чтобы обеспечить возможность одновременного доступа к данным в двух разных схемах.

Строки 2-4 отвечают за создание и открытие соединения к схеме-источнику.

Встроке 26 команда COMMIT нужна потому, что без неё при закрытии соединения мы рискуем потерять часть данных.

Встроках 29 и 32-34 представлены два варианта закрытия соединения. Как правило, срабатывает первые вариант, но если не сработал — есть второй.

Встроке 37 ранее созданное соединение уничтожается.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 198/545

Пример 24: слияние данных

Oracle Решение 2.3.4.b (работа от имени двух пользователей)

1-- Создание соединения со схемой-источником:

2CREATE DATABASE LINK "huge"

3CONNECT TO "логин" IDENTIFIED BY "пароль"

4USING 'localhost:1521/xe';

5

6-- Отключение триггера, обеспечивающего автоинкремент первичного ключа:

7ALTER TRIGGER "TRG_genres_g_id" DISABLE;

8

9-- Слияние данных:

10MERGE INTO "genres" "destination"

11USING "genres"@"huge" "source"

12ON ("destination"."g_id" = "source"."g_id")

13WHEN MATCHED THEN

14UPDATE SET "destination"."g_name" =

16WHEN NOT MATCHED THEN

17INSERT ("g_id",

18"g_name")

19VALUES ("source"."g_id",

20"source"."g_name");

21

22-- Включение триггера, обеспечивающего автоинкремент первичного ключа:

23ALTER TRIGGER "TRG_genres_g_id" ENABLE;

24

25-- Явное подтверждение сохранения всех изменений:

26COMMIT;

27

28-- Закрытие соединения со схемой источником:

29ALTER SESSION CLOSE DATABASE LINK "huge";

30

31-- Если не помогло ALTER SESSION CLOSE ... :

32BEGIN

33DBMS_SESSION.CLOSE_DATABASE_LINK('huge');

34END;

35

36-- Удаление соединения со схемой-источником:

37DROP DATABASE LINK "huge";

Задание 2.3.4.TSK.A: добавить в базу данных жанры «Политика», «Психология», «История».

Задание 2.3.4.TSK.B: скопировать (без повторений) в базу данных «Библиотека» содержимое таблицы subscribers из базы данных «Большая библиотека»; в случае совпадения первичных ключей добавить к существующему имени читателя слово « [OLD]».

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 199/545