Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 42: управление явными транзакциями
один. В ситуациях, когда необходимо использовать несколько курсоров, используются т.н. «блоки кода», ограничивающие область видимости переменных. В нашем случае таких блоков два, второй вложен в первый, и расположены они в строках 7- 57 и 22-53 соответственно).
MySQL |
Решение 6.1.2.a (код процедуры) |
1DELIMITER $$
2CREATE PROCEDURE THREE_RANDOM_BOOKS()
3BEGIN
4SELECT 'Starting transaction...';
5START TRANSACTION;
6
7USERS: BEGIN
8DECLARE s_id_value INT DEFAULT 0;
9DECLARE subscribers_done INT DEFAULT 0;
10DECLARE subscribers_cursor CURSOR FOR
11SELECT `s_id`
12 |
|
FROM |
`subscribers`; |
13 |
|
DECLARE |
CONTINUE HANDLER FOR NOT FOUND SET subscribers_done = 1; |
14 |
|
|
|
|
|
|
|
15OPEN subscribers_cursor;
16read_users_loop: LOOP
17FETCH subscribers_cursor INTO s_id_value;
18IF subscribers_done THEN
19LEAVE read_users_loop;
20END IF;
21
22BOOKS: BEGIN
23DECLARE b_id_value INT DEFAULT 0;
24DECLARE books_done INT DEFAULT 0;
25DECLARE books_cursor CURSOR FOR
26SELECT `b_id`
27 FROM `books`
28ORDER BY RAND()
29LIMIT 3;
30DECLARE CONTINUE HANDLER FOR NOT FOUND SET books_done = 1;
31OPEN books_cursor;
32
33read_books_loop: LOOP
34FETCH books_cursor INTO b_id_value;
35IF books_done THEN
36LEAVE read_books_loop;
37END IF;
38 |
|
|
39 |
|
INSERT INTO `subscriptions` |
40 |
|
(`sb_subscriber`, |
41 |
|
`sb_book`, |
42 |
|
`sb_start`, |
43 |
|
`sb_finish`, |
44 |
|
`sb_is_active`) |
45 |
|
VALUES (s_id_value, |
46 |
|
b_id_value, |
|
|
|
47 |
|
NOW(), |
48 |
|
NOW() + INTERVAL 1 MONTH, |
49 |
|
'Y'); |
50 |
|
|
51END LOOP read_books_loop;
52CLOSE books_cursor;
53END BOOKS;
54
55END LOOP read_users_loop;
56CLOSE subscribers_cursor;
57END USERS;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 420/545
Пример 42: управление явными транзакциями
MySQL Решение 6.1.2.a (код процедуры) (продолжение)
58 |
|
IF EXISTS (SELECT 1 |
|
59 |
|
FROM |
`subscriptions` |
60 |
|
WHERE |
`sb_is_active`='Y' |
61 |
|
GROUP |
BY `sb_subscriber` |
62 |
|
HAVING COUNT(1)>10 |
|
63 |
|
LIMIT |
1) |
64THEN
65SELECT 'Rolling transaction back...';
66ROLLBACK;
67ELSE
68SELECT 'Committing transaction...';
69COMMIT;
70END IF;
71
72END;
73$$
74DELIMITER ;
Обратите внимание на имена переменных, в которые извлекаются значе-
ния полей `s_id` и `b_id`: s_id_value и b_id_value. Часть _value
туда добавлена не случайно, т.к. если имена таких переменных будут совпадать с именами полей таблицы, MySQL не будет извлекать в них данные.
Для проверки работоспособности полученного решения можно использовать следующие запросы. Если вы выполните их на исходном наборе данных базы данных «Библиотека», то дважды операция завершится успешно, а третий и последующие вызовы будут завершаться отменой транзакции.
MySQL Решение 6.1.2.a (код для проверки работоспособности)
1CALL THREE_RANDOM_BOOKS();
2SELECT * FROM `subscriptions`;
На этом решение для MySQL завершено.
Переходим к MS SQL Server. Общая логика решения для данной СУБД совпадает с логикой решения для MySQL, но поскольку работать с вложенными курсорами здесь приходится иначе, снова повторим алгоритм действий со ссылками на соответствующие фрагменты кода.
Итак, для получения решения мы будем должны:
•запустить транзакцию (строка 17);
•открыть курсор для извлечения идентификаторов всех читателей (строка 19);
•для каждого идентификатора читателя выполнить вложенный цикл (строки 23-49), в котором:
o открыть курсор для извлечения трёх идентификаторов случайных книг (строка 25);
o для каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 28-44);
o закрыть курсор для извлечения трёх идентификаторов случайных книг (строка 45);
•закрыть курсор для извлечения идентификаторов всех читателей (строка 50);
•проверить, было ли нарушено условие о недопустимости нахождения на руках у одного читателя более десяти книг (строки 53-66) и:
o если условие было нарушено, отменить транзакцию (строка 60);
o если условие не было нарушено, подтвердить транзакцию (строка 65).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 421/545
Пример 42: управление явными транзакциями
В MS SQL Server нет необходимости использовать отдельные блоки кода для каждого курсора. Вместо этого мы сохраняем значение параметра @@FETCH_STATUS (предоставляющего информацию о последней операции извлечения данных из курсора) в отдельной переменной для каждого из циклов (строки 21, 27, 43, 48), а затем используем эти переменные для организации работы циклов.
MySQL |
Решение 6.1.2.a (код процедуры) |
1CREATE PROCEDURE THREE_RANDOM_BOOKS
2AS
3BEGIN
4DECLARE @s_id_value INT;
5DECLARE @b_id_value INT;
6DECLARE subscribers_cursor CURSOR LOCAL FAST_FORWARD FOR
7SELECT [s_id]
8 FROM [subscribers];
9DECLARE books_cursor CURSOR LOCAL FAST_FORWARD FOR
10SELECT TOP 3 [b_id]
11 |
|
FROM |
[books] |
12 |
|
ORDER BY |
NEWID(); |
13DECLARE @fetch_subscribers_cursor INT;
14DECLARE @fetch_books_cursor INT;
15
16PRINT 'Starting transaction...';
17BEGIN TRANSACTION;
18
19OPEN subscribers_cursor;
20FETCH NEXT FROM subscribers_cursor INTO @s_id_value;
21SET @fetch_subscribers_cursor = @@FETCH_STATUS;
22
23WHILE @fetch_subscribers_cursor = 0
24BEGIN
25OPEN books_cursor;
26FETCH NEXT FROM books_cursor INTO @b_id_value;
27SET @fetch_books_cursor = @@FETCH_STATUS;
28WHILE @fetch_books_cursor = 0
29BEGIN
30INSERT INTO [subscriptions]
31 |
|
([sb_subscriber], |
32 |
|
[sb_book], |
33 |
|
[sb_start], |
34 |
|
[sb_finish], |
35 |
|
[sb_is_active]) |
36 |
|
VALUES (@s_id_value, |
37 |
|
@b_id_value, |
38 |
|
GETDATE(), |
39 |
|
DATEADD(month, 1, GETDATE()), |
40 |
|
N'Y'); |
41 |
|
|
42FETCH NEXT FROM books_cursor INTO @b_id_value;
43SET @fetch_books_cursor = @@FETCH_STATUS;
44END;
45CLOSE books_cursor;
46
47FETCH NEXT FROM subscribers_cursor INTO @s_id_value;
48SET @fetch_subscribers_cursor = @@FETCH_STATUS;
49END;
50CLOSE subscribers_cursor;
51DEALLOCATE subscribers_cursor;
52DEALLOCATE books_cursor;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 422/545
Пример 42: управление явными транзакциями
|
MySQL |
|
Решение 6.1.2.a (код процедуры) |
|||
|
53 |
|
|
IF EXISTS (SELECT TOP 1 1 |
||
|
54 |
|
|
|
FROM |
[subscriptions] |
|
55 |
|
|
|
WHERE |
[sb_is_active]='Y' |
|
56 |
|
|
|
GROUP |
BY [sb_subscriber] |
|
57 |
|
|
|
HAVING COUNT(1)>10) |
|
|
|
|
|
|
|
|
58BEGIN
59PRINT 'Rolling transaction back...';
60ROLLBACK TRANSACTION;
61END
62ELSE
63BEGIN
64PRINT 'Committing transaction...';
65COMMIT TRANSACTION;
66END;
67
68END;
69GO
Для проверки работоспособности полученного решения можно использовать следующие запросы.
MySQL Решение 6.1.2.a (код для проверки работоспособности)
1EXECUTE THREE_RANDOM_BOOKS;
2SELECT * FROM [subscriptions];
На этом решение для MS SQL Server завершено.
Переходим к Oracle. Несмотря на то, что мы уже дважды рассматривали алгоритм решения, здесь мы повторим его снова — в том числе для того, чтобы прослеживая отсылки к коду вы увидели, насколько просто и элегантно реализуется работа с вложенными курсорами в Oracle.
Итак, для получения решения мы будем должны:
•завершить предыдущую транзакцию (строка 17) (напомним, что «запустить транзакцию» в Oracle невозможно, т.к. транзакция всегда активируется первой операцией модификации данных);
•создать цикл для прохода по рядам курсора для извлечения идентификаторов всех читателей (строки 19-35), и внутри этого цикла:
o создать цикл для прохода по рядам курсора для извлечения трёх идентификаторов случайных книг (строки 21-34);
o для каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 23-33);
•проверить, было ли нарушено условие о недопустимости нахождения на руках у одного читателя более десяти книг (строки 37-52) и:
o если условие было нарушено, отменить транзакцию (строка 48);
o если условие не было нарушено, подтвердить транзакцию (строка 51).
Небольшое неудобство в этом решении вызывает только необходимость выяснять существование записей, нарушающих условие задачи, через промежуточную переменную и подзапрос (строки 37-43), что связано с невозможностью применения в Oracle конструкции IF EXISTS. В остальном весь код хранимой процедуры выглядит не сложнее примитивного примера на любом распространённом языке программирования.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 423/545
Пример 42: управление явными транзакциями
Oracle |
Решение 6.1.2.a (код процедуры) |
1CREATE OR REPLACE PROCEDURE THREE_RANDOM_BOOKS
2AS
3counter INT := 0;
4CURSOR subscribers_cursor IS
5SELECT "s_id"
6 FROM "subscribers";
7CURSOR books_cursor IS
8SELECT "b_id"
9FROM
10(SELECT "b_id"
11 FROM "books"
12ORDER BY DBMS_RANDOM.VALUE)
13WHERE ROWNUM <= 3;
14
15BEGIN
16DBMS_OUTPUT.PUT_LINE('Committing previous transaction...');
17COMMIT;
18
19FOR one_subscriber IN subscribers_cursor
20LOOP
21FOR one_book IN books_cursor
22LOOP
23INSERT INTO "subscriptions"
24 |
|
("sb_subscriber", |
25 |
|
"sb_book", |
26 |
|
"sb_start", |
27 |
|
"sb_finish", |
28 |
|
"sb_is_active") |
29 |
|
VALUES (one_subscriber."s_id", |
30 |
|
one_book."b_id", |
31 |
|
SYSDATE, |
32 |
|
ADD_MONTHS(SYSDATE, 1), |
33 |
|
'Y'); |
34END LOOP;
35END LOOP;
36
37SELECT COUNT(1) INTO counter
38FROM
39(SELECT COUNT(1)
40FROM "subscriptions"
41WHERE "sb_is_active"='Y'
42GROUP BY "sb_subscriber"
43HAVING COUNT(1)>10);
44
45IF (counter > 0)
46THEN
47DBMS_OUTPUT.PUT_LINE('Rolling transaction back...');
48ROLLBACK;
49ELSE
50DBMS_OUTPUT.PUT_LINE('Committing transaction...');
51COMMIT;
52END IF;
53
54 END;
Для проверки работоспособности полученного решения можно использовать следующие запросы.
Oracle Решение 6.1.2.a (код для проверки работоспособности)
1SET SERVEROUTPUT ON;
2EXECUTE THREE_RANDOM_BOOKS;
3SELECT * FROM "subscriptions";
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 424/545