Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 38: выполнение динамических запросов с помощью хранимых процедур
ления решена, остаётся только создать хранимую процедуру по аналогии с реше-
ниями для MySQL и MS SQL Server.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 410/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
Oracle і Решение 5.2.1 .b (альтернативное решение в виде одной хранимой функции)
1CREATE OR REPLACE TYPE ........................"Show^table^objects^row" AS
2........... OBJECT
3 |
( |
|
|
|
|
"field_a" VARCHAR2(500), |
|
||||
4 |
|
||||
"field_b" VARCHAR2(32767) |
|
||||
5 |
|
||||
); |
|
|
|
|
|
6 |
|
|
|
|
|
/ |
|
|
|
|
|
7 |
|
|
|
|
|
CREATE TYPE |
"show_table_objects_table" |
|
|||
8 |
|
||||
IS TABLE OF |
"show_table_objects_row"; |
|
|||
9 |
|
||||
/ |
|
|
|
|
|
10 |
|
|
|
|
|
|
|
|
|
|
|
11 |
|
CREATE OR REPLACE FUNCTION SHOW_TABLE_OBJECTS_FNC(table_name |
IN |
||
|
|
||||
12 |
VARCHAR2) |
|
|
|
|
13 |
RETURN |
|
|
"show_table_objects_table" PIPELINED |
|
|
|
|
|
||
14 |
AS |
|
|
|
|
|
|
|
|
|
|
15 |
TYPE |
type_rc |
IS REF CURSOR; |
|
|
16 |
|
||||
rc |
type_rc |
|
|
||
|
|
|
|||
17field_a VARCHAR2(500);
18field_b VARCHAR2(32767);
19query_text VARCHAR2(1000);
20BEGIN
21query_text := '
22SELECT ''foreign_key''AS "object_type",
23CONSTRAINT_NAME AS "object_name"
24FROM ALL_CONSTRAINTS
25WHERE OWNER = USER
26AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''
27 |
AND |
CONSTRAINT_TYPE = ''R'' ' |
||
28 |
||||
UNION |
|
|
||
|
|
|
||
29 |
SELECT ''trigger'' |
AS |
"object_type", |
|
30TRIGGER_NAME AS "object_name"
31FROM ALL_TRIGGERS
32WHERE OWNER = USER
33AND TABLE_NAME = ''_FP_TABLE_NAME_PLACEHOLDER_''';
34 |
query_text := REPLACE query_text |
'_FP_TABLE_NAME_PLACEHOLDER_', |
|
35 |
|||
table_name ; |
|||
36 |
|||
|
|
||
37 |
OPEN rc FOR query_text; |
|
|
38 |
LOOP |
|
|
|
|
||
39 |
FETCH rc INTO field_a, field_b; |
|
|
|
|
||
40 |
EXIT WHEN rc%NOTFOUND; |
|
|
41 |
PIPE ROW("show_table_objects_row" field_a, field_b ); |
||
|
|||
42 |
END LOOP; |
|
|
43 |
CLOSE rc; |
|
|
44 |
|
45query_text := '
46SELECT VIEW_NAME,
47TEXT
48FROM ALL_VIEWS
49WHERE OWNER = USER';
51OPEN rc FOR query_text;
52LOOP
53FETCH rc INTO field_a, field_b;
54EXIT WHEN rc%NOTFOUND;
55IF (INSTR field_b, '"' || table_name || '"') > 0)
56THEN
57PIPE ROW "show_table_objects_row" 'view', field_a );
58END IF;
59END LOOP;
60CLOSE rc;
61
62 RETURN; END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 412/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 Стр: 413/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.
В начале каждых суток запускается созданная хранимая процедура. Все таблицы базы данных приводятся в оптимизированное состояние.
уЦ7
■ч. Решение 5.2.2.a{388}.
Основной запрос (строки 14-29), выполняющий обновление данных в таблице books_statistics, построен на основе решения{216} задачи 3.1.2.a{215}.
Однако для повышения надёжности мы будем проверять существование таблицы books_statistics, которую собираемся обновить. Эта проверка реализована в строках 5-13. Если искомая таблица не существует, мы завершаем работу хранимой процедуры, возвращая соответствующее сообщение об ошибке.
MySQL I |
Решение 5.2.2.a (код процедуры) |
| |
1DELIMITER $$
2CREATE PROCEDURE UPDATE BOOKS STATISTICS()
3BEGIN
4 |
|
5 |
IF (NOT EXISTS(SELECT * |
6 |
FROM 'information schema' 'tables' |
7 |
WHERE 'table schema' = DATABASE() |
8 |
AND 'table name' = 'books statistics')) |
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 Стр: 414/545