Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 400/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
MySQL
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 401/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
на использование одной и той же таблицы одновременно в UPDATE- и SELECT-частях запроса);
•в строках 48-50 формируется текстовое представления запроса на обновление значения первичного ключа;
•для этого запроса в строках 53, 54, 55 соответственно формируется исполнимое выражение, происходит выполнение запроса, исполнимое выражение освобождается (т.к. на следующем шаге цикла текст запроса уже будет иным);
•в строке 57 происходит наращивание счётчика обновлённых значений первичного ключа;
•в строке 58 происходит переход на следующую итерацию цикла.
После завершения цикла нам остаётся только освободить ранее подготовленные исполнимые выражения, что и происходит в строках 61-62.
Выполнить полученную хранимую процедуру и узнать, сколько значений первичного ключа было изменено, можно следующими запросами (в первом случае будет возвращено значение 9, во втором — 0, т.к. в таблице books нет свободных значений первичного ключа).
MySQL і Решение 5.2.1 .а (код для выполнения хранимой процедуры и получения результата её работы)
1CALL COMPACT_KEYS ('subscriptions', 'sb_id', @keys_changed);
2SELECT @keys_changed;
3
4 CALL COMPACT_KEYS ('books', 'b_id', @keys_changed ; SELECT @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 |
99 |
95 |
91 |
86 |
62 |
61 |
57 |
42 |
10 |
10 |
|
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
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 Стр: 402/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
приведёт к фатальным последствиям, если на модифицируемый первичный ключ ссылаются внешние ключи других таблиц.
Никакого простого универсального решения для отключения и повторного включения IDENTITY-свойства у поля средствами SQL-запросов нет. Единствен-
ный более-менее доступный вариант — это выключение и повторное включение этого свойства через графический интерфейс MS SQL Server Management Studio.
Таким образом, в нашей хранимой процедуре мы будем проверять, является ли предназначенное для обработки поле IDENTITY-ПОЛЄМ, и запрещать выполнение операции, если является (строки 14-22 кода хранимой процедуры).
|
:.i.a |
|
|
|
|
|
|
1 |
CREATE PROCEDURE |
COMPACT_KEYS |
|
|
|
|
|
2 |
|
@table_name |
NVARCHAR |
150) |
@pk_name |
NVARCHAR(150 |
, |
3@keys_changed INT OUTPUT WITH EXECUTE AS OWNER
46 AS
5 |
DECLARE |
@empty_key_query NVARCHAR 10001 = |
|
''; |
|
8 |
DECLARE @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'))) ;
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 |
|
37UNION
38SELECT 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 Стр: 403/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
MS SQL I |
Решение 5.2.1.a (код процедуры) (продолжение) |
| |
|
53 |
|
WHILE (1 = 1) |
|
54 |
|
BEGIN |
|
55 |
|
EXECUTE sp_executesql @empty_key_query |
|
56 |
|
N'@empty k v INT OUT', |
|
57 |
|
@empty key value OUTPUT; |
|
58 |
|
|
|
59 |
|
IF (@empty key value IS NULL) |
|
60 |
|
BREAK; |
|
61 |
|
|
|
62 |
|
EXECUTE sp_executesql @max_key_query |
|
63 |
|
N' @max k v INT OUT' , |
|
64 |
|
@max key value OUTPUT; |
|
65 |
|
|
|
66 |
|
SET @update_key_query = |
|
67 |
|
CONCAT('UPDATE [', @table name |
'] SET [', @pk name |
68 |
|
'] = ', @empty key value |
' WHERE [', @pk name, '] = ', |
69 |
|
@max key value ; |
|
70 |
|
|
|
71 |
|
PRINT(CONCAT('Point 3. @update key query = ', @update key query ); |
|
72 |
|
|
|
73 |
|
EXECUTE sp executesql @update key query |
|
74 |
|
|
|
75 |
|
SET @keys changed = @keys changed + 1; |
|
76 |
|
|
|
77 |
|
END; |
|
78 |
GO |
|
|
|
|
|
|
SQL-запросы для определения первого свободного значения первичного ключа, максимального значения первичного ключа и обновления значения первичного ключа аналогичны решению для MySQL.
Небольшое отличие состоит в том, как получить в переменную результат выполнения динамического запроса: вместо SELECT ... INTO ... используется SET ... = (SELECT ...), а при выполнении динамического SQL с помощью sp_executesql передаются дополнительные параметры, позволяющие поместить результат выполнения запроса в указанную переменную (строки 55-57, 62-64).
Поскольку MS SQL Server не поддерживает do ... while циклы, мы вынуждены использовать бесконечный WHILE-цикл (строки 53-77), внутри которого будем про-
верять условие выхода и (при его выполнении) принудительно завершать цикл (строки 59-60).
Последнее отличие от MySQL состоит в способе вывода отладочной информации: в MS SQL Server мы можем использовать конструкцию PRINT (строки 26-27, 51-52, 71).
Проверить работоспособность полученного решения можно следующими запросами.
MS SQL I |
Решение 5.2.1.a (код для выполнения хранимой процедуры и получения результата её работы) |
| |
1DECLARE @res INT;
2EXECUTE COMPACT_KEYS 'subscriptions', 'sb id', @res OUTPUT;
3SELECT @res
4GO
5
6DECLARE @res INT;
7EXECUTE COMPACT_KEYS 'books', 'b quantity', @res OUTPUT;
8SELECT @res
9GO
10 |
|
|
11 |
SELECT * FROM |
ORDER BY [b quantity] |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 404/545