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

Пример 38: выполнение динамических запросов с помощью хранимых процедур

53WHILE (1 = 1)

54BEGIN

55EXECUTE sp_executesql @empty_key_query,

59IF (@empty_key_value IS NULL)

60BREAK;

66SET @update_key_query =

67CONCAT('UPDATE [', @table_name, '] SET [', @pk_name,

77END;

78GO

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 Решение 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 [books] ORDER BY [b_quantity];

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

Пример 38: выполнение динамических запросов с помощью хранимых процедур

В первом случае мы получим сообщение об ошибке с просьбой убрать IDEN- TITY-свойство с поля sb_id таблицы subscriptions, во втором случае процедура выполнится (да, устранение свободных значений в поле, хранящем количество книг, лишено всякого здравого смысла, но для проверки работоспособности хранимой процедуры такой вариант годится).

На этом решение для MS SQL Server завершено.

Переходим к Oracle. Характерных для MS SQL Server проблем здесь нет (даже нет необходимости отключать триггеры, обеспечивающие автоинкрементацию первичного ключа при вставке, т.к. там именно INSERT-триггеры, а мы будем выполнять UPDATE).

Oracle Решение 5.2.1.a (код процедуры)

3empty_key_query VARCHAR(1000) := '';

4max_key_query VARCHAR(1000) := '';

5empty_key_value NUMBER := NULL;

6max_key_value NUMBER := NULL;

7update_key_query VARCHAR(1000) := '';

8BEGIN

9keys_changed := 0;

10

11DBMS_OUTPUT.PUT_LINE('Point 1. table_name = ' || table_name ||

12' || pk_name = ' || pk_name || ', keys_changed = ' || keys_changed);

13

14empty_key_query :=

15'SELECT MIN("empty_key") AS "empty_key"

21WHERE "right"."' || pk_name || '" IS NULL

22UNION

23SELECT 1 AS "empty_key"

32max_key_query :=

33'SELECT MAX("' || pk_name || '") FROM "' || table_name || '"';

34

35DBMS_OUTPUT.PUT_LINE('Point 2. empty_key_query = ' || empty_key_query ||

36CHR(13) || CHR(10) || ' max_key_query = ' || max_key_query);

37

38LOOP

39EXECUTE IMMEDIATE empty_key_query INTO empty_key_value;

40EXIT WHEN empty_key_value IS NULL;

41EXECUTE IMMEDIATE max_key_query INTO max_key_value;

42update_key_query :=

43'UPDATE "' || table_name || '" SET "' || pk_name ||

44'" = ' || TO_CHAR(empty_key_value) || ' WHERE "' || pk_name ||

45'" = ' || TO_CHAR(max_key_value);

46DBMS_OUTPUT.PUT_LINE('Point 3. update_key_query = ' || update_key_query);

47EXECUTE IMMEDIATE update_key_query;

48keys_changed := keys_changed + 1;

49END LOOP;

50END;

51/

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

Пример 38: выполнение динамических запросов с помощью хранимых процедур

Получается, что единственные два отличия решения для Oracle от решения для MySQL состоят в способе вывода отладочной информации (строки 11-12, 3536, 46) и синтаксисе описания логики выхода из цикла (строка 40).

Проверить работоспособность полученного решения можно следующими запросами (не забудьте предварительно включить отображение получаемых от сервера сообщений запросом SET SERVEROUTPUT ON).

1DECLARE

2keys_changed_in_table NUMBER;

3BEGIN

4COMPACT_KEYS('books', 'b_id', keys_changed_in_table);

5DBMS_OUTPUT.PUT_LINE('Keys changed: ' || keys_changed_in_table);

6

7COMPACT_KEYS('subscriptions', 'sb_id', keys_changed_in_table);

8DBMS_OUTPUT.PUT_LINE('Keys changed: ' || keys_changed_in_table);

9END;

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

Решение 5.2.1.b{375}.

В решении{375} задачи 5.2.1.a{375} мы уже рассмотрели логику формирования и выполнения динамических SQL-запросов в хранимых процедурах. Отличие решения этой задачи будет в том, что результатом работы хранимой процедуры будет не изменение в БД и возвращение числа правок, а возвращение таблицы.

Решение для MySQL выглядит следующим образом.

MySQL Решение 5.2.1.b (код процедуры)

1DELIMITER $$

2CREATE PROCEDURE SHOW_TABLE_OBJECTS (IN table_name VARCHAR(150))

3BEGIN

4SET @query_text = '

9AND `table_name` = \'_FP_TABLE_NAME_PLACEHOLDER_\'

10AND `constraint_type` = \'FOREIGN KEY\'

11UNION

15WHERE `event_object_schema` = DATABASE()

16AND `event_object_table` = \'_FP_TABLE_NAME_PLACEHOLDER_\'

17UNION

21WHERE `table_schema` = DATABASE()

22AND `view_definition` LIKE \'%`_FP_TABLE_NAME_PLACEHOLDER_`%\'';

23SET @query_text = REPLACE(@query_text,

26PREPARE query_stmt FROM @query_text;

27EXECUTE query_stmt;

28DEALLOCATE PREPARE query_stmt;

29END;

30$$

31DELIMITER ;

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

Пример 38: выполнение динамических запросов с помощью хранимых процедур

Здесь (для разнообразия) текст итогового запроса мы получаем не с использованием функции CONCAT, а путём замены плейсхолдера _FP_TABLE_NAME_PLACEHOLDER_ на реальное имя таблицы в заранее подготовленном полном тексте запроса.

Логика же получения самого списка искомых объектов полностью тривиальная для внешних ключей и триггеров (см. текст запроса в строках 5-16), и только для представлений мы должны анализировать их исходный код, чтобы обнаружить упоминание там имени таблицы, полученной как параметр нашей процедуры (т.к. представления не ассоциируются напрямую с таблицами, а являются независимыми объектами).

Проверить работоспособность полученного решения можно следующим запросом.

1 CALL SHOW_TABLE_OBJECTS('subscriptions')

На этом решение для MySQL завершено.

Переходим к MS SQL Server. Здесь логика решения полностью совпадает с решением для MySQL, за исключением того факта, что информацию о триггерах приходится извлекать из [sys].[triggers] как аналога

`information_schema`.`triggers`.

MS SQL Решение 5.2.1.b (код процедуры)

3WITH EXECUTE AS OWNER

4AS

5DECLARE @query_text NVARCHAR(1000) = '';

6SET @query_text =

10WHERE [table_catalog] = DB_NAME()

11AND [table_name] = ''_FP_TABLE_NAME_PLACEHOLDER_''

12AND [constraint_type] = ''FOREIGN KEY''

13UNION

14SELECT ''trigger'' AS [object_type],

17WHERE OBJECT_NAME([parent_id]) = ''_FP_TABLE_NAME_PLACEHOLDER_''

18UNION

22WHERE [table_catalog] = DB_NAME()

23AND [view_definition] LIKE ''%[_FP_TABLE_NAME_PLACEHOLDER_]%''';

24

25SET @query_text = REPLACE(@query_text, '_FP_TABLE_NAME_PLACEHOLDER_',

28EXECUTE sp_executesql @query_text;

29GO

Проверить работоспособность полученного решения можно следующим запросом.

MS SQL Решение 5.2.1.b (код для выполнения хранимой процедуры и получения результата её работы)

1 EXECUTE SHOW_TABLE_OBJECTS 'subscriptions';

На этом решение для MS SQL Server завершено.

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

Пример 38: выполнение динамических запросов с помощью хранимых процедур

Переходим к Oracle. И вот здесь уже появятся радикальные отличия. В основе решения всё равно будет лежать тот же запрос, который мы использовали для MySQL и MS SQL Server, но в Oracle существует одна очень неприятная проблема, усложняющая решение в разы.

Текст представления (в котором мы ищем упоминание интересующей нас таблицы) хранится в поле типа LONG (это — не «длинное целое», это устаревший, но всё ещё используемый иногда текстовый тип данных22), и данные этого типа невозможно ни использовать в выражениях типа LIKE, ни преобразовать простым способом к другому типу (например, VARCHAR2).

Единственный более-менее адекватный способ извлечения LONG-данных с конвертацией к VARCHAR2 — создание хранимой функции. Существует универсальное решение23, но для простоты мы реализуем вариант, привязанный к конкретному источнику данных.

В представленном ниже коде мы создаём хранимую функцию, возвращающую таблицу (принцип создания и использования таких функций рассмотрен в решении{355} задачи 5.1.1.b{352}). Ключевая идея здесь состоит в том, что поле TEXT объекта all_views_row объявлено как VARCHAR2, и именно такой тип данных будет в выходной таблице. А с VARCHAR2-данными уже можно выполнять операции сравнения.

Вданном конкретном случае функция сначала готовит полный набор данных,

изатем возвращает его в виде сформированной таблицы, а PIPLINED-решение

будет представлено далее.

Oracle Решение 5.2.1.b (код функции для преобразования LONG в VARCHAR2)

1CREATE OR REPLACE TYPE "all_views_row" AS OBJECT

2(

3"VIEW_NAME" VARCHAR2(500),

4 "TEXT" VARCHAR2(32767)

5);

6/

7CREATE TYPE "all_views_table" IS TABLE OF "all_views_row";

8/

9

10CREATE OR REPLACE FUNCTION ALL_VIEWS_VARCHAR2

11RETURN "all_views_table"

12AS

13result_table "all_views_table" := "all_views_table"();

14CURSOR all_views_table_cursor IS

15SELECT VIEW_NAME,

16TEXT

17 FROM ALL_VIEWS

18WHERE OWNER = USER;

19BEGIN

20FOR one_row IN all_views_table_cursor

21LOOP

22result_table.extend;

23result_table(result_table.last) :=

25END LOOP;

26RETURN result_table;

27END;

28/

Теперь, когда проблема с применением выражения LIKE к тексту представ-

ления решена, остаётся только создать хранимую процедуру по аналогии с реше-

ниями для MySQL и MS SQL Server.

22https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS346

23https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:839298816582

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