Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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(SELEC 'table name'
15FROM 'information schema' 'tables'
16WHERE 'table schema' = DATABASE()
17 |
AND |
'table |
type' |
= |
'BASE TABLE' |
18 |
AND |
'table |
name' = |
'tables rc') |
|
19THEN
20CREATE TABLE 'tables rc'
21(
22'tabl name' VARCHAR(200 ,
23' count' INT
24);
25END IF;
26 |
|
|
|
27 |
TRUNCAT TABLE 'tables |
rc'; |
|
28 |
|
|
|
29 |
OPEN |
tables cursor |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 430/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
MySQL I |
Решение 5.2.3.b (код процедуры) (продолжение) |
| |
30tables loop LOOP
31FETCH all tables cursor INTO tbl name
32IF done THEN
33LEAVE tables loop;
34END IF;
35 |
|
36 |
SET @table rc query = CONCAT('SELECT COUNT(1) INTO @tbl rc FROM ' , |
37 |
tbl name ’'’); |
38PREPARE table opt stmt FROM @table rc query'
39EXECUTE table opt stmt;
40DEALLOCATE PREPARE table opt stmt
41 |
|
42 |
INSERT INTO 'tables rc' ('table name', |
43 |
'rows count') |
44 |
VALUES (tbl name |
45 |
@tbl rc ; |
46 |
|
47 |
END LOOP tables loop; |
48 |
|
49CLOSE all tables cursor;
50END;
51$$
52DELIMITER ;
Для проверки корректности полученного решения можно выполнить следующие запросы.
MySQL |
Решение 5.2.3.b (проверка работоспособности) |
|
1 |
CALL CACHE_TABLES_RC; |
|
2 |
SELECT * FROM 'tables rc'; |
|
Решение для MS SQL Server выглядит следующим образом.
MS SQL Решение 5.2.3.b
1CREATE PROCEDURE CACHE TABLES RC
2AS
3BEGIN
4DECLARE @table name NVARCHAR'200 ;
5DECLARE @table rows INT;
6DECLARE @query text NVARCHAR'2000 ;
7DECLARE tables cursor CURSOR LOCAL FAST FORWARD FOR
8SELECT [name]
9 |
FROM |
sys.tables; |
10 |
|
|
11IF NOT EXISTS
12(SELECT [name]
13 FROM sys.tables
14WHERE [name] = 'tables rc')
15BEGIN
16CREATE TABLE [tables rc]
17(
18[table name] VARCHAR 200),
19[rows count] INT
20);
21END;
22
23 TRUNCATE TABLE [tables rc];
24
25OPEN tables cursor
26FETCH NEXT FROM tables_cursor INTO @table_name;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 431/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
MS SQL I |
Решение 5.2.3.b (код процедуры) (продолжение) |
| |
27WHILE @@FETCH STATUS = 0
28BEGIN
29SET @query text = CONCAT('SELECT @cnt = COUNT(1) FROM [',
30 |
@table name ']'); |
31 |
EXECUTE sp executesql @query text, N'@cnt INT OUT', @table rows OUTPUT; |
32 |
|
33 |
INSERT INTO [tables rc] ([table name], |
34 |
[rows count]) |
35 |
VALUES @table name |
36 |
@table rows'; |
37 |
|
38FETCH NEXT FROM tables cursor INTO @table name
39END;
40CLOSE tables cursor;
41DEALLOCATE tables cursor;
42END;
43GO
Для проверки корректности полученного решения можно выполнить следующие запросы.
MS SQL |
Решение 5.2.3.b (проверка работоспособности) |
||||||
2 |
|
SELECT * FROM [tables rc] |
|
|
|||
1 |
EXECUTE CACHE_TABLES_RC; |
|
|
||||
|
|
Решение для Oracle выглядит следующим образом. |
|||||
Oracl |
і |
Решение 5.2.3.b (код процедуры) |
| |
||||
e |
|
||||||
|
|
|
|
|
|
|
|
1 |
|
CREATE OR REPLACE PROCEDURE CACHE TABLES RC |
|||||
2 |
|
AS |
|
|
|
|
|
3 |
|
table name VARCHAR(150 |
:= ''; |
||||
4 |
|
table rows NUMBER 10) := 0 |
|
||||
5 |
|
table found NUMBER(1) :=0; |
|
||||
6 |
|
query text VARCHAR(1000) := ''; |
|||||
7 |
|
CURSOR tables cursor IS |
|
|
|||
8 |
|
|
SELECT TABLE NAME AS "table name" |
||||
9 |
|
|
FROM |
ALL TABLES |
|
|
|
10 |
|
|
WHERE OWNER=USER; |
|
|
||
11 |
|
BEGIN |
|
|
|
|
|
12 |
|
SELECT COUNT 1 |
INTO table found |
||||
13 |
|
FROM |
ALL TABLES |
|
|
||
14 |
|
WHERE |
OWNER=USER |
|
|
||
15 |
|
AND |
TABLE_NAME = 'tables_rc'; |
||||
16 |
|
|
|
|
|
|
|
17 |
|
IF (table found = 0) |
|
|
|||
18 |
|
THEN |
|
|
|
|
|
19 |
|
|
EXECUTE IMMEDIATE 'CREATE TABLE "tables rc" |
||||
20 |
|
|
("table name" VARCHAR(200), |
||||
21 |
|
|
"rows count" NUMBER(10)) ' ; |
||||
22 |
|
END IF; |
|
|
|
||
23 |
|
EXECUTE IMMEDIATE 'TRUNCATE TABLE "tables_rc"'; |
|||||
24 |
|
|
|
|
|
|
|
25FOR one row IN tables cursor
26LOOP
27query text := 'SELECT COUNT(1) FROM "' || one row "table name" ||
28 |
'"'; |
29 |
EXECUTE IMMEDIATE query_text INTO table_rows |
30 |
|
31 |
query text := 'INSERT INTO "tables rc" ("table name", "rows count") |
32 |
VALUES (''' || one row "table name" || ''', ' || |
33 |
table rows || ')'; |
34EXECUTE IMMEDIATE query text 1
35END LOOP;
36END;
37/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 432/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Для проверки корректности полученного решения можно выполнить следующие запросы.
1 EXECUTE CACHE_TABLES_RC;
2 SELECT * FROM "tables rc"
Задание 5.2.3.TSK.A: создать хранимую процедуру, автоматически создающую и наполняющую данными таблицу arrears, в которой должны быть представлены идентификаторы и имена читателей, у которых до сих пор находится на руках хотя бы одна книга, по которой дата возврата установлена в прошлом относительно текущей даты. Эта таблица должна быть связана с таблицей subscriptions связью «один к одному».
Задание 5.2.3.TSK.B: создать хранимую процедуру, удаляющую все индексы (кроме первичных ключей), построенные на таблицах текущей базы данных и включающие в себя более одного поля.
& Задание 5.2.3.TSK.C: создать хранимую процедуру, удаляющую все представления, для которых SELECT COUNT(1) FROM представление
возвращает значение меньше десяти.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 433/545
Пример 41: управление неявными транзакциями
Раздел 6: Использование транзакций
6.1. Управление неявными и явными транзакциями
6.1.1. Пример 41: управление неявными транзакциями
О |
Задача 6.1.1.a{408}: продемонстрировать поведение СУБД при выполнении |
операций модификации данных в случаях, когда режим автоподтвержде- |
|
|
ния неявных транзакций включён и выключен. |
О Задача 6.1.1.b{413}: создать хранимую процедуру, выполняющую следующие действия:
•определяющую, включён ли режим автоподтверждения неявных транзакций;
•выключающую этот режим, если требуется (если в процедуру передан соответствующий параметр с соответствующим значением);
•выполняющую вставку N записей в таблицу subscribers (N передаётся в процедуру соответствующим параметром);
•восстанавливающую исходное значение режима автоподтверждения неявных транзакций (если оно было изменено);
•возвращающую время, затраченное на выполнение вставки.
Ожидаемый результат 6.1.1 .a.
При включённом режиме автоподтверждения неявных транзакций модификация данных немедленно фиксируется, при выключенном режиме автоподтверждения неявных транзакций модификация данных не вступает в силу до момента явного подтверждения транзакции.
Ожидаемый результат 6.1.1.b.
Хранимая процедура выводит отладочные сообщения по всем шагам описанного в задании алгоритма и после завершения своей работы возвращает информацию о количестве затраченного на выполнение операции вставки времени.
уЦ7
Решение 6.1.1 .a{408}.
Режим автоподтверждения неявных транзакций актуален для MySQL27 и MS SQL Server28’ 29 (и не актуален для Oracle30, где данное поведение полностью отдано на усмотрение клиентского ПО) в случае, когда операции не обрамляются явным образом выражениями по запуску и подтверждению или отмене транзакций.
MySQL по умолчанию работает с включённым автоподтверждением неявных транзакций, т.е. любые изменения данных сразу же вступают в силу. За изменение данного поведения отвечает параметр autocommit (которым можно управлять локально на протяжении сессии или глобально, изменив соответствующую настройку
27http://dev.mysql.com/doc/refman/5.6/en/commit.html
28https://technet.microsoft.com/en-us/library/ms190230%28v=sql.105%29.aspx
29https://msdn.microsoft.com/en-us/library/ms187807.aspx
30https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:314816776423
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 434/545