Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 41: управление неявными транзакциями
В MS SQL Server нет необходимости использовать отдельные блоки кода для каждого курсора. Вместо этого мы сохраняем значение параметра @@FETCH_STATUS (предоставляющего информацию о последней операции извле-
чения данных из курсора) в отдельной переменной для каждого из циклов (строки 21, 27, 43, 48), а затем используем эти переменные для организации работы циклов.
MySQL і |
Решение 6.1.2.a (код процедуры) |
[ |
||
. 1 .. |
CREATE.. |
PROCEDURE .. |
THREE_RANDOM_BOOKS' |
.......................................... |
2AS
3BEGIN
4DECLARE @s_id_value INT;
5DECLARE @b_id_value INT;
6DECLARE subscribers_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT [s_id]
8FROM [subscribers];
9DECLARE books_cursor CURSOR LOCAL FAST_FORWARD FOR
10 |
SELECT TOP |
3 [b_id] |
|
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
|
INSERT 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 |
|
|
|
|
42 |
FETCH NEXT FROM books_cursor INTO @b_id_value; |
|
||
43 |
SET |
@fetch_books_cursor |
= |
@@FETCH_STATUS; |
44END;
45CLOSE books_cursor
47 |
FETCH |
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 в примерах © Богдан Марчук Стр: 450/545
Пример 41: управление неявными транзакциями
MySQL I |
Решение 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 |
|
58 |
|
BEGIN |
|
59 |
|
PRINT 'Rolling transaction |
; |
60 |
|
ROLLBACK TRANSACTION; |
|
61 |
|
END |
|
62 |
|
ELSE |
|
63 |
|
BEGIN |
|
64 |
|
PRINT 'Committing transaction...'; |
|
65 |
|
COMMIT TRANSACTION; |
|
66 |
|
END; |
|
67 |
|
|
|
68 |
END; |
|
|
69 |
GO |
|
|
|
|
|
|
Для проверки работоспособности полученного решения можно использовать следующие запросы.
MySQL Решение 6.1.2.a (код для проверки работоспособности)
1 EXECUTE THREE_RANDOM_BOOKS;
2 SELECT * FROM [subscriptions];
На этом решение для MS SQL Server завершено.
Переходим к Oracle. Несмотря на то, что мы уже дважды рассматривали алгоритм решения, здесь мы повторим его снова — в том числе для того, чтобы прослеживая отсылки к коду вы увидели, насколько просто и элегантно реализуется работа с вложенными курсорами в Oracle.
Итак, для получения решения мы будем должны:
•завершить предыдущую транзакцию (строка 17) (напомним, что «запустить транзакцию» в Oracle невозможно, т.к. транзакция всегда активируется первой операцией модификации данных);
•создать цикл для прохода по рядам курсора для извлечения идентификаторов всех читателей (строки 19-35), и внутри этого цикла:
осоздать цикл для прохода по рядам курсора для извлечения трёх идентификаторов случайных книг (строки 21-34);
одля каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 23-33);
•проверить, было ли нарушено условие о недопустимости нахождения на руках
уодного читателя более десяти книг (строки 37-52) и:
оесли условие было нарушено, отменить транзакцию (строка 48);
оесли условие не было нарушено, подтвердить транзакцию (строка 51).
Небольшое неудобство в этом решении вызывает только необходимость выяснять существование записей, нарушающих условие задачи, через промежуточную переменную и подзапрос (строки 37-43), что связано с невозможностью применения в Oracle конструкции IF EXISTS. В остальном весь код хранимой процедуры
выглядит не сложнее примитивного примера на любом распространённом языке программирования.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 451/545
Пример 41: управление неявными транзакциями
Oracl |
і |
Решение 6.1.2.a (код процедуры) |
| |
||
e |
|||||
|
|
|
|
||
1 |
CREATE OR REPLACE PROCEDURE THREE RANDOM BOOKS |
||||
2 |
AS |
|
|
|
|
3 |
counter INT := 0; |
|
|||
4 |
CURSOR subscribers cursor IS |
|
|||
5 |
|
SELECT "s id" |
|
||
6 |
|
FROM |
"subscribers"; |
|
|
7 |
CURSOR books cursor IS |
|
|||
8 |
|
SELECT "b id" |
|
||
9 |
|
FROM |
|
|
|
10 |
|
(SELECT "b id" |
|
||
11 |
|
FROM |
"books" |
|
|
12 |
|
ORDER BY DBMS RANDOM VALUE |
|
||
13 |
|
WHERE 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;
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;
3 SELECT * FROM "subscriptions";
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 452/545
Пример 41: управление неявными транзакциями
Решение 6.1.2.b{419}.
В отличие от решения{419} задачи 6.1.2.a{419}, здесь нам даже не понадобятся курсоры. Потому решение сведётся к серии простых действий:
•выполнить изменения;
•проверить, нарушено ли условие задачи, и:
оотменить изменения, если нарушено;
оподтвердить изменения, если не нарушено.
Остаётся только рассмотреть код хранимых процедур. Отличия будут только в способе вычисления интервалов дат и (в Oracle) запуске транзакции. В остальном решения для всех трёх СУБД полностью эквивалентны.
Решение для MySQL выглядит следующим образом.
MySQL |
Решение 6.1.2.b (код процедуры) |
1DELIMITER $$
2CREATE PROCEDURE CHANGE DATES()
3BEGIN
4SELECT 'Starting transaction...';
5START TRANSACTION;
6
7UPDATE 'subscriptions'
8SET 'sb_finish' = DATE_ADD('sb_finish', INTERVAL 3 MONTH);
10 SET @avg read = (SELECT AVG(DATEDIFF('sb finish', 'sb start')) 11 FROM 'subscriptions');
12
13IF @avg read > 120)
14THEN
15SELECT 'Rolling transaction back...';
16ROLLBACK;
17ELSE
18SELECT 'Committing transaction...';
19COMMIT;
20END IF;
21
22END;
23$$
24DELIMITER ;
Для проверки работоспособности полученного решения можно использовать следующий запрос.
MySQL Решение 6.1.2.b (код для проверки работоспособности)
1 CALL CHANGE_DATES()
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 453/545
Пример 41: управление неявными транзакциями
Решение для MS SQL Server выглядит следующим образом.
MS SQL |
Решение 6.1.2.b (код процедуры) |
1CREATE PROCEDURE CHANGE DATES
2AS
3BEGIN
4DECLARE @avg_read DOUBLE PRECISION;
6PRINT 'Starting transaction...';
7 BEGIN TRANSACTION;
8
9UPDATE [subscriptions]
10SET [sb finish] = DATEADD(month, 3 [sb finish]);
12 SET @avg read = (SELECT AVG(DATEDIFF (month, [sb start], [sb finish] ) 13 FROM [subscriptions]);
14
15IF @avg read > 4
16BEGIN
17PRINT 'Rolling transaction back...';
18ROLLBACK TRANSACTION;
19END
20ELSE
21BEGIN
22PRINT 'Committing transaction...';
23COMMIT TRANSACTION;
24END;
25
26END;
27GO
Для проверки работоспособности полученного решения можно использовать следующий запрос.
MS SQL Решение 6.1.2.b (код для проверки работоспособности)
1 EXECUTE CHANGE_DATES
Решение для Oracle выглядит следующим образом.
|
Oracl |
і |
Решение 6.1.2.b (код процедуры) |
I |
e |
|
|||
|
|
|
|
|
1 |
|
CREATE OR REPLACE PROCEDURE CHANGE DATES |
||
2 |
|
AS |
|
|
3 |
|
avg_read NUMBER 5, 3) := 0.0; |
|
|
4 |
|
|
|
|
5BEGIN
6DBMS OUTPUT.PUT LINE('Committing previous transaction...');
7COMMIT;
8
9UPDATE "subscriptions"
10SET "sb finish" = ADD MONTHS "sb finish" 3 ;
12SELECT AVG(MONTHS BETWEEND'sb finish" "sb start" ) INTO avg read
13FROM "subscriptions";
14
15IF (avg read > 4.0
16THEN
17DBMS OUTPUT.PUT LINE('Rolling transaction back...');
18ROLLBACK;
19ELSE
20DBMS OUTPUT.PUT LINE('Committing transaction...');
21COMMIT;
22END IF;
23
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 454/545