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

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

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

Решение для Oracle выглядит следующим образом. Да, именно так и выглядит, т.к. в Oracle нет такого явления, как автоподтверждение транзакций — этот эффект может быть реализован некоторыми средствами работы с СУБД, но сама СУБД всегда ждёт явного COMMITT или ROLLBACK.

Oracle

Решение 6.2.3.b (код функции)

1CREATE FUNCTION NO AUTOCOMMITT

2RETURN INT

3DETERMINISTIC

4IS

5BEGIN

6DBMS OUTPUT.PUT LINE('Have a nice day :)');

7RETURN 1

8END;

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

Oracle Решение 6.2.3.Ь (код для проверки работоспособности решения)

1 SET SERVEROUTPUT ON;

2 SELECT NO AUTOCOMMITT FROM DUAL;

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

ЧРешение 6.2.3.C 465.

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

В MySQL таким уровнем является READ UNCOMMITTED, в MS SQL Server —

тоже READ UNCOMMITTED или SNAPSHOT (но SNAPSHOT может приводить к допол-

нительным расходам ресурсов), в Oracle чтение данных всегда происходит в независимом режиме, потому в этой СУБД можно использовать READ COMMITTED (тем более, что READ UNCOMMITTED в Oracle нет).

Решение для MySQL выглядит следующим образом.

MySQL і

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

[

1DELIMITER $$................. ........................

2CREATE PROCEDURE COUNT_ROWS(IN table_name VARCHAR 150),

3	OUT rows_in_table INT)
4	BEGIN
5	SET SESSION TRANSACTION
6	ISOLATION LEVEL READ UNCOMMITTED;
7
8	SET @count_query =
9	CONCAT('SELECT COUNT(1) INTO @rows_found
10	FROM ' , table_name);
11

12PREPARE count_stmt FROM @count_query

13EXECUTE count_stmt;

14DEALLOCATE PREPARE count_stmt

16SET rows_in_table := @rows_found

17END;

18$$

19DELIMITER ;

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 500/545

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

MySQL Решение 6.2.3.С (код для проверки работоспособности решения)

1CALL COUNT_ROWS('subscriptions', @rows_in_table);

2SELECT @rows in table

Решение для MS SQL Server выглядит следующим образом.


MS SQL		Решение 6.2.3.С (код процедуры) і
1	CREATE PROCEDURE ....COUNTJROWS ...‘ ..........

2	@table_name NVARCHAR 150

3	@rows in table INT OUTPUT

4AS

5DECLARE @count_query NVARCHAR(1000)

7SET TRANSACTION ISOLATION

8LEVEL READ UNCOMMITTED;

10SET @count_query =

11CONCAT('SET @rows_f = (SELECT COUNT(1) FROM [', @table_name, '])');

12EXECUTE sp_executesql @ count_query

13	N'@rows_f INT OUT',
14	@rows in table OUTPUT;
15	GO

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

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

1DECLARE. @res INT ;

2EXECUTE COUNT_ROWS 'subscriptions', @res OUTPUT;

3SELECT @res;

Решение для Oracle выглядит следующим образом.

Oracle і Решение 6.2.3.С (код процедуры)

CREATE PROCEDURE COUNT_ROWS	table_name IN VARCHAR,
	rows_in_table OUT NUMBER) AS count_query
VARCHAR 10001	:= '';
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET
ISOLATION_LEVEL = READ COMMITTED';
count_query := 'SELECT COUNT(1) FROM "'\|\| table_name \|\|		'"';
EXECUTE IMMEDIATE count_query INTO rows_in_table' END;
/

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

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

1DECLARE

2res NUMBER;

3BEGIN

