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

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

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

3IS

4query_text VARCHAR2(1000);

5BEGIN

6query_text := '

7SELECT ''foreign_key'' AS "object_type",

8CONSTRAINT_NAME AS "object_name"

9FROM ALL_CONSTRAINTS

10WHERE OWNER = USER

11AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''

12AND CONSTRAINT_TYPE = ''R''

13UNION

14SELECT ''trigger'' AS "object_type",

15TRIGGER_NAME AS "object_name"

16FROM ALL_TRIGGERS

17WHERE OWNER = USER

18AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''

19UNION

21"VIEW_NAME" AS "object_name"

22FROM TABLE(ALL_VIEWS_VARCHAR2)

23WHERE "TEXT" LIKE ''%"_FP_TABLE_NAME_PLACEHOLDER_"%''';

24

25 query_text := REPLACE(query_text, '_FP_TABLE_NAME_PLACEHOLDER_', 26 table_name);

27

28OPEN final_rc FOR query_text;

29END;

30/

Но одно неудобство остаётся: чтобы получить результат работы такой процедуры, придётся использовать достаточно нетривиальный код:

1DECLARE

2rc SYS_REFCURSOR;

3object_type VARCHAR2(500);

4object_name VARCHAR2(500);

5BEGIN

6SHOW_TABLE_OBJECTS('subscriptions', rc);

7

8LOOP

9FETCH rc INTO object_type, object_name;

10EXIT WHEN rc%NOTFOUND;

11DBMS_OUTPUT.PUT_LINE(object_type || ' | ' || object_name);

12END LOOP;

13CLOSE rc;

14END;

Идаже с таким нетривиальным кодом мы получаем результат в виде текста,

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

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

Заодно мы изменим тип функции на PIPELINED, что снизит нагрузку на опе-

ративную память и немного повысит производительность.

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

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

Oracle Решение 5.2.1.b (альтернативное решение в виде одной хранимой функции)

1CREATE OR REPLACE TYPE "show_table_objects_row" AS OBJECT

2(

3"field_a" VARCHAR2(500),

4"field_b" VARCHAR2(32767)

5);

6/

7CREATE TYPE "show_table_objects_table"

8IS TABLE OF "show_table_objects_row";

9/

10

11CREATE OR REPLACE FUNCTION SHOW_TABLE_OBJECTS_FNC(table_name IN VARCHAR2)

12RETURN "show_table_objects_table" PIPELINED

13AS

14TYPE type_rc IS REF CURSOR;

15rc type_rc;

18query_text VARCHAR2(1000);

19BEGIN

20query_text := '

21SELECT ''foreign_key'' AS "object_type",

22CONSTRAINT_NAME AS "object_name"

23FROM ALL_CONSTRAINTS

24WHERE OWNER = USER

25AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''

26AND CONSTRAINT_TYPE = ''R''

27UNION

28SELECT ''trigger'' AS "object_type",

29TRIGGER_NAME AS "object_name"

30FROM ALL_TRIGGERS

31WHERE OWNER = USER

32AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''';

