Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 38: выполнение динамических запросов с помощью хранимых процедур
5.2. Использование хранимых процедур
5.2.1.Пример 38: выполнение динамических запросов с помощью хранимых процедур
Задача 5.2.1.a{375}: создать хранимую процедуру, устраняющую промежутки в последовательности значений первичного ключа для заданной таблицы (например, если значения первичного ключа были равны 4, 7, 9, то после выполнения хранимой процедуры они станут равны 1, 2, 3).
Задача 5.2.1.b{382}: создать хранимую процедуру, формирующую список представлений, триггеров и внешних ключей для указанной таблицы.
Ожидаемый результат 5.2.1.a.
После выполнения хранимой процедуры, в которую первыми двумя параметрами передано имя обрабатываемой таблицы и её первичного ключа, значения первичного ключа в таблице принимают вид 1, 2, 3, … (т.е. начинаются с 1 и идут без пропусков), а сама хранимая процедура возвращает информацию о том, сколько значений первичного ключа было изменено.
Ожидаемый результат 5.2.1.b.
После выполнения хранимой процедуры, в которую первым параметром передано имя обрабатываемой таблицы, формируется и возвращается результирующая таблица вида:
object_type |
object_name |
foreign_key |
FK_1 |
foreign_key |
FK_2 |
trigger |
TRG_1 |
trigger |
TRG_2 |
view |
VIEW_1 |
view |
VIEW_2 |
Решение 5.2.1.a{375}.
Традиционно начнём решение задачи с MySQL. В отличие от хранимых функций в хранимые процедуры данной СУБД позволяют формировать и выполнять динамические SQL-запросы.
Прежде, чем начать рассмотрение кода хранимой процедуры, сделаем два важных замечания:
•выполнять динамические запросы и помещать результаты их работы в переменные можно только с использованием т.н. «сессионных переменных20» (имена которых начинаются со знака @);
•имена переменных, в которые помещается результат выполнения запроса, не должны совпадать с именами параметров хранимой процедуры (и, в некоторых случаях, с именами полей, возвращаемых запросом21).
20http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference
21http://dba.stackexchange.com/questions/112285/select-into-variable-results-in-null-or-idk
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 375/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
MySQL Решение 5.2.1.a (код процедуры)
1DROP PROCEDURE COMPACT_KEYS;
2DELIMITER $$
3 |
|
|
|
4 |
|
CREATE PROCEDURE COMPACT_KEYS (IN |
table_name VARCHAR(150), |
5 |
|
IN |
pk_name VARCHAR(150), |
6 |
|
OUT keys_changed INT) |
|
7BEGIN
8SET keys_changed = 0;
9SELECT
10CONCAT('Point 1. table_name = ', table_name, ', pk_name = ',
11pk_name, ', keys_changed = ', IFNULL(keys_changed, 'NULL'));
12
13SET @empty_key_query =
14CONCAT('SELECT MIN(`empty_key`) AS `empty_key` INTO @empty_key_value
15 |
|
FROM (SELECT `left`.`', pk_name, '` |
+ |
1 |
AS `empty_key` |
|||
16 |
|
FROM `', table_name, '` AS `left` |
|
|||||
17 |
|
LEFT OUTER JOIN |
`', |
table_name, '` AS |
`right` |
|||
18 |
|
ON |
`left`.`', pk_name, |
|
||||
19 |
|
|
'` |
+ |
1 |
= |
`right`.`', |
pk_name, '` |
20WHERE `right`.`', pk_name, '` IS NULL
21UNION
22SELECT 1 AS `empty_key`
23 |
|
FROM |
`', table_name, '` |
|
|
|
24 |
|
WHERE |
NOT EXISTS(SELECT `', |
pk_name, '` |
|
|
25 |
|
|
FROM |
`', |
table_name, |
'` |
26 |
|
|
WHERE |
`', |
pk_name, '` |
= 1)) AS `prepared_data` |
27 |
|
WHERE |
`empty_key` < (SELECT MAX(`', pk_name, '`) |
|||
28 |
|
|
FROM |
|
`', table_name, '`)'); |
|
29 |
|
|
|
|
|
|
30SET @max_key_query =
31CONCAT('SELECT MAX(`', pk_name, '`)
32 |
|
INTO @max_key_value FROM `', table_name, '`'); |
33 |
|
SELECT CONCAT('Point 2. empty_key_query = ', @empty_key_query, |
34 |
|
'max_key_query = ', @max_key_query); |
35 |
|
|
36PREPARE empty_key_stmt FROM @empty_key_query;
37PREPARE max_key_stmt FROM @max_key_query;
38
39while_loop: LOOP
40EXECUTE empty_key_stmt;
41SELECT CONCAT('Point 3. @empty_key_value = ', @empty_key_value);
42
43IF (@empty_key_value IS NULL)
44THEN LEAVE while_loop;
45END IF;
46
47EXECUTE max_key_stmt;
48SET @update_key_query =
49CONCAT('UPDATE `', table_name, '` SET `', pk_name,
50'` = @empty_key_value WHERE `', pk_name, '` = ', @max_key_value);
51SELECT CONCAT('Point 4. @update_key_query = ', @update_key_query);
52
53PREPARE update_key_stmt FROM @update_key_query;
54EXECUTE update_key_stmt;
55DEALLOCATE PREPARE update_key_stmt;
56
57SET keys_changed = keys_changed + 1;
58ITERATE while_loop;
59END LOOP while_loop;
60
61DEALLOCATE PREPARE max_key_stmt;
62DEALLOCATE PREPARE empty_key_stmt;
63END;
64$$
65DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 376/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
Несмотря на общую громоздкость и кажущуюся сложность, логика работы данной процедуры очень проста. Рассмотрим её детально.
Запросы в строках 10-11, 33-34, 41 и 51 представлены исключительно для отладки и наглядности, и могут быть удалены.
Встроке 8 происходит инициализация значения выходного параметра, который будет накапливать в себе информацию о количестве изменений, внесённых в таблицу.
Встроках 13-28 на основе переданных в хранимую процедуру имён обрабатываемой таблицы и её первичного ключа формируется текст SQL-запроса, который будет искать первое свободное значение в последовательности значений первичного ключа. Рассмотрим этот запрос отдельно (на примере таблицы subscrip-
tions).
MySQL Решение 5.2.1.a (текст запроса, выполняющего поиск первого свободного значения первичного ключа)
1 |
SELECT MIN(`empty_key`) AS `empty_key` |
||||
2 |
FROM |
(SELECT |
`left`.`sb_id` + 1 AS `empty_key` |
||
3 |
|
FROM |
`subscriptions` |
AS `left` |
|
4 |
|
|
LEFT OUTER JOIN |
`subscriptions` AS `right` |
|
5 |
|
|
|
ON |
`left`.`sb_id` + 1 = `right`.`sb_id` |
6 |
|
WHERE |
`right`.`sb_id` |
IS NULL |
|
7 |
|
UNION |
|
|
|
8 |
|
SELECT |
1 AS `empty_key` |
|
|
9 |
|
FROM |
`subscriptions` |
|
|
10 |
|
WHERE |
NOT EXISTS(SELECT `sb_id` |
||
11 |
|
|
FROM |
`subscriptions` |
|
12 |
|
|
WHERE |
`sb_id` = 1)) AS `prepared_data` |
|
13 |
WHERE |
`empty_key` < (SELECT |
MAX(`sb_id`) |
||
14 |
|
|
FROM |
`subscriptions`) |
|
|
|
|
|
|
|
Основная секция запроса в строках 2-4 ищет отсутствующие значения первичного ключа, следующие сразу за реально существующими.
Дополнительная UNION-секция в строках 8-12 проверяет наличие свободных значений первичного ключа в самом начале последовательности (начиная с 1).
В строке 1 выбирается минимальное значение из набора найденных.
И, наконец, в строках 13-14 применяется условие, гарантирующее, что обнаруженное свободное значение не превышает реально существующее максимальное значение первичного ключа.
Возвращаемся к коду хранимой процедуры.
Встроках 30-32 формируется текст запроса, определяющего максимальное значение первичного ключа в обрабатываемой таблице — именно оно на каждом шаге цикла будет меняться на первое найденное свободное значение.
Встроках 36-37 на основе текстового представления SQL-запросов, в которые подставлены имена обрабатываемой таблицы и её первичного ключа, создаются исполнимые выражения.
Встроках 39-59 представлен цикл, который выполняется до тех пор, пока существует хотя бы одно свободное значение первичного ключа:
•в строке 40 происходит выполнение основного запроса, производящего поиск первого (минимального) свободного значения первичного ключа и помещающего это значение в переменную @empty_key_value;
•в строках 43-45 происходит проверка полученного значения на равенство NULL (если условие выполнено, то больше свободных значений нет, и происходит выход из цикла);
•в строке 47 выполняется запрос, помещающий текущее максимальное значение первичного ключа в переменную @max_key_value (мы вынуждены использовать промежуточную переменную, чтобы обойти ограничение MySQL
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 377/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
на использование одной и той же таблицы одновременно в UPDATE- и SELECT-частях запроса);
•в строках 48-50 формируется текстовое представления запроса на обновление значения первичного ключа;
•для этого запроса в строках 53, 54, 55 соответственно формируется исполнимое выражение, происходит выполнение запроса, исполнимое выражение освобождается (т.к. на следующем шаге цикла текст запроса уже будет иным);
•в строке 57 происходит наращивание счётчика обновлённых значений первичного ключа;
•в строке 58 происходит переход на следующую итерацию цикла.
После завершения цикла нам остаётся только освободить ранее подготов-
ленные исполнимые выражения, что и происходит в строках 61-62.
Выполнить полученную хранимую процедуру и узнать, сколько значений первичного ключа было изменено, можно следующими запросами (в первом случае будет возвращено значение 9, во втором — 0, т.к. в таблице books нет свободных значений первичного ключа).
MySQL |
Решение 5.2.1.a (код для выполнения хранимой процедуры и получения результата её работы) |
1CALL COMPACT_KEYS ('subscriptions', 'sb_id', @keys_changed);
2SELECT @keys_changed;
3
4CALL COMPACT_KEYS ('books', 'b_id', @keys_changed);
5SELECT @keys_changed;
Если рассмотреть по шагам (для таблицы subscriptions их будет девять) работу этой хранимой процедуры, получится следующая картина:
|
Исх. |
Шаг 1 |
Шаг 2 |
Шаг 3 |
Шаг 4 |
Шаг 5 |
Шаг 6 |
Шаг 7 |
Шаг 8 |
Шаг 9 |
|
2 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
Значения первичного ключа |
3 |
2 |
2 |
2 |
2 |
2 |
2 |
2 |
2 |
2 |
42 |
3 |
3 |
3 |
3 |
3 |
3 |
3 |
3 |
3 |
|
57 |
42 |
4 |
4 |
4 |
4 |
4 |
4 |
4 |
4 |
|
61 |
57 |
42 |
5 |
5 |
5 |
5 |
5 |
5 |
5 |
|
62 |
61 |
57 |
42 |
6 |
6 |
6 |
6 |
6 |
6 |
|
86 |
62 |
61 |
57 |
42 |
7 |
7 |
7 |
7 |
7 |
|
91 |
86 |
62 |
61 |
57 |
42 |
8 |
8 |
8 |
8 |
|
95 |
91 |
86 |
62 |
61 |
57 |
42 |
9 |
9 |
9 |
|
99 |
95 |
91 |
86 |
62 |
61 |
57 |
42 |
10 |
10 |
|
|
100 |
99 |
95 |
91 |
86 |
62 |
61 |
57 |
42 |
11 |
Своб. |
1 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
NULL |
Обн. |
100 |
99 |
95 |
91 |
86 |
62 |
61 |
57 |
42 |
11 |
Для дополнительного изучения логики работы представленного решения рекомендуется создать рассмотренную хранимую процедуру, не удаляя отладочный вывод, и проследить на его основе логику работы и изменения значения переменных внутри процедуры.
На этом решение для MySQL завершено.
Переходим к MS SQL Server. Общая логика решения будет очень похожа на только что рассмотренное для MySQL за исключением одной непреодолимой проблемы: MS SQL Server не позволяет обновлять IDENTITY-поля в таблице (включение IDENTITY_INSERT позволяет лишь вставлять значения в такие поля, но не обновлять их).
Мы могли бы обойти это ограничение через удаление старого ряда таблицы и вставку нового (с подменённым значением первичного ключа), но такое решение
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 378/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
приведёт к фатальным последствиям, если на модифицируемый первичный ключ ссылаются внешние ключи других таблиц.
Никакого простого универсального решения для отключения и повторного включения IDENTITY-свойства у поля средствами SQL-запросов нет. Единственный более-менее доступный вариант — это выключение и повторное включение этого свойства через графический интерфейс MS SQL Server Management Studio.
Таким образом, в нашей хранимой процедуре мы будем проверять, является ли предназначенное для обработки поле IDENTITY-полем, и запрещать выполнение операции, если является (строки 14-22 кода хранимой процедуры).
MS SQL Решение 5.2.1.a (код процедуры)
1 |
|
CREATE PROCEDURE COMPACT_KEYS |
2 |
|
@table_name NVARCHAR(150), |
3 |
|
@pk_name NVARCHAR(150), |
4 |
|
@keys_changed INT OUTPUT |
5WITH EXECUTE AS OWNER
6AS
7DECLARE @empty_key_query NVARCHAR(1000) = '';
8DECLARE @max_key_query NVARCHAR(1000) = '';
9DECLARE @empty_key_value INT = NULL;
10DECLARE @max_key_value INT = NULL;
11DECLARE @update_key_query NVARCHAR(1000) = '';
12DECLARE @error_message NVARCHAR(1000) = '';
13
14IF (COLUMNPROPERTY(OBJECT_ID(@table_name), @pk_name, 'IsIdentity') = 1)
15BEGIN
16SET @keys_changed = -1;
17SET @error_message = CONCAT('Remove identity property for column [',
18 |
|
@pk_name,'] of table |
[', @table_name, |
19 |
|
'] via MS SQL Server |
Management Studio.'); |
20RAISERROR (@error_message, 16, 1);
21RETURN -1;
22END;
23 |
|
|
24 |
|
SET @keys_changed = 0; |
25 |
|
|
26PRINT(CONCAT('Point 1. @table_name = ', @table_name, ', @pk_name = ',
27@pk_name, ', @keys_changed = ', ISNULL(@keys_changed, 'NULL')));
28
29SET @empty_key_query =
30CONCAT('SET @empty_k_v = (SELECT MIN([empty_key]) AS [empty_key]
31 |
|
FROM |
(SELECT [left].[', @pk_name, '] + 1 |
AS [empty_key] |
|
32 |
|
|
FROM [', @table_name, '] AS [left] |
||
33 |
|
|
LEFT OUTER JOIN [', @table_name, '] AS [right] |
||
34 |
|
|
|
ON [left].[', |
@pk_name, |
35 |
|
|
|
'] + 1 = [right].[', @pk_name, '] |
|
36 |
|
WHERE |
[right].[', @pk_name, '] IS NULL |
|
|
37 |
|
UNION |
|
|
|
38 |
|
SELECT |
1 AS [empty_key] |
|
|
39 |
|
FROM |
[', @table_name, '] |
|
|
40 |
|
WHERE |
NOT EXISTS(SELECT [', @pk_name, '] |
|
|
|
|
|
|
|
|
41 |
|
|
FROM |
[', @table_name, |
'] |
42 |
|
|
WHERE |
[', @pk_name, '] |
= 1) |
43) AS [prepared_data]
44WHERE [empty_key] < (SELECT MAX([', @pk_name, '])
45 |
|
FROM [', @table_name, ']))'); |
46 |
|
|
|
|
|
47SET @max_key_query =
48CONCAT('SET @max_k_v = (SELECT MAX([', @pk_name, ']) FROM [',
49 |
|
@table_name, '])'); |
50 |
|
|
51 |
|
PRINT(CONCAT('Point 2. @empty_key_query = ', @empty_key_query, |
52 |
|
CHAR(13), CHAR(10), '@max_key_query = ', @max_key_query)); |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 379/545