Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 40: управление структурами базы данных с помощью хранимых процедур
5.2.3.Пример 40: управление структурами базы данных с помощью хранимых процедур
Задача 5.2.3.a{400}: создать хранимую процедуру, автоматически создающую и наполняющую данными агрегирующую таблицу books_statistics (см. задачу 3.1.2.a{215}).
Задача 5.2.3.b{404}: создать хранимую процедуру, автоматически создающую и наполняющую данными агрегирующую таблицу tables_rc, содержащую информацию о количестве записей во всех таблицах базы данных в формате (имя_таблицы, количество_записей).
Ожидаемый результат 5.2.3.a.
При вызове хранимой процедуры таблица books_statistics создаётся и наполняется данными. Если таблица уже существует на момент вызова хранимой процедуры, данные в ней обновляются (приводятся в актуальное состояние).
Пример содержимого таблицы books_statistics см. в задаче 3.1.2.a{215}.
Ожидаемый результат 5.2.3.b.
При вызове хранимой процедуры таблица tables_rc создаётся и наполняется данными. Если таблица уже существует на момент вызова хранимой процедуры, данные в ней обновляются (приводятся в актуальное состояние).
Пример содержимого таблицы tables_rc:
table_name |
rows_count |
authors |
7 |
books |
7 |
books_statistics |
1 |
genres |
6 |
m2m_books_authors |
9 |
m2m_books_genres |
11 |
subscribers |
4 |
subscriptions |
11 |
tables_rc |
8 |
Решение 5.2.3.a{400}.
В решении{367} задачи 5.1.1.c{352} уже содержатся готовые запросы для создания таблицы books_statistics, наполнения её данными и обновления её данных. Сейчас нам остаётся только разместить эти запросы внутри хранимой процедуры и добавить проверку существования самой таблицы books_statistics.
Решения для MySQL и MS SQL Server будут полностью идентичными, а в Oracle нам придётся все запросы выполнять через EXECUTE IMMEDIATE, т.к. в случае отсутствия таблицы books_statistics эта СУБД автоматически считает хранимую процедуру некорректной, если в ней напрямую прописаны запросы, обращающиеся к этой таблице.
Теперь остаётся только привести код.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 400/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для MySQL выглядит следующим образом.
MySQL Решение 5.2.3.a (код процедуры)
1DELIMITER $$
2CREATE PROCEDURE CREATE_BOOKS_STATISTICS()
3BEGIN
4
5IF NOT EXISTS
6(SELECT `table_name`
7 |
|
FROM |
`information_schema`.`tables` |
||
8 |
|
WHERE |
`table_schema` |
= DATABASE() |
|
9 |
|
AND |
`table_type` |
= |
'BASE TABLE' |
10 |
|
AND |
`table_name` |
= |
'books_statistics') |
11THEN
12CREATE TABLE `books_statistics`
13(
14`total` INTEGER UNSIGNED NOT NULL,
15`given` INTEGER UNSIGNED NOT NULL,
16`rest` INTEGER UNSIGNED NOT NULL
17);
18INSERT INTO `books_statistics`
19 |
|
(`total`, |
20 |
|
`given`, |
21 |
|
`rest`) |
|
|
|
22SELECT IFNULL(`total`, 0),
23IFNULL(`given`, 0),
24IFNULL(`total` - `given`, 0) AS `rest`
25 |
|
FROM |
(SELECT (SELECT |
SUM(`b_quantity`) |
|
|
26 |
|
|
FROM |
`books`) |
AS |
`total`, |
27 |
|
|
(SELECT |
COUNT(`sb_book`) |
|
|
28 |
|
|
FROM |
`subscriptions` |
|
|
29 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`given`) |
|
30AS `prepared_data`;
31ELSE
32UPDATE `books_statistics`
33JOIN
34(SELECT IFNULL(`total`, 0) AS `total`,
35IFNULL(`given`, 0) AS `given`,
36IFNULL(`total` - `given`, 0) AS `rest`
37 |
|
FROM |
(SELECT (SELECT |
SUM(`b_quantity`) |
|
|
38 |
|
|
FROM |
`books`) |
AS |
`total`, |
39 |
|
|
(SELECT |
COUNT(`sb_book`) |
|
|
40 |
|
|
FROM |
`subscriptions` |
|
|
41 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`given`) |
|
42AS `prepared_data`) AS `src`
43SET `books_statistics`.`total` = `src`.`total`,
44`books_statistics`.`given` = `src`.`given`,
45`books_statistics`.`rest` = `src`.`rest`;
46END IF;
47END;
48$$
49DELIMITER ;
Для проверки корректности полученного решения можно выполнить следующие запросы.
MySQL |
Решение 5.2.3.a (проверка работоспособности) |
1DROP TABLE `books_statistics`;
2CALL CREATE_BOOKS_STATISTICS;
3SELECT * FROM `books_statistics`;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 401/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для MS SQL Server выглядит следующим образом.
MS SQL Решение 5.2.3.a (код процедуры)
1CREATE PROCEDURE CREATE_BOOKS_STATISTICS
2AS
3BEGIN
4IF NOT EXISTS
5(SELECT [name]
6 |
|
FROM |
sys.tables |
7 |
|
WHERE |
[name] = 'books_statistics') |
8BEGIN
9CREATE TABLE [books_statistics]
10(
11[total] INTEGER NOT NULL,
12[given] INTEGER NOT NULL,
13[rest] INTEGER NOT NULL
14);
15INSERT INTO [books_statistics]
16 |
|
([total], |
17 |
|
[given], |
18 |
|
[rest]) |
|
|
|
19SELECT ISNULL([total], 0) AS [total],
20ISNULL([given], 0) AS [given],
21ISNULL([total] - [given], 0) AS [rest]
22 |
|
FROM |
(SELECT (SELECT |
SUM([b_quantity]) |
|
23 |
|
|
FROM |
[books]) |
AS [total], |
24 |
|
|
(SELECT |
COUNT([sb_book]) |
|
25 |
|
|
FROM |
[subscriptions] |
|
26 |
|
|
WHERE |
[sb_is_active] = 'Y') AS [given]) |
|
27 |
|
|
AS [prepared_data]; |
|
|
28 |
|
|
|
|
|
29END
30ELSE
31BEGIN
32UPDATE [books_statistics]
33SET
34[books_statistics].[total] = [src].[total],
35[books_statistics].[given] = [src].[given],
36[books_statistics].[rest] = [src].[rest]
37FROM [books_statistics]
38JOIN
39(SELECT ISNULL([total], 0) AS [total],
40 |
|
|
ISNULL([given], |
0) AS [given], |
|
41 |
|
|
ISNULL([total] - [given], 0) AS [rest] |
|
|
42 |
|
FROM |
(SELECT (SELECT |
SUM([b_quantity]) |
|
43 |
|
|
FROM |
[books]) |
AS [total], |
44 |
|
|
(SELECT |
COUNT([sb_book]) |
|
45 |
|
|
FROM |
[subscriptions] |
|
46 |
|
|
WHERE |
[sb_is_active] = 'Y') AS [given]) |
|
47 |
|
|
AS [prepared_data] |
|
|
48) AS [src]
49ON 1=1;
50END;
51END;
52GO
Для проверки корректности полученного решения можно выполнить следующие запросы.
MS SQL |
Решение 5.2.3.a (проверка работоспособности) |
1DROP TABLE [books_statistics];
2EXECUTE CREATE_BOOKS_STATISTICS;
3SELECT * FROM [books_statistics];
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 402/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение для Oracle выглядит следующим образом. Обратите внимание на то, как реализована проверка существования таблицы books_statistics: т.к. Oracle не поддерживает вариант с IF NOT EXISTS, мы вынуждены поместить в переменную количество найденных рядов и затем проверить его значение в блоке IF.
Oracle Решение 5.2.3.a (код процедуры)
1CREATE OR REPLACE PROCEDURE CREATE_BOOKS_STATISTICS
2AS
3table_found NUMBER(1) :=0;
4BEGIN
5 |
|
|
|
6 |
|
SELECT |
COUNT(1) INTO table_found |
7 |
|
FROM |
ALL_TABLES |
8 |
|
WHERE |
OWNER=USER |
9 |
|
AND |
TABLE_NAME = 'books_statistics'; |
10 |
|
|
|
11IF (table_found = 0)
12THEN
13EXECUTE IMMEDIATE 'CREATE TABLE "books_statistics"
14(
15"total" NUMBER(10),
16"given" NUMBER(10),
17"rest" NUMBER(10)
18)';
19 |
|
|
20 |
|
EXECUTE IMMEDIATE 'INSERT INTO "books_statistics" |
21 |
|
("total", |
22 |
|
"given", |
23 |
|
"rest") |
24SELECT "total",
25"given",
26("total" - "given") AS "rest"
27 |
|
FROM |
(SELECT |
SUM("b_quantity") AS "total" |
28 |
|
|
FROM |
"books") |
29 |
|
JOIN |
(SELECT |
COUNT("sb_book") AS "given" |
30 |
|
|
FROM |
"subscriptions" |
31 |
|
|
WHERE |
"sb_is_active" = ''Y'') |
32 |
|
ON 1 |
= 1'; |
|
33 |
|
|
|
|
34ELSE
35EXECUTE IMMEDIATE 'UPDATE "books_statistics"
36SET ("total", "given", "rest") =
37(SELECT "total",
38 |
|
|
"given", |
|
39 |
|
|
("total" - "given") AS "rest" |
|
40 |
|
FROM |
(SELECT |
SUM("b_quantity") AS "total" |
41 |
|
|
FROM |
"books") |
42 |
|
JOIN |
(SELECT |
COUNT("sb_book") AS "given" |
43 |
|
|
FROM |
"subscriptions" |
44 |
|
|
WHERE |
"sb_is_active" = ''Y'') |
45ON 1 = 1)';
46END IF;
47END;
48/
Для проверки корректности полученного решения можно выполнить следующие запросы.
Oracle |
Решение 5.2.3.a (проверка работоспособности) |
1DROP TABLE "books_statistics";
2EXECUTE CREATE_BOOKS_STATISTICS;
3SELECT * FROM "books_statistics";
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 403/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Решение 5.2.3.b{400}.
Логика решения этой задачи представляет собой комбинацию подходов, представленных в решениях{393}, {400} задач 5.2.2.b{388} и 5.2.3.a{400}. Мы будем:
•проверять существование целевой таблицы tables_rc;
•создавать её, если таковой не обнаружилось;
•получать список таблиц базы данных и для каждой из них выполнять требуемую операцию — получение количества рядов и добавление этого количества вместе с именем анализируемой таблицы в агрегирующую таблицу tables_rc.
Важно отметить, что здесь для упрощения кода мы выполняем операцию TRUNCATE с последующим добавлением рядов. Это избавляет нас от необходимости реализовывать алгоритм с добавлением информации о новых таблицах, удалением информации о старых и обновлением информации о существующих.
Однако в реальных проектных задачах некий код может не ожидать, что в таблице tables_rc нет данных (что наблюдается в промежуток времени между выполнением операции TRUNCATE и проходом по циклу наполнения), и это может привести к возникновению ошибок.
Решение для MySQL выглядит следующим образом.
MySQL Решение 5.2.3.b (код процедуры)
1DELIMITER $$
2CREATE PROCEDURE CACHE_TABLES_RC()
3BEGIN
4DECLARE done INT DEFAULT 0;
5DECLARE tbl_name VARCHAR(200) DEFAULT '';
6DECLARE all_tables_cursor CURSOR FOR
7SELECT `table_name`
8 |
|
FROM |
`information_schema`.`tables` |
|||
9 |
|
WHERE |
`table_schema` |
= |
DATABASE() |
|
10 |
|
AND |
`table_type` = |
'BASE |
TABLE'; |
|
11 |
|
DECLARE |
CONTINUE HANDLER |
FOR |
NOT FOUND SET done = 1; |
|
12 |
|
|
|
|
|
|
13IF NOT EXISTS
14(SELECT `table_name`
15 |
|
FROM |
`information_schema`.`tables` |
||
16 |
|
WHERE |
`table_schema` = DATABASE() |
||
17 |
|
AND |
`table_type` |
= |
'BASE TABLE' |
18 |
|
AND |
`table_name` |
= |
'tables_rc') |
19THEN
20CREATE TABLE `tables_rc`
21(
22`table_name` VARCHAR(200),
23`rows_count` INT
24);
25END IF;
26
27 TRUNCATE TABLE `tables_rc`;
28
29 OPEN all_tables_cursor;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 404/545