Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 36: выборка и модификация данных с использованием хранимых функций
Решение для MS SQL Server:
MS SQL I Решение 5.1.1.a |
1 |
CREATE FUNCTION READ DURATION AND STATUS @start date DATE, |
2 |
@finish date DATE) |
3RETURNS NVARCHAR 150)
4WITH SCHEMABINDING
5AS
6BEGIN
7DECLARE @days INT;
8DECLARE @message NVARCHAR(150 ;
10SET @days = DATEDIFF(day, @start date @finish date);
11SET @message =
12CASE
13WHEN @days 10) THEN ' OK'
14 WHEN ( @days 10 AND @days 30)) THEN ' NOTICE'
15WHEN @days 30) THEN ' WARNING'
16END;
17
18RETURN CONCAT(@days, @message ;
19END;
20GO
Для проверки корректности полученного решения нужно использовать следующий запрос (обратите внимание на необходимость обращения к функции по её полному имени, включающему имя схемы):
MS SQL Решение 5.1.1.a (проверка работоспособности)
1SELECT [sb id],
2[sb start],
3[sb finish],
4 |
|
dbo.READ DURATION AND STATUS([sb start] [sb finish]) AS [rdns] |
5 |
FROM |
[subscriptions] |
6 |
WHERE |
[sb is active] = 'Y' |
|
Решение для Oracle: |
|
|
|||
Oracl |
і |
Решение 5.1.1.a |
I |
|
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
CREATE FUNCTION READ DURATION AND STATUS start date IN DATE, |
|||||
2 |
|
|
|
|
|
finish date IN DATE) |
3 |
RETURN NVARCHAR2 |
|
|
|||
4 |
DETERMINISTIC |
|
|
|
||
5 |
IS |
|
|
|
|
|
6 |
days NUMBER 10 |
; |
|
|
||
7 |
message NVARCHAR2(150 ; |
|
|
|||
8 |
BEGIN |
|
|
|
|
|
9 |
SELECT |
finish date - start date) INTO days FROM dual |
||||
10 |
SELECT CASE |
|
|
|
||
11 |
|
|
WHEN (days 10 |
THEN ' OK' |
|
|
12 |
|
|
WHEN ((days>=10) AND (days |
30)) THEN ' NOTICE' |
||
13 |
|
|
WHEN (days 30 |
THEN ' WARNING' |
||
14 |
|
|
END |
|
|
|
15 |
INTO message FROM dual |
|
|
|||
16 |
|
|
|
|
|
|
17 |
RETURN CONCAT(days, message); |
|
||||
18 |
END; |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 375/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Для проверки корректности полученного решения нужно использовать следующий запрос:
|
Oracl |
і |
Решение 5.1.1.a (проверка работоспособности) |
| |
|
||
e |
|
|
|||||
|
|
|
|
|
|
|
|
1 |
|
SELECT "sb id", |
|
|
|
||
2 |
|
|
|
"sb start", |
|
|
|
3 |
|
|
|
"sb finish", |
|
|
|
4 |
|
|
|
READ DURATION AND STATUS("sb start" |
"sb finish") AS "rdns" |
||
5 |
|
FROM |
|
"subscriptions" |
|
|
|
6 |
|
WHERE |
"sb is active" = |
'Y' |
|
|
|
На этом решение данной задачи завершено.
Решение 5.1.1. b{352}.
Логику решения данной задачи удобнее всего рассматривать на примере таблицы subscriptions (там есть свободные значения первичного ключа). Для большей наглядности предварительно добавим две выдачи книг — со значениями первичного ключа 200 и 202.
Посмотрим, что должно получиться.
sb_id Свободные значения
1 — 1
2
3
4 — 41
42
43 — 56
57
58 — 60
61
62
63 — 85
86
87 — 90
91
92 — 94
95
96 — 98
99
100
101 — 199
200
201 — 201
202
К сожалению, хранимые функции в MySQL на текущий момент имеют ряд жёстких ограничений, два из которых сильно влияют на решение данной задачи:
•внутри хранимых функций нельзя выполнять динамические SQL-запросы (т.е. мы не сможем передать имя таблицы, и функцию придётся жёстко привязывать к конкретной таблице БД);
•хранимые функции не могут возвращать таблицы (это ограничение частично можно обойти, возвращая множество значений в виде строки, которую затем можно будет обработать встроенными функциями MySQL, например,
FIND_IN_SET).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 376/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Таким образом, результат работы функции примет следующий вид:
1,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,
32,33,34,35,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,52,53,54,55,56,58,59,60,
63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,87,88,89,90,
92,93,94,96,97,98,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116
117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,
137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,
157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,
177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,
197,198,199,201
Итак, решение данной задачи для MySQL выглядит следующим образом.
MySQL Решение 5.1.1
1 DROP.b FUNCTION IF EXISTS GET FREE KEYS IN SUBSCRIPTIONS;
2DELIMITER $$
3CREATE FUNCTION GET FREE KEYS IN SUBSCRIPTIONS() RETURNS VARCHAR 21845
4BEGIN
5DECLARE start value INT DEFAULT 0
6 |
DECLARE |
stop |
value |
INT |
DEFAULT |
0 |
7 |
DECLARE |
done |
|
INT |
DEFAULT |
0 |
8DECLARE free keys string VARCHAR(21845) DEFAULT '';
9DECLARE free keys cursor CURSOR FOR
10SELECT 'start',
11'stop'
12 |
FROM |
(SELECT 'min t' 'sb id' + 1 |
AS 'start', |
||
13 |
|
|
(SELECT MIN('sb id') - 1 |
|
|
14 |
|
|
FROM |
'subscriptions' AS 'x' |
|
15 |
|
|
WHERE |
'x' 'sb id' > 'min t' 'sb id') AS 'stop' |
|
16 |
|
FROM |
'subscriptions' AS 'min t' |
|
|
17 |
|
UNION |
|
|
|
18 |
|
SELECT 1 |
|
AS 'start', |
|
19 |
|
|
(SELECT MIN('sb id') - 1 |
|
|
20 |
|
|
FROM |
'subscriptions' AS 'x' |
|
21 |
|
|
WHERE |
'sb id' > 0) |
AS 'stop' |
22) AS 'data'
23WHERE 'stop' >= 'start'
24ORDER BY 'start',
25 |
'stop'; |
26 |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; |
27 |
|
28OPEN free keys cursor
29BEGIN
30read loop: LOOP
31FETCH free keys cursor INTO start value, stop value
32IF done THEN
33LEAVE read loop
34END IF;
35for loop: LOOP
36 |
SET free keys string = CONCATfree keys string start value, ','); |
37SET start value := start value + 1
38IF start value <= stop value THEN
39ITERATE for loop
40END IF;
41LEAVE for loop
42END LOOP for loop
43END LOOP read loop
44END;
45
46CLOSE free keys cursor
47RETURN SUBSTRING free keys string 1, CHAR LENGTH(free keys string) - 1 ;
48END;
49$$
50DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 377/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Данная функция возвращает строку с максимальной длиной 21845 символов (предел для VARCHAR в UTF-кодировках).
Встроках 5-9 объявляются переменные:
•start_value — начало последовательности «свободных ключей»;
•stop_value — конец последовательности «свободных ключей»;
•done — признак того, что курсор выбрал все данные из результата выполнения запроса;
•free_keys_string — строка для накопления и возврата результата работы функции;
•free_keys_cursor — курсор для построкового доступа к результатам выполнения запроса, ищущего начало и конец последовательностей «свободных ключей».
Встроках 10-25 содержится запрос, являющийся «сердцем» все функции, но
кнему мы вернёмся чуть позже.
Встроке 26 объявляется обработчик ситуации NOT FOUND для курсора, объ-
явленного в строке 9 (такой обработчик срабатывает при достижении конца набора данных, или когда данных нет вообще).
Встроке 28 происходит открытие курсора, т.е. выполнение представленного
встроках 10-25 запроса и предоставление доступа к строкам результата его выполнения.
Встроках 30-43 находится цикл, выполняющийся для каждой строки результата выполнения запроса:
•в строке 31 очередной набор данных из результата выполнения запроса по-
мещается в переменные start_value и stop_value;
•в строках 32-34 проверяется, удалось ли получить данные (если не удалось
— происходит выход из цикла);
•в строках 35-42 находится вложенный цикл, представляющий собой SQL-ре- ализацию классического цикла FOR: все значения от start_value до stop_value пошагово накапливаются в строке free_keys_string;
Встроке 46 происходит закрытие курсора, и в строке 47 мы возвращаем результат работы функции (предварительно убрав последнюю запятую).
Теперь рассмотрим отдельно запрос в строках 10-25. Его суть выражена в строках 12-16:
•мы берём значение_ключа+1 (логично, что само существующее значение ключа не может быть началом диапазона «свободных ключей», потому мы делаем предположение, что такой диапазон начинается со следующего значения) — результат помещается в поле start;
•затем мы ищем минимальное значение ключа, которое больше найденного при получении значения поля start значения ключа, и вычитаем из него единицу (логично, что существующее значение не может быть концом диапазона «свободных ключей», потому мы делаем предположение, что такой диапазон заканчивается предыдущим значением) — результат помещается в поле stop;
•условие в строке 23 позволяет отличить реально существующие диапазоны «свободных значений» от ложных срабатываний (в реально существующих диапазонах верхняя граница не может быть меньше нижней).
UNION-секция в строках 17-21 нужна для обнаружения «свободных диапазонов» в начале последовательности значений первичного ключа (от 1 до первого реально существующего значения).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 378/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Если выполнить этот запрос без условия WHERE (строка 23), мы получим следующий набор данных (серым фоном отмечены «ложные срабатывания», от которых как раз и позволяет избавиться условие WHERE):
start |
stop |
1 |
1 |
3 |
2 |
4 |
41 |
43 |
56 |
58 |
60 |
62 |
61 |
63 |
85 |
87 |
90 |
92 |
94 |
96 |
98 |
100 |
99 |
101 |
199 |
201 |
201 |
203 |
NULL |
Получить результат рабоы функции нможно следующим запросом.
MySQL Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT GET FREE KEYS IN SUBSCRIPTIONS()
Итак, решение для MySQL завершено. Переходим к MS SQL Server. Логика основного запроса, а также логика работы с курсорами и циклами только что была рассмотрена, потому здесь мы не будем повторять эти пояснения.
MS SQL Server тоже не поддерживает динамический SQL внутри хранимых функций, зато здесь мы можем возвращать из хранимой функции таблицы. Благодаря этому, в первом варианте решения мы лишь «обернём» в функцию запрос, возвращающий начальные и конечные значения диапазонов свободных ключей.
MS SQL і Решение 5.1.1.b (первый вариант решения)
1CREATE FUNCTION GET FREE KEYS IN SUBSCRIPTIONS()
2RETURNS @free keys TABLE
3(
4[start] INT,
5[stop] INT
6)
7AS
8BEGIN
9INSERT @free keys
10SELECT [start],
11[stop]
12 |
FROM |
(SELECT [min t] [sb id] + 1 |
AS [start], |
||
13 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
14 |
|
|
FROM |
[subscriptions] AS [x] |
|
15 |
|
|
WHERE |
[x] [sb id] > [min t] [sb id]) AS [stop] |
|
16 |
|
FROM |
[subscriptions] AS [min t] |
|
|
17 |
|
UNION |
|
|
|
18 |
|
SELECT 1 |
|
AS [start], |
|
19 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
20 |
|
|
FROM |
[subscriptions] AS [x] |
|
21 |
|
|
WHERE |
[sb id] > 0) |
AS [stop] |
22) AS [data]
23WHERE [stop] >= [start]
24ORDER BY [start],
25 |
[stop] |
26RETURN
27END;
28GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 379/545