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

Пример 36: выборка и модификация данных с использованием хранимых функций

Для проверки корректности полученного решения нужно использовать следующий запрос:

Oracle Решение 5.1.1.a (проверка работоспособности)

1SELECT "sb_id",

2"sb_start",

3"sb_finish",

4READ_DURATION_AND_STATUS("sb_start", "sb_finish") AS "rdns"

На этом решение данной задачи завершено.

Решение 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 Стр: 355/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.b

1DROP 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;

8DECLARE free_keys_string VARCHAR(21845) DEFAULT '';

9DECLARE free_keys_cursor CURSOR FOR

10SELECT `start`,

11`stop`

22) AS `data`

23WHERE `stop` >= `start`

24ORDER BY `start`,

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

36SET free_keys_string = CONCAT(free_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 Стр: 356/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 Стр: 357/545

Пример 36: выборка и модификация данных с использованием хранимых функций

Если выполнить этот запрос без условия WHERE (строка 23), мы получим следующий набор данных (серым фоном отмечены «ложные срабатывания», от которых как раз и позволяет избавиться условие WHERE):

Получить результат рабоы функции нможно следующим запросом.

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]

22) AS [data]

23WHERE [stop] >= [start]

24ORDER BY [start],

26RETURN

27END;

28GO

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

Пример 36: выборка и модификация данных с использованием хранимых функций

Получить результат рабоы функции нможно следующим запросом.

MS SQL Решение 5.1.1.b (запрос для получения результата работы функции)

1 SELECT * FROM GET_FREE_KEYS_IN_SUBSCRIPTIONS()

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

MS SQL Решение 5.1.1.b (второй вариант решения)

1CREATE FUNCTION GET_FREE_KEYS_IN_SUBSCRIPTIONS()

2RETURNS @free_keys TABLE

3(

4[key] INT

5)

6AS

7BEGIN

8DECLARE @start_value INT;

9DECLARE @stop_value INT;

10DECLARE free_keys_cursor CURSOR LOCAL FAST_FORWARD FOR

11SELECT [start],

12[stop]

23) AS [data]

24WHERE [stop] >= [start]

25ORDER BY [start],

28OPEN free_keys_cursor;

29FETCH NEXT FROM free_keys_cursor INTO @start_value, @stop_value;

30WHILE @@FETCH_STATUS = 0

31BEGIN

32WHILE @start_value <= @stop_value

33BEGIN

34INSERT INTO @free_keys([key]) VALUES (@start_value);

35SET @start_value = @start_value + 1;

36END;

37FETCH NEXT FROM free_keys_cursor INTO @start_value, @stop_value;

38END;

39CLOSE free_keys_cursor;

40DEALLOCATE free_keys_cursor;

41

42RETURN

43END;

44GO

Для получения результата работы функции здесь, как и в первом варианте решения, необходимо выполнить запрос следующего вида.

MS SQL Решение 5.1.1.b (запрос для получения результата работы функции)

1 SELECT * FROM GET_FREE_KEYS_IN_SUBSCRIPTIONS()

И, наконец, реализуем третий вариант решения, который полностью повторяет логику решения для MySQL.

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