4COUNT ROWS('subscriptions', res ;

5DBMS OUTPUT.PUT LINE('Rows: ' || res);

6END;

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

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 501/545

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

Задание 6.2.3.TSK.A: создать на таблице subscriptions триггер, определяющий уровень изолированности транзакции, в котором сейчас проходит операция обновления, и отменяющий операцию, если уровень изолированности транзакции отличен от REPEATABLE READ.

Задание 6.2.3.TSK.B: создать хранимую функцию, порождающую исключительную ситуацию в случае, если выполняются оба условия:

•режим автоподтверждения транзакций выключен;

•функция запущена из вложенной транзакции.

Подсказка: эта задача имеет решение только для MS SQL Server.

Задание 6.2.3.TSK.C: создать хранимую процедуру, выполняющую подсчёт количества записей в указанной таблице таким образом, чтобы она возвращала максимально корректные данные, даже если для достижения этого результата придётся пожертвовать производительностью.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 502/545

Пример 46: формирование и анализ иерархических структур

Раздел 7: Решение типичных задач и выполнение типичных операций

7.1.Работа с иерархическими и связанными структурами

7.1.1.Пример 46: формирование и анализ иерархических структур

Для решения задач из данного примера нам понадобится новая таблица, которую мы создадим в базе данных «Исследование». Эта таблица будет хранить дерево элементов (допустим, что это будет древовидная структура сайта нашей гипотетической библиотеки).

Существует множество способов хранения древовидных структур в реляционных базах данных39, но мы используем рекурсивные внешние ключи как одно из самых распространённых и универсальных решений. Соответствующие фрагменты схемы БД для всех трёх СУБД представлены на рисунке 7. а.

dm MySQL	dm SQLServer2012	dm Oracle
		dm Oracle

site_pages

«column»

*PK sp_id: INT FK sp_parent: INT

sp_name: VARCHAR(200)

«FK»

+FK_site_pages_site_pages(INT)

«PK»

+PK_site_pages(INT)

0..‡‡‡‡‡‡‡‡‡‡ §§§§§§§§§§

(sp_parent = sp_id)

«FK»

+PK_ate_pages

+FK_site_pages_9te_pages

«FK»

site_pages

«column»

*PK sp_id: int FK sp_parent: int

sp_name: nvarchar(200)

+FK_site_pages_site_pages(int)

«PK»

+PK_site_pages(i nt)

0..*

/\ 1

(sp_parent = sp_id)

«FK»

+ FK_site_pages_site_pages+PK_site_Pages

«FK»

+ FK_site_pages_site_pages(NUMBER)

«PK»

site_pages

+PK_site_pages(NUMBER)

0..*	/\ 1
(sp parent = sp id)
«FK»	.

+FK_site_pages_9te_pages +PK_site_Pages

	MySQL	MS SQL Server	Oracle
	Рисунок 7.а — Таблица site_pages во всех трёх СУБД
Сохраним в таблице site_pages следующий набор данных, визуально
представленный на рисунке 7.b.

sp_id	sp_parent	sp_name
1	NULL	Главная
2	1	Читателям
3	1	Спонсорам
4	1	Рекламодателям
5	2	Новости
6	2	Статистика
7	3	Предложения
8	3	Истории успеха
9	4	Акции
10	1	Контакты
11	3	Документы
12	6	Текущая
13	6	Архивная
14	6	Неофициальная

39 http://www.amazon.eom/dp/1558609202/

«column»

§§§§§§§§§§PK sp_id: NUMBER(10)

FK sp_parent: NUMBER(10) sp_name: NVARCHAR2(200)

Пример 46: формирование и анализ иерархических структур

Рисунок 7.b — Визуальное представление карты сайта

Теперь, когда все данные подготовлены, мы можем переходить к задачам.

Задача 7.1.1.a{476}: создать функцию, возвращающую список идентификаторов всех дочерних вершин заданной вершины (например, идентификаторов всех подстраниц страницы «Читателям»).

Задача 7.1.1.b{482}: написать запрос для показа всего поддерева заданной вершины дерева, включая саму родительскую вершину (например, всех подстраниц страницы «Читателям», включая саму эту страницу).

Задача 7.1.1.c{485}: написать функцию, возвращающую список идентификаторов вершин на пути от заданной вершины к корню дерева (например, идентификаторов всех вершин на пути от страницы «Архивная» к странице

«Главная»).

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

Для вершины с идентификатором 2 (страница «Читателям») функция должна возвратить следующие данные: shildren_of_2

5,6,12,13,14

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

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

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