Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 40: управление структурами базы данных с помощью хранимых процедур
MySQL Решение 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 (проверка работоспособности) |
1CALL CACHE_TABLES_RC;
2SELECT * 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 Стр: 405/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
MS SQL Решение 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 (проверка работоспособности) |
1EXECUTE CACHE_TABLES_RC;
2SELECT * FROM [tables_rc];
Решение для Oracle выглядит следующим образом.
Oracle Решение 5.2.3.b (код процедуры)
1CREATE OR REPLACE PROCEDURE CACHE_TABLES_RC
2AS
3table_name VARCHAR(150) := '';
4table_rows NUMBER(10) := 0;
5table_found NUMBER(1) :=0;
6query_text VARCHAR(1000) := '';
7CURSOR tables_cursor IS
8SELECT TABLE_NAME AS "table_name"
9 FROM ALL_TABLES
10WHERE OWNER=USER;
11BEGIN
12SELECT COUNT(1) INTO table_found
13 |
|
FROM |
ALL_TABLES |
14 |
|
WHERE |
OWNER=USER |
15 |
|
AND |
TABLE_NAME = 'tables_rc'; |
16 |
|
|
|
17IF (table_found = 0)
18THEN
19EXECUTE IMMEDIATE 'CREATE TABLE "tables_rc"
20("table_name" VARCHAR(200),
21"rows_count" NUMBER(10))';
22END IF;
23EXECUTE 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;
35END LOOP;
36END;
37/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 406/545
Пример 40: управление структурами базы данных с помощью хранимых процедур
Для проверки корректности полученного решения можно выполнить следующие запросы.
Oracle |
Решение 5.2.3.b (проверка работоспособности) |
1EXECUTE CACHE_TABLES_RC;
2SELECT * 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 Стр: 407/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.
Хранимая процедура выводит отладочные сообщения по всем шагам описанного в задании алгоритма и после завершения своей работы возвращает информацию о количестве затраченного на выполнение операции вставки времени.
Решение 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 408/545
Пример 41: управление неявными транзакциями
Для решения данной задачи в MySQL необходимо использовать следующий набор запросов.
MySQL Решение 6.1.1.a
1-- Автоподтверждение выключено:
2SET autocommit = 0;
3 |
|
|
|
4 |
|
SELECT |
COUNT(*) |
5 |
|
FROM |
`subscribers`; -- 4 |
6 |
|
|
|
7 |
|
INSERT |
INTO `subscribers` |
8 |
|
|
(`s_name`) |
9 |
|
VALUES |
('Иванов И.И.'); |
10 |
|
|
|
11 |
|
SELECT |
COUNT(*) |
12 |
|
FROM |
`subscribers`; -- 5 |
13 |
|
|
|
14 |
|
ROLLBACK; |
|
15 |
|
|
|
16 |
|
SELECT |
COUNT(*) |
17 |
|
FROM |
`subscribers`; -- 4 |
18 |
|
|
|
19-- Автоподтверждение включено:
20SET autocommit = 1;
21 |
|
|
|
22 |
|
SELECT |
COUNT(*) |
23 |
|
FROM |
`subscribers`; -- 4 |
24 |
|
|
|
25 |
|
INSERT |
INTO `subscribers` |
26 |
|
|
(`s_name`) |
27 |
|
VALUES |
('Иванов И.И.'); |
28 |
|
|
|
29 |
|
SELECT |
COUNT(*) |
30 |
|
FROM |
`subscribers`; -- 5 |
31 |
|
|
|
32 |
|
ROLLBACK; |
|
33 |
|
|
|
34 |
|
SELECT |
COUNT(*) |
35 |
|
FROM |
`subscribers`; -- 5 |
Встроках 1-17 запросы выполняются в режиме отключённого автоподтверждения неявных транзакций: именно поэтому отмена транзакции в строке 14 проходит успешно и вставка данных, выполненная в строках 7-9, аннулируется.
Встроках 19-35 запросы выполняются в режиме включённого автоподтверждения неявных транзакций, и потому отмена транзакции в строке 32 ни на что не влияет: вставка данных, выполненная в строках 25-27, остаётся в силе.
MS SQL Server (как и MySQL) по умолчанию работает с включённым автоподтверждением неявных транзакций, т.е. любые изменения данных сразу же вступают в силу. За изменение данного поведения отвечает параметр IMPLICIT_TRANSACTIONS (которым в общем случае можно управлять только локально на протяжении сессии; общие идеи по управлению этим параметром на уроне настроек описаны здесь31).
Для решения данной задачи в MS SQL Server необходимо использовать следующий набор запросов. Обратите внимание, что параметр IMPLICIT_TRANSACTIONS в MS SQL Server по своей логике противоположен параметру autocommit в MySQL (т.е. для выключения автоподтверждения неявных транзакций необходимо выполнить команду SET IMPLICIT_TRANSACTIONS ON).
31 https://msdn.microsoft.com/en-us/library/ms176031%28SQL.90%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 409/545