Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 200/545
Пример 25: использование условий при модификации данных
|
Ожидаемый результат 2.3.5.c. |
|
|
|
|
s_id |
s_name |
|
1 |
Иванов И.И. [0] [GREEN] |
|
2 |
Петров П.П. [0] [GREEN] [0] |
|
3 |
Сидоров С.С. [3] [YELLOW] |
|
4 |
Сидоров С.С. [4] [YELLOW] |
|
Решения всех задач данного примера построены на использовании выражения CASE, позволяющего в рамках одного запроса учитывать несколько вариантов данных или несколько вариантов поведения СУБД.
|
|
Решение 2.3.5.a{200}. |
|
|
|
MySQL |
Решение 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 201/545
Пример 25: использование условий при модификации данных
Решение для MS SQL Server реализуется аналогичным образом:
MS SQL |
Решение 2.3.5.a |
|
1 |
INSERT INTO [subscriptions] |
|
2 |
|
( |
3 |
|
[sb_subscriber], |
4 |
|
[sb_book], |
5 |
|
[sb_start], |
6 |
|
[sb_finish], |
7 |
|
[sb_is_active] |
8 |
|
) |
9 |
|
VALUES |
10 |
|
( |
11 |
|
4, |
12 |
|
2, |
13 |
|
CONVERT(date, '2015-02-01'), |
14 |
|
CONVERT(date, '2015-07-20'), |
15 |
|
CASE |
16 |
|
WHEN CONVERT(date, GETDATE()) < CONVERT(date, '2015-07-20') |
17 |
|
THEN (SELECT N'N') |
18 |
|
ELSE (SELECT N'Y') |
19 |
|
END |
20 |
|
), |
21 |
|
( |
22 |
|
4, |
23 |
|
3, |
24 |
|
CONVERT(date, '2015-02-01'), |
25 |
|
CONVERT(date, '2015-07-20'), |
26 |
|
CASE |
27 |
|
WHEN CONVERT(date, GETDATE()) < CONVERT(date, '2015-07-20') |
28 |
|
THEN (SELECT N'N') |
29 |
|
ELSE (SELECT N'Y') |
30 |
|
END |
31 |
|
) |
Как и в MySQL, в MS SQL Server можно использовать синтаксис вида THEN 'N' вместо THEN (SELECT 'N').
Решение для Oracle реализуется аналогичным образом.
Как и в MySQL, и в MS SQL Server, в Oracle можно использовать синтаксис вида THEN 'N' вместо THEN (SELECT 'N' FROM "DUAL").
В данной конкретной задаче нет принципиальной разницы в использовании синтаксиса вида THEN 'N' или вида THEN (SELECT 'N') — оптимизатор запросов в СУБД всё равно «поймёт», что выбирается константа, и заменит её значением всё выражение SELECT.
Но гораздо чаще потребуется не выбирать значение константы, а выполнять полноценный запрос — именно потому для сохранения единого стиля рекомендуется и в этом простом случае писать THEN (SELECT 'N').
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 202/545
Пример 25: использование условий при модификации данных
Oracle Решение 2.3.5.a
1INSERT ALL
2INTO "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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 203/545
Пример 25: использование условий при модификации данных
MySQL |
|
Решение 2.3.5.b |
|
|
||
1 |
UPDATE |
`subscriptions` AS `ext` |
|
|
||
2 |
SET |
|
`sb_finish` = CASE |
|
|
|
3 |
|
|
|
WHEN `sb_is_active` = 'Y' |
|
|
4 |
|
|
|
AND EXISTS (SELECT |
`int`.`sb_subscriber` |
|
5 |
|
|
|
FROM |
(SELECT `sb_subscriber`, |
|
6 |
|
|
|
|
|
`sb_book`, |
7 |
|
|
|
|
|
`sb_is_active` |
8 |
|
|
|
|
FROM |
`subscriptions`) AS `int` |
9 |
|
|
|
WHERE |
`int`.`sb_is_active` = 'Y' |
|
10 |
|
|
|
|
AND `int`.`sb_subscriber` = |
|
11 |
|
|
|
|
`ext`.`sb_subscriber` |
|
12 |
|
|
|
GROUP |
BY `int`.`sb_subscriber` |
|
13 |
|
|
|
HAVING |
COUNT(`int`.`sb_book`) > 2) |
|
14 |
|
|
|
THEN (SELECT DATE_ADD(`sb_finish`, INTERVAL 2 MONTH)) |
||
15 |
|
|
|
ELSE (SELECT DATE_ADD(`sb_finish`, INTERVAL 1 MONTH)) |
||
16 |
|
|
|
END |
|
|
Составное условие, являющееся ядром решения поставленной задачи, представлено в строках 3-13.
Его первая часть (строка 3) проста: мы определяем значение поля.
Его вторая часть (строки 4-13) представляет собой коррелирующий подза-
прос, результаты которого передаются в функцию EXISTS, т.е. нас интересует сам факт того, вернул ли подзапрос хотя бы одну строку, или нет.
Необходимость ещё одного вложенного запроса в строках 5-8 только что была рассмотрена — так мы обходим ограничение MySQL на чтение из обновляемой таблицы.
В остальном — это классический коррелирующий подзапрос. Если выполнить его отдельно, вручную подставляя значения идентификатора читателя, получится следующая картина:
MySQL |
|
Решение 2.3.5.b (модифицированный фрагмент) |
|||
|
|
|
|
||
1 |
SELECT |
`int`.`sb_subscriber` |
|
||
2 |
FROM |
(SELECT |
`sb_subscriber`, |
|
|
3 |
|
|
|
`sb_book`, |
|
4 |
|
|
|
`sb_is_active` |
|
5 |
|
|
FROM |
`subscriptions`) |
AS `int` |
6 |
WHERE |
`int`.`sb_is_active` = 'Y' |
|||
7 |
|
|
AND `int`.`sb_subscriber` = {id читателя из основной части запроса} |
||
8 |
GROUP |
BY `int`.`sb_subscriber` |
|
||
9 |
HAVING |
COUNT(`int`.`sb_book`) > |
2 |
||
Для читателей с идентификаторами 1 и 2 этот подзапрос вернёт ноль строк, а для читателей с идентификаторами 3 и 4 результат будет непустым.
Строки 14 и 15 описывают желаемое поведение СУБД в случае, когда составное условие соответственно выполнилось и не выполнилось.
MS SQL |
|
Решение 2.3.5.b |
|
||
1 |
UPDATE |
[subscriptions] |
|
||
2 |
SET |
|
[sb_finish] = CASE |
|
|
3 |
|
|
|
WHEN [sb_is_active] = 'Y' |
|
4 |
|
|
|
AND EXISTS (SELECT |
[int].[sb_subscriber] |
5 |
|
|
|
FROM |
[subscriptions] AS [int] |
6 |
|
|
|
WHERE [int].[sb_is_active] = 'Y' |
|
7 |
|
|
|
AND [int].[sb_subscriber] = |
|
8 |
|
|
|
|
[subscriptions].[sb_subscriber] |
9 |
|
|
|
GROUP |
BY [int].[sb_subscriber] |
10 |
|
|
|
HAVING COUNT([int].[sb_book]) > 2) |
|
11 |
|
|
|
THEN (SELECT DATEADD(month, 2, [sb_finish])) |
|
12 |
|
|
|
ELSE (SELECT DATEADD(month, 1, [sb_finish])) |
|
13 |
|
|
|
END |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 204/545