Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 24: слияние данных
Ora( ?le І решение 2.3.4.b (раоота от имени двух пользователей) І
1-- Создание соединения со схемой-источником:
2CREATE DATABASE LINK "huge"
3CONNECT TO "логин" IDENTIFIED BY "пароль" USING 'localhost:1521/xe';
5-- Отключение триггера, обеспечивающего автоинкремент первичного
6 |
ключа: ALTER TRIGGER "TRG_genres_g_id" |
DISABLE; |
7 |
|
|
8-- Слияние данных:
9MERGE INTO "genres" "destination"
10USING "genres"@"huge" "source"
11ON ("destination" "g_id" = "source" "g_id"
12WHEN MATCHED THEN
13UPDATE SET "destination" "g_name" =
14 |
CONCAT("destination" "g_name" N' [OLD]') |
15WHEN NOT MATCHED THEN
16INSERT "g_id",
17"g_name"
18VALUES "source" "g_id",
19"source" "g_name" ;
20
21— Включение триггера, обеспечивающего автоинкремент первичного ключа:
22ALTER TRIGGER "TRG_genres_g_id" ENABLE;
23
24 |
-- Явное подтверждение сохранения всех изменений: COMMIT; |
|
|
||
25 |
-- Закрытие соединения со схемой источником: ALTER SESSION CLOSE |
|
26 |
||
DATABASE LINK "huge"; |
||
27 |
||
|
||
28 |
— Если не помогло ALTER SESSION CLOSE ... : |
|
29 |
||
BEGIN |
||
30 |
||
DBMS_SESSION.CLOSE_DATABASE_LINK('huge'); |
||
31 |
||
END; |
||
32 |
||
|
||
33 |
-- Удаление соединения со схемой-источником: DROP DATABASE LINK |
|
34 |
||
"huge"; |
||
35 |
||
|
||
36 |
|
|
37 |
|
Задание 2.3.4.TSK.A: добавить в базу данных жанры «Политика», «Психология», «История».
Задание 2.3.4.TSK.B: скопировать (без повторений) в базу данных «Библиотека» содержимое таблицы subscribers из базы данных «Большая библиотека»; в случае совпадения первичных ключей добавить к существующему имени читателя слово « [OLD]».
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 210/545
Пример 25: использование условий при модификации данных
2.3.5. Пример 25: использование условий при модификации данных
Задача 2.3.5.a{201}: добавить в базу данных информацию о том, что читатель с идентификатором 4 взял в библиотеке книги с идентификаторами 2 и 3 1 февраля 2015-го года, и планировал вернуть их не позднее 20-го июля 2015-го года; если текущая дата меньше 20-го июля 2015-го года, отметить выдачи как невозвращённые, если больше — как возвращённые.
Задача 2.3.5.b{203}: изменить даты возврата всех книг на «два месяца от текущего дня», если книга не возвращена и у соответствующего читателя сейчас на руках больше двух книг, и на «месяц от текущего дня» в противном случае (книга возвращена или у соответствующего читателя на руках сейчас не более двух книг).
Задача 2.3.5.c{205}: обновить все имена читателей, добавив в конец в квадратных скобках количество невозвращённых книг (например, « [3]») и слова
« [RED]», « [YELLOW]», « [GREEN]», соответственно, если у читателя сейчас на руках более пяти книг, от трёх до пяти, менее трёх.
Ожидаемые результаты представлены, исходя из предположения, что вы используете «чистую» копию базы данных «Библиотека», не изменённую предыдущими примерами модификации данных, но каждая задача данного примера работает с данными, изменёнными предыдущей задачей.
Ожидаемый результат 2.3.5.a: в таблице subscriptions должны появиться две следующие записи.
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
101 |
4 |
2 |
2015-02-01 |
2015-07-20 |
Y |
102 |
4 |
3 |
2015-02-01 |
2015-07-20 |
Y |
Ожидаемый результат 2.3.5.b.
|
|
|
|
Было |
Стало |
|
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_finish |
sb_is_active |
2 |
1 |
1 |
2011-01-12 |
2011-02-12 |
2011-03-12 |
N |
3 |
3 |
3 |
2012-05-17 |
2012-07-17 |
2012-09-17 |
Y |
42 |
1 |
2 |
2012-06-11 |
2012-08-11 |
2012-09-11 |
N |
57 |
4 |
5 |
2012-06-11 |
2012-08-11 |
2012-09-11 |
N |
61 |
1 |
7 |
2014-08-03 |
2014-10-03 |
2014-11-03 |
N |
62 |
3 |
5 |
2014-08-03 |
2014-10-03 |
2014-12-03 |
Y |
86 |
3 |
1 |
2014-08-03 |
2014-09-03 |
2014-11-03 |
Y |
91 |
4 |
1 |
2015-10-07 |
2015-03-07 |
2015-05-07 |
Y |
95 |
1 |
4 |
2015-10-07 |
2015-11-07 |
2015-12-07 |
N |
99 |
4 |
4 |
2015-10-08 |
2025-11-08 |
2026-01-08 |
Y |
100 |
1 |
3 |
2011-01-12 |
2011-02-12 |
2011-03-12 |
N |
101 |
4 |
2 |
2015-02-01 |
2015-07-20 |
2015-09-20 |
Y |
102 |
4 |
3 |
2015-02-01 |
2015-07-20 |
2015-09-20 |
Y |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 211/545
Пример 25: использование условий при модификации данных
Ожидаемый результат 2.3.5.C.
s_id |
s_name |
1 |
Иванов И.И. [0] [GREEN] |
2 |
Петров П.П. [0] [GREEN] [0] |
3 |
Сидоров С.С. [3] [YELLOW] |
4 |
Сидоров С.С. [4] [YELLOW] |
Решения всех задач данного примера построены на использовании выражения CASE, позволяющего в рамках одного запроса учитывать несколько вариантов данных или несколько вариантов поведения СУБД.
'IT Решение 2.3.5.a{200}.
|
Решение 2.3.5.a |
1 |
INSERT INTO 'subscriptions' |
2 |
( |
3 |
'sb id', |
4 |
'sb subscriber', |
5 |
'sb book' |
6 |
'sb start', |
7 |
'sb finish', |
8 |
'sb is active' |
9 |
) |
10 |
VALUES |
11 |
( |
12 |
NULL, |
13 |
4 |
14 |
2 |
15 |
'2015-02-01', |
16 |
'2015-07-20', |
17 |
CASE |
18 |
WHEN CURDATE() < '2015-07-20' |
19 |
THEN (SELECT 'N') |
20 |
ELSE (SELECT 'Y') |
21 |
END |
22 |
), |
23 |
( |
24 |
NULL, |
25 |
4 |
26 |
3 |
27 |
'2015-02-01', |
28 |
'2015-07-20', |
29 |
CASE |
30 |
WHEN CURDATE() < '2015-07-20' |
31 |
THEN (SELECT 'N') |
32 |
ELSE (SELECT 'Y') |
33 |
END |
34 |
) |
В строках 17-21 и 28-33 выражение CASE позволяет на уровне СУБД опреде-
лить текущую дату, сравнить её с заданной и прийти к выводу о том, какое значение (Y или N) должно принять поле sb_is_active.
В MySQL срабатывает и упрощённый синтаксис вида THEN 'N' вместо THEN (SELECT 'N'). Это не ошибка, но такой код намного сложнее читается и воспри-
нимается, т.к. в контексте SQL привычным способом получения некоего значения является именно использование SELECT.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 212/545
Пример 25: использование условий при модификации данных |
||||
Oracl |
і |
Решение 2.3.5.a |
| |
|
e |
||||
|
|
|
||
1 |
INSERT ALL |
|
||
2 |
|
INTO "subscriptions" |
||
3 |
|
( |
|
|
4 |
|
"sb subscriber", |
||
5 |
|
"sb book" |
||
6 |
|
"sb start" |
||
7 |
|
"sb finish", |
||
8 |
|
"sb is active" |
||
9 |
|
) |
|
|
10 |
|
VALUES |
||
11 |
|
( |
|
|
12 |
|
4 |
|
|
13 |
|
2 |
|
|
14 |
|
TO DATE('2015-02-01', 'YYYY-MM-DD'), |
||
15 |
|
TO DATE('2015-07-20', 'YYYY-MM-DD'), |
||
16 |
|
CASE |
||
17 |
|
WHEN TRUNC(SYSDATE) < TO DATE('2015-07-20', 'YYYY-MM-DD') |
||
18 |
|
THEN (SELECT 'N' FROM "DUAL" |
||
19 |
|
ELSE (SELECT 'Y' FROM "DUAL" |
||
20 |
|
END |
|
|
21 |
|
) |
|
|
22 |
|
INTO "subscriptions" |
||
23 |
|
( |
|
|
24 |
|
"sb subscriber" |
||
25 |
|
"sb book" |
||
26 |
|
"sb start", |
||
27 |
|
"sb finish", |
||
28 |
|
"sb is active" |
||
29 |
|
) |
|
|
30 |
|
VALUES |
||
31 |
|
( |
|
|
32 |
|
4 |
|
|
33 |
|
3 |
|
|
34 |
|
TO DATE('2015-02-01', 'YYYY-MM-DD'), |
||
35 |
|
TO DATE('2015-07-20', 'YYYY-MM-DD'), |
||
36 |
|
CASE |
||
37 |
|
WHEN TRUNC(SYSDATE) < TO DATE('2015-07-20', 'YYYY-MM-DD') |
||
38 |
|
THEN (SELECT 'N' FROM "DUAL" |
||
39 |
|
ELSE (SELECT 'Y' FROM "DUAL" |
||
40 |
|
END |
|
|
41 |
|
) |
|
|
42 |
SELECT 1 FROM "DUAL"; |
|||
•Ч |
|
Решение 2.3.5.b{200}. |
||
В отличие от предыдущей задачи, где условие было крайне тривиальным и наглядным, здесь придётся решать две проблемы:
•Создать достаточно сложное составное условие, опирающееся на коррелирующий подзапрос.
•«Убедить» MySQL разрешить использование в одном запросе одной и той же таблицы (subscriptions) как для обновления, так и для чтения — MySQL запрещает такие ситуации.
За решение второй проблемы отвечают строки 5-8 запроса: мы «оборачиваем» операцию чтения из обновляемой таблицы в подзапрос, что позволяет обойти налагаемое MySQL ограничение. Это довольно распространённое, но в то же время опасное решение, т.к. оно не только снижает производительность, но и потенциально может привести к некорректной работе некоторых запросов — причём универсального ответа на вопрос «сработает или нет» не существует: нужно проверять в конкретной ситуации.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 214/545