Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

Пример 41: управление неявными транзакциями

ции данных происходит внутри транзакции), в начале работы нашей хранимой процедуры мы должны выполнить операцию COMMIT (строка 12), чтобы завершить те-

кущую транзакцию (если она есть).

В строках 16-27 мы выполняем цикл вставки, в котором мы можем явно инициировать подтверждение вставки каждой отдельной записи (строка 24), если нам нужно эмулировать режим автоподтверждения неявных транзакций. Если такая эмуляция не нужна, то все выполненные в цикле вставки подтверждаются как набор операций (строка 35).

 

Oracl

і

Решение 6.1.1.b (код процедуры)

I

e

 

 

 

 

 

1

 

CREATE OR REPLACE PROCEDURE TEST INSERT SPEED records count IN INT,

2

 

 

 

use autocommit IN INT,

3

 

 

 

total time OUT NVARCHAR2)

4AS

5counter INT := 0;

6start time TIMESTAMP;

7finish time TIMESTAMP;

8diff time INTERVAL DAY TO SECOND;

9BEGIN

10DBMS OUTPUT.PUT LINE('Autocommit value = ' || use autocommit ;

11DBMS OUTPUT.PUT LINE('Committing previous transaction...');

12COMMIT;

13DBMS OUTPUT.PUT LINE('Starting insert of ' || records count ||

14

' records...');

15start time := CURRENT TIMESTAMP;

16WHILE (counter < records count

17LOOP

18INSERT INTO "subscribers"

19

("s name")

20VALUES (CONCAT('New subscriber ', (counter + 1 ));

21IF use autocommit = 1)

22THEN

23DBMS OUTPUT.PUT LINE('Committing small transaction...');

24COMMIT;

25END IF;

26counter := counter + 1;

27END LOOP;

28finish time := CURRENT TIMESTAMP;

29DBMS OUTPUT.PUT LINE('Finished insert of ' || records count ||

30

' records...');

31

 

32IF use autocommit = 0

33THEN

34DBMS OUTPUT.PUT LINE('Committing one big transaction...');

35COMMIT;

36END IF;

37

38diff time := finish time - start time

39total time := TO CHAR(EXTRACT(hour FROM diff time ) || ':' ||

40

TO CHAR(EXTRACT(minute

FROM

diff time))

|| ':' ||

41

TO_CHAR(EXTRACT(second

FROM

diff_time ),

'fm00.000000' );

42

 

 

 

 

43DBMS OUTPUT.PUT LINE('Time used: ' || total time);

44END;

Для проверки работоспособности и оценки производительности Oracle в двух режимах работы с неявными транзакциями можно использовать следующие запросы.

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 445/545

Пример 41: управление неявными транзакциями

 

Oracl

і Решение 6.1.1.b (код для проверки работоспособности)

I

e

 

 

 

1

DECLARE

 

 

2

t NVARCHAR2(100);

 

 

3

BEGIN

 

 

4

TEST INSERT SPEED 10, 1

t ;

 

5

DBMS OUTPUT.PUT LINE('Stored procedure has returned

6

The following value: ' || t ;

7

END;

 

 

8

 

 

 

9

DECLARE

 

 

10

t NVARCHAR2(100);

 

 

11

BEGIN

 

 

12

TEST INSERT SPEED 10, 0, t ;

 

13

DBMS OUTPUT.PUT LINE('Stored procedure has returned

14

The following value: ' || t ;

15

END;

 

 

На этом решение данной задачи завершено.

Задание 6.1.1.TSK.A: сравнить скорость работы представленной в решении{413} задачи 6.1.1.b{408} хранимой процедуры при вставке в обоих режимах автоподтверждения неявных транзакций для 10, 100, 1000, 10000, 100000 записей во всех трёх СУБД.

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 446/545

Пример 41: управление неявными транзакциями

6.1.2.Пример 42: управление явными транзакциями

ОЗадача 6.1.2.a{419}: создать хранимую процедуру, которая:

•добавляет каждому читателю три случайных книги с датой выдачи, равной текущей дате, и датой возврата, равной «текущая дата плюс месяц»;

•отменяет совершённые действия, если по итогу выполнения операции хотя бы у одного читателя на руках окажется более десяти книг.

ОЗадача 6.1.2.b{425}: создать хранимую процедуру, которая:

•изменяет все даты возврата книг на «плюс три месяца»;

•отменяет совершённое действие, если по итогу выполнения операции среднее время чтения книги превысит 4 месяца.

Ожидаемый результат 6.1.2.a.

Хранимая процедура выполняет указанные в условии задачи действия, в конце своей работы сигнализируя о фиксации или отмене внесённых изменений.

Ожидаемый результат 6.1.2.b.

Хранимая процедура выполняет указанные в условии задачи действия, в конце своей работы сигнализируя о фиксации или отмене внесённых изменений.

Решение 6.1.2.a{419}.

Решение данной задачи для MySQL будет примечательно рассмотрением способа работы с несколькими курсорами внутри одной хранимой процедуры.

Для получения решения мы будем должны:

•запустить транзакцию (строка 5);

•открыть курсор для извлечения идентификаторов всех читателей (строка 15);

•для каждого идентификатора читателя выполнить вложенный цикл (строки 16-55), в котором:

ооткрыть курсор для извлечения трёх идентификаторов случайных книг (строка 13);

одля каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 33-51);

озакрыть курсор для извлечения трёх идентификаторов случайных книг (строка 52);

•закрыть курсор для извлечения идентификаторов всех читателей (строка 56);

•проверить, было ли нарушено условие о недопустимости нахождения на руках у одного читателя более десяти книг (строки 58-70) и:

оесли условие было нарушено, отменить транзакцию (строка 66);

оесли условие не было нарушено, подтвердить транзакцию (строка 69).

Несмотря на громоздкость синтаксиса и длительное описание, сам алгоритм тривиален: это обычный вложенный цикл. Что в этой задаче представляет интерес, так это уже упомянутая ранее работа с двумя курсорами.

Обратите внимание, что конструкция DECLARE CONTINUE HANDLER FOR NOT FOUND SET не подразумевает указание имени курсора, т.е. предполагается, что он

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 447/545

Пример 41: управление неявными транзакциями

один. В ситуациях, когда необходимо использовать несколько курсоров, используются т.н. «блоки кода», ограничивающие область видимости переменных. В нашем случае таких блоков два, второй вложен в первый, и расположены они в строках 757 и 22-53 соответственно).

MySQL

Решение 6.1.2.a (код процедуры)

 

1

DELIMITER ............. $$

 

2

CREATE PROCEDURE THREE_RANDOM_BOOKS()

 

BEGIN

 

 

3

 

 

SELECT 'Starting transaction...';

 

4

 

START TRANSACTION;

 

5

 

 

 

 

6

USERS: BEGIN

 

 

7

 

 

DECLARE

s_id_value INT DEFAULT 0

 

8

 

DECLARE

subscribers_done

INT DEFAULT 0;

9

DECLARE

subscribers_cursor CURSOR FOR

 

10SELECT 's_id'

11FROM 'subscribers';

12DECLARE CONTINUE HANDLER FOR NOT FOUND SET subscribers_done = 1

14OPEN subscribers_cursor;

15read_users_loop: LOOP

16FETCH subscribers_cursor INTO s_id_value;

17IF subscribers_done THEN

18LEAVE read_users_loop;

19END IF;

20

21BOOKS: BEGIN

22DECLARE b_id_value INT DEFAULT 0;

23DECLARE books_done INT DEFAULT 0;

24DECLARE books_cursor CURSOR FOR

25SELECT 'b_id'

26FROM 'books'

27ORDER BY RAND()

28LIMIT 3;

29DECLARE CONTINUE HANDLER FOR NOT FOUND SET books_done = 1 OPEN

30books_cursor;

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

read_books_loop: LOOP

FETCH books_cursor INTO b_id_value IF books_done THEN

LEAVE read_books_loop END IF;

INSERT INTO 'subscriptions' ('sb_subscriber',

'sb_book', 'sb_start', 'sb_finish', 'sb_is_active'I

VALUES (s_id_value, b_id_value, NOW() , NOW() + INTERVAL 1 MONTH, 'Y');

END LOOP read_books_loop;

CLOSE books_cursor

END BOOKS;

END LOOP read_users_loop

CLOSE subscribers_cursor

END USERS;

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 448/545

Пример 41: управление неявными транзакциями

MySQL I

Решение 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" (); 2 SELECT * FROM 'subscriptions';

На этом решение для MySQL завершено.

Переходим к MS SQL Server. Общая логика решения для данной СУБД совпадает с логикой решения для MySQL, но поскольку работать с вложенными курсорами здесь приходится иначе, снова повторим алгоритм действий со ссылками на соответствующие фрагменты кода.

Итак, для получения решения мы будем должны:

•запустить транзакцию (строка 17);

•открыть курсор для извлечения идентификаторов всех читателей (строка 19);

•для каждого идентификатора читателя выполнить вложенный цикл (строки 2349), в котором:

ооткрыть курсор для извлечения трёх идентификаторов случайных книг (строка 25);

одля каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 28-44);

озакрыть курсор для извлечения трёх идентификаторов случайных книг (строка 45);

•закрыть курсор для извлечения идентификаторов всех читателей (строка 50);

•проверить, было ли нарушено условие о недопустимости нахождения на руках

уодного читателя более десяти книг (строки 53-66) и:

оесли условие было нарушено, отменить транзакцию (строка 60);

оесли условие не было нарушено, подтвердить транзакцию (строка 65).

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 449/545

Источник: https://studfile.net/preview/16420333/

Смотрите также:

_-___-~1
_3 тема - Диффузия
_Роджерс Карл, Брак и его альтернативы
'Заповит' Рейнгольда Глиэра: стилистические особенности
'Создание и продвижение интернет - платформы реферальной системы' на примере компании Бартер–кард
[БИЛЕТЫ] IMK №3
[ТЕСТЫ] ИМК №1 по физиологии (база 1 семестр и на экз)
[ТЕСТЫ] Спланхнология
[ТЕСТЫ] ЦНС - ЛУЧШИЕ 2020
+ Проблемы криминологии. Курс лекций