33query_text := REPLACE(query_text, '_FP_TABLE_NAME_PLACEHOLDER_',

36OPEN rc FOR query_text;

37LOOP

38FETCH rc INTO field_a, field_b;

39EXIT WHEN rc%NOTFOUND;

40PIPE ROW("show_table_objects_row"(field_a, field_b));

41END LOOP;

42CLOSE rc;

43

44query_text := '

45SELECT VIEW_NAME,

46TEXT

50OPEN rc FOR query_text;

51LOOP

52FETCH rc INTO field_a, field_b;

53EXIT WHEN rc%NOTFOUND;

54IF (INSTR(field_b, '"' || table_name || '"') > 0)

55THEN

56PIPE ROW("show_table_objects_row"('view', field_a));

57END IF;

58END LOOP;

59CLOSE rc;

60

61RETURN;

62END;

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

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

Ключевая идея этой функции состоит в том, чтобы передавать на выход данные, полученные в отдельности из двух разных запросов.

Первый запрос (строки 20-42) просто выбирает данные по внешним ключам и триггерам без каких-то особых сложностей и нюансов: в переменную field_a помещается строковая константа ('foreign_key', 'trigger'), в переменную field_b — имя соответствующего внешнего ключа или триггера. Затем эти переменные используются для инициализации полей объекта show_table_objects_row.

Во втором запросе (строки 44-59) мы с использованием тех же переменных

итого же объекта, что и в первом запросе, делаем следующее:

•сначала в переменные field_a и field_b помещаются имя и текст представления соответственно (строка 52);

•затем значение переменной field_b используется для проверки того факта,

что текст представления содержит имя анализируемой таблицы (строка 54), и больше это значение переменной field_b нам не нужно и нигде не используется;

•наконец (в строке 56) мы используем текстовую константу 'view' и имя представления, хранящееся в переменной field_b, для инициализации полей объекта show_table_objects_row.

Теперь мы можем получить необходимый нам результат в виде таблицы.

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

1 SELECT * FROM TABLE(SHOW_TABLE_OBJECTS_FNC('subscriptions'));

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

Задание 5.2.1.TSK.A: создать хранимую процедуру, обновляющую все поля типа DATE (если такие есть) всех записей указанной таблицы на значение текущей даты.

Задание 5.2.1.TSK.B: создать хранимую процедуру, формирующую список таблиц и их внешних ключей, зависящих от указанной в параметре функции таблицы.

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

Пример 39: оптимизация производительности с помощью хранимых процедур

5.2.2.Пример 39: оптимизация производительности с помощью хранимых процедур

Задача 5.2.2.a{388}: создать хранимую процедуру, запускаемую по расписанию каждый час и обновляющую данные в агрегирующей таблице books_statistics (см. задачу 3.1.2.a{215}).

Задача 5.2.2.b{393}: создать хранимую процедуру, запускаемую по расписанию каждый день и оптимизирующую (дефрагментирующую, компактифицирующую) все таблицы базы данных.

Ожидаемый результат 5.2.2.a.

Вначале каждого часа запускается созданная хранимая процедура. Данные

втаблице books_statistics приводятся в актуальное состояние.

Ожидаемый результат 5.2.2.b.

В начале каждых суток запускается созданная хранимая процедура. Все таблицы базы данных приводятся в оптимизированное состояние.

Решение 5.2.2.a{388}.

Основной запрос (строки 14-29), выполняющий обновление данных в таблице books_statistics, построен на основе решения{216} задачи 3.1.2.a{215}.

Однако для повышения надёжности мы будем проверять существование таблицы books_statistics, которую собираемся обновить. Эта проверка реализована в строках 5-13. Если искомая таблица не существует, мы завершаем работу хранимой процедуры, возвращая соответствующее сообщение об ошибке.

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

1DELIMITER $$

2CREATE PROCEDURE UPDATE_BOOKS_STATISTICS()

3BEGIN

9THEN

10SIGNAL SQLSTATE '45001'

11SET MESSAGE_TEXT = 'The `books_statistics` table is missing.',

12MYSQL_ERRNO = 1001;

13END IF;

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

Пример 39: оптимизация производительности с помощью хранимых процедур

14UPDATE `books_statistics`

15JOIN

16(SELECT IFNULL(`total`, 0) AS `total`,

17IFNULL(`given`, 0) AS `given`,

18IFNULL(`total` - `given`, 0) AS `rest`

24AS `prepared_data`

25) AS `src`

26SET

27`books_statistics`.`total` = `src`.`total`,

28`books_statistics`.`given` = `src`.`given`,

29`books_statistics`.`rest` = `src`.`rest`;

30END;

31$$

32DELIMITER ;

Проверим работоспособность (предварительно можно выполнить отдельный запрос на обновление данных в таблице books_statistics и установить все значения в ноль).

1 CALL UPDATE_BOOKS_STATISTICS;

Установка запуска полученной хранимой процедуры по расписанию выглядит следующим образом. Предварительно в строке 1 мы включаем планировщик задач MySQL (для того, чтобы он продолжал работать и после перезапуска MySQL, необходимо добавить строку event_scheduler = on в файл настроек MySQL my.ini).

3CREATE EVENT `update_books_statistics_hourly`

4ON SCHEDULE

5EVERY 1 HOUR

6STARTS DATE(NOW()) + INTERVAL (HOUR(NOW())+1) HOUR + INTERVAL 1 MINUTE

7ON COMPLETION PRESERVE

8DO

9CALL UPDATE_BOOKS_STATISTICS;

Убедиться, что соответствующая задача добавлена в планировщик, можно выполнив следующий запрос:

MySQL Решение 5.2.2.a (просмотр расписания)

1 SELECT * FROM `information_schema`.`events`

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

Переходим к MS SQL Server. Логика работы хранимой процедуры здесь полностью эквивалентна решению для MySQL: мы проверяем наличие таблицы books_statistics в строках 3-10 (и завершаем работу хранимой процедуры, если таблицы нет), а в строках 12-30 выполняем запрос, обновляющий данные.

Пока всё выглядит достаточно просто и тривиально, но добавление задачи в планировщик в данной СУБД реализовано куда более сложным образом.

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