2.57M

Category:

database

Работа в PostgreQL. Триггеры, транзакции, события

1.

РАБОТА В СУБД
POSTGRESQL
Триггеры PL/pgSQL

Триггеры
—
это
именованные
программные
бл оки, выполняемые в ответ на происходящие в
Триггеры
базе данных события
Они относятся к числу важнейших элементов
профессионально спроектированных прил ожений
PostgreSQL

3.

В
аргументе
триггер
ук азывается
произвольное имя создаваемого триггера.
Имя может совпадать с именем триггера, уже
существующего в базе данных
CREATE TRIG
GER триггер
что
этот
триггер
при усл овии,
установлен
для
другой таблицы.
Кроме
того,
по
аналогии
с
большинством
других несистемных объектов баз данных, имя
триггера (в сочетании с таблицей, для которой
он устанавливается) должно быть уник альным
лишь в к онтек сте базы данных, в которой он
создается

4.

Ключевое
сл ово
означает,
BEFORE
что
функция должна выполняться
перед попытк ой
выполнения
включая
операции,
{ BEFORE
встроенные
проверки
| AFTER }
к оманд INSERT и DELETE.
реализуемые
ограничений
при
все
данных,
выполнении
Ключевое слово AFTER означает, что функция
вызывается
после
завершения
приводящей в действие триггер
операции,

5.

{ событие [
События
OR событие
При
SQL,
поддерживаемые
в PostgreSQL: INSERT, UPDATE или DELETE
перечислении
неск ольких
событий
в
к ачестве разделителя используется ключевое
... ] }
сл ово OR

6.

ON таблица
Имя таблицы, модифик ация которой заданным
событием приводит к срабатыванию триггера

7.

FOR EACH
{ ROW | STATEME
NT}
Ключевое
сл ово,
к онструкцией
FOR
EACH
следующее
и
за
определяющее
количество вызовов функции при наст уплении
ук азанного события
Ключевое слово ROW означает, что функция
вызывается
для
каждой
модифицируемой
записи.
Если функция должна вызываться всего один
раз для всей команды, используется ключевое
сл ово STATEMENT

8.

EXECUTE PROCED
URE
функция
( аргументы )
Имя
вызываемой
функции
На
практик е
аргументы
с
аргументами.
при
триггерных функций не используются
вызове

9.

Триггерная
функция
В
триггерных
специальные
функциях
используются
переменные,
содержащие
информацию о сработавшем триггере

10.

1. Реализация
нельзя
сл ожных
обеспечить
бизнес-правил,
с
помощью
которые
ограничений
цел остности
2. Контроль
изменений,
например,
проверк а
вносимых в таблицы изменений
Применение
3. Автоматизация сопровождения базы данных
4. Точная настройк а ограничений на выполнение
административных операций
5. Реализация
сл ожных
правил
безопасности
6. Сбор статистической информации
обеспечения

11.

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

12.

Применение триггеров
Например, мы можем делать запись в журнале об изменении.
Вместо написания двух отдельных запросов (один - для изменения данных,
другой для внесения записи в журнал), можно написать триггер, который будет
содержать правил о:
“Когда бы ни изменялась строк а, создать новую строк у в другой таблице, чтобы
сообщить, что были сделаны изменения”.
В цел ом так ой подход способствует улучшению производительности.

13.

Типы
событий,
которые
запускают
триггер
Операции
вставки,
обновления,
удаления
строк
Выполнение операций определения данных
для создания и редактирования объектов
базы данных
Системные
события:
запуск
и
остановк а
базы данных, вход пользователя в систему,
возникновение ошибок сервера

14.

С тр о ков ы й три г г е р
- з а п ус к а етс я од ин р аз дл я к а ждо й с т р о к и
т а бл и ц ы
Виды
триггеров
Та бл и ч н ы й три г г е р – в ы полн я етс я од ин р аз , н ез а в ис им о от
т о го , с к ол ь к о с т р о к и с од е р жи т з а п ус к а ю ще е е го п р и л о ж е н и е
К оло н оч н ы й тр и г г е р

15.

Триггер DML
Типы
Триггеры замещения ( instead of)
триггеров
Событийные триггеры (event triggers)

16.

Активация триггеров DML – при выполнении
предл ожений insert, delete, update
Для
Порядок
активации
триггеров
к аждого
предл ожения
создано 4 триггера
может
быть

17.

ТРИГГЕРЫ
УРОВНЯ
КОМАНД DML

18.

Три г г е р B EFO R E. Вы зываетс я до внесе ния к а ки хл и б о и з м е н е ни й ( н а п рим е р, BEFO R E I N SERT) .
Три г г е р
Основные
AF TER .
Вып олня ется
д ля
отдел ьной
к о м анды SQL, к отор ая может обр абатывать одн у
и ли б ол ее з апис ей базы да нны х (на прим е р, AFTER
U PD ATE) .
концепции
триггеров
Три г г е р
уровня
коман ды .
Вы полняетс я
для
к о м анды SQL в цел о м ( к отор ая может обр абаты вать
од н у и л и н е с к ол ь к о с т р о к баз ы д а н н ых) .
Три г г е р
ур овня
запи си .
Вып олн яется
для
отдел ьн ой запис и, об ра баты вае м ой к о м ан дой SQL .
Ес ли, п редпол ожи м, таблиц а bo oks с оде ржит 10 00
с тро к,
то
м оди фиц ирует
сл ед ую щая
все
к ом анд а
U PD ATE
эт и
с тр оки :
U PD ATE b o o k s S E T t i t l e = U PPER ( t i tl e );

19.

Пс ев дозапи сь N EW. С трук т ур а д анны х с и мен ем
N EW та к же вы гля ди т и о бла дает ( поч ти ) так ими же
с войс т ва ми, к а к з апис ь PL/ p gSQL . Эта псе вдоза пис ь
Основные
д ос т упн а
толь к о
вн ут ри
тр иггеро в
о бно вления
и
вста вк и ; он а с оде ржит значе ния м од ифи ци ро ванн ой
з а п и с и п о с л е вн е с е ния и з м е н е н ий .
концепции
триггеров
Пс ев дозапи сь OLD. С трук т ур а дан ны х с и ме нем
O L D та к же вы гля ди т и обла дает (поч т и) такими же
с войс т ва ми, к а к з апис ь PL/ p gSQL . Эта псе вдоза пис ь
д ос т упн а
толь к о
вн ут ри
тр иггеро в
о бно вления
и
вста вк и ; он а с оде ржит значе ния м од ифи ци ро ванн ой
з а п и с и д о вн е с е н ия и з м е н е ний .
С ек ц ия W H EN. Час т ь три ггер а DM L, опр ед еляю щая
ус л о вия вы пол нени я к од а тр иггера (и поз вол яю щая
и збежат ь л и ш н их о п е р а ций ) .

20.

По
умолчанию
триггеры
DML
участвуют
в
транзакциях, из к оторых они запущены. Это
означает, что:
если триггер инициирует исключение, будет
Триггеры в
транзакциях
выполнен
отк ат
соответствующей
части
транзакции;
если триггер сам выполнит команду DML
(например,
журнал),
вставит
она
запись
станет
в
таблицу
частью
главной
транзакции;
в триггере DML нельзя выполнять к оманды
COMMIT и ROLLBACK.

21.

СОЗДАНИЕ ТРИГГЕРА DML
Если задана секция FOR EACH ROW, триггер будет запускаться для каждой обрабатываемой
командой строки

22.

Доступ к
новым и
старым
данным OLD
и NEW

23.

Пример
Создадим триггер

24.

Пример

25.

26.

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

27.

Пример Создание
триггера-генератора для
таблицы поставщиков s
•eсли не указан номер поставщика –
он генерируется по схеме – S+
уникальный номер из
последовательности;
•eсли не указано имя поставщика –
оно генерируется по схеме –
Postawchik_ + уникальный номер из
последовательности;
•eсли не указан город – ставится
значение по умолчанию –
“Novosibirsk” ;
•если не указан рейтинг или рейтинг
<=0 – устанавливается рейтинг = 10
для поставщиков из Novosibirskа и 0
для всех остальных.

28.

Выполнение неск олько проверок при добавлении
или изменении строки в таблице с отрудник ов.
Содержимое
полей
псевдозаписи
NEW
передается отдельным программам проверки:
TRIGGER validate_employee_changes AFTER
Пример
INSERT OR UPDATE ON employees FOR EACH
ROW
BEGIN
check_date (:NEW.hire_date );
check_email (:NEW.email);
END;

29.

Триггер,
запуск аемый
проверяет изменения,
ceo_compensation .
перед
вставкой
производимые в
данных,
таблице
Для с охранения новой строки таблицы аудита вне
главной транзакции в нем используется технология
автономных транзакций:
Пример
TRIGGER bef_ins_ceo_comp BEFORE INSERT ON
ceo_compensation
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ceo_comp_history VALUES
(:NEW.name, : OLD.compensation ,
:NEW.compensation , 'AFTER INSERT', SYSDATE);
COMMIT;
END;

30.

В большинстве случаев секция
WHEN содержит ссылки на поля
псевдозаписей NEW и OLD
Предл ожение
уточнения
предназначено
WHEN
усл овий,
при
которых
для
должен
выполняться к од триггера.
Основной код триггера должен быть реализован
Предложение
только при изменении значения столбца salary:
WHEN
ON employees
TRIGGER check_raise AFTER UPDATE OF salary
FOR EACH ROW
WHEN ((OLD.salary != NEW.salary) OR
Если при обновлении записи
пользователь по какой-то причине
оставит salary текущее значение,
триггер активизируется, но его
основной код выполняться не
будет
(OLD.salary IS NULL AND NEW.salary IS NOT
NULL) OR (OLD.salary IS NOT NULL AND
NEW.salary IS NULL))
BEGIN ...

31.

При использовании WHEN следует соблюдать ряд
правил:
Все л огические выражения всегда должны
заключаться в круглые ск обки. Эти ск обки не
обязательны в к оманде IF, но необходимы в
секции WHEN триггера.
Перед идентифик аторами OLD и NEW не
должно стоять двоеточие (:). В секции WHEN
следует
использовать
тольк о
встроенные
функции.
Пользовательские
функции
и
функции,
определенные во встроенных пакетах (таких,
к ак DBMS_UTILITY), в нем вызывать нельзя.
Чтобы вызвать так ую функцию, переместите
с оответствующ ую логик у в начало исполняемого
раздела триггера.

32.

Для
Несколько
правил,
которые
следует
принимать во
внимание при
работе с
псевдозаписям
и NEW и OLD
триггеров,
связанных
с
командой
INSERT,
струк т ура OLD не содержит данных, поскольк у старого
набора значений у операции вставки нет.
Для
триггеров,
связанных
с
к омандой
UPDATE,
заполняются обе струк т уры, OLD и NEW.
Структ ура OLD с одержит исходные значения записи до
обновления,
а
NEW
—
значения,
к оторые
будут
с одержаться в строк е после обновления .
Для
триггеров,
связанных
с
командой
DELETE,
заполняется тольк о струк т ура OLD, а структ ура NEW
остается пустой, поск ольк у запись удаляется.
Псевдозаписи NEW и OLD также содержат столбец
ROWID,
который
в
обеих
псевдозаписях
всегда
заполняется одинак овыми значениями.
Значения полей записи OLD изменять нельзя; попытк а
приведет
к
ошибк е
ORA04085.
Значения
струк т уры NEW модифицировать можно.
полей

33.

Несколько
правил,
которые
следует
принимать во
Структ уры NEW и OLD нельзя передавать в к ачестве
параметров процедурам или функциям, вызываемым
из
триггера.
Разрешается
отдельные
поля.
В
содержится
программа,
к опирования
данных
передавать
сценарии
которая
NEW
и
лишь
их
gentrigrec.sp
генерирует
OLD
в
код
записи,
передаваемые в параметрах.
В ссылк ах на структ уры NEW и OLD в анонимном
внимание при
бл оке триггера перед соответствующими ключевыми
работе с
:NEW.salary > 10000 THEN...
псевдозаписям
и NEW и OLD
сл овами
Над
необходимо
структурами
NEW
ставить
и
OLD
двоеточие:
IF
нельзя
выполнять
операции
уровня
записи.
Например,
следующая
команда
вызовет
ошибк у
к омпиляции
триггера:
BEGIN :new := NULL; END; .

34.

PostgreSQL предоставляет набор функций (также
называемых операционными директивами) для
идентифик ации к оманды DML, вызвавшей запуск
триггера:
Идентификация
команды DML в
триггере
INSERTING — возвращает TRUE, если триггер
запущен в ответ на вставк у записи в таблицу, с
к оторой он связан, и FALSE в противном случае.
UPDATING — возвращает TRUE, если триггер
запущен в ответ на обновление записи в таблице,
с к оторой он связан, и FALSE в противном
случае.
DELETING — возвращает TRUE, если триггер
запущен в ответ на удаление записи из таблицы,
с к оторой он связан, и FALSE в противном
случае.

35.

Можно создать один триггер, который объединяет действия
для нескольких операций

36.

Функция UPDATING
TRIGGER validate_update
имеет перегруженную
BEFORE UPDATE ON account_transaction
версию, которой в
FOR EACH ROW
качестве аргумента
передается имя
конкретного столбца.
BEGIN
IF UPDATING ('ACCOUNT_NO')
Перегрузка функций
THEN
является удобным
errpkg.raise('Account number cannot be
способом изоляции
updated');
операций обновления
END IF;
отдельных столбцов
END;

37.

Составные
триггеры

38.

Триггеры замещения можно создавать только
для представлений
Триггеры
замещения
В
отличии
вместо
от
DML
предл ожений
триггеров
DML,
выполняются
вызывающих
их
срабатывание
Триггеры замещения должны быть строк овыми
триггерами

39.

Пример

40.

Событийные
триггеры
С р а бат ы в а ю т н а п р ед л о ж е н и я D D L
( c r e a t e , a l t e r, d r o p )
П р о ц ед у р а , к ото р а я в ы з ы ва ет с я
к а к с о б ы т и й н ы й т р и г ге р , д ол ж н а
о бъ я вл я т ь с я б ез а р г ум е н т о в
Ти п о м во з в р а щ а е м о го з н ач е н и я
д ол ж е н б ы т ь e v e n t _ t r i g g e r

41.

42.

СОЗДАНИЕ ТРИГГЕРА DDL

43.

Событийные
триггеры
tg_event — событие, вызывающее триггер

44.

create or replace trigger SUB_COUNT before
insert on users
for each row
Пример1
Триггер, в котором в
declare
tot int;
заранее изменяет
begin
значение поля другой
select num into tot from itog;
таблицы при вставке
tot := tot+1;
записи
update itog set num=tot;
end;

45.

ПРИМЕР 2. Триггер для
формирования значения
первичного ключа для
таблицы users
Проверка работы триггера

46.

ПРИМЕР 3. Запись логов
Проверка работы

47.

ПРИМЕР 4. Триггер,
оповещающий о создании
объектов базы данных

48.

Таблицы
customer__details
CREATE TABLE customer__details
(
customer__id number(10) primary key,
ПРИМЕР 5. Триггер
customer__name varchar2(20),
country varchar2(20)
INSTEAD OF, который
);
будет вставлять строки в
projects__details
соответствующие
(
CREATE TABLE projects__details
projec t _ _ i d number (1 0 ) primar y key,
таблицы представления,
project__name varchar2(30),
project__start__Date date,
когда мы выполняем
оператор вставки в
представлении
customer__id number(10) references customer__details(customer__id)
);
Представление
CREATE OR REPLACE VIEW customer__projects__view AS
SELECT cust.customer__id, cust.customer__name, cust.country,
projectdtls.project__id, projectdtls.project__name,
projectdtls.project__start__Date
FROM customer__details cust, projects__details projectdtls
WHERE cust.customer__id = projectdtls.customer__id;

49.

INSERT INTO customer_details
(customer_id,customer_name,country)
VAL U E S ( : n e w . c u s t o m e r _ i d , : n e w . c u s t o m e r _ n a m e , : n e w . c o u n t r y ) ;
Тр и г г е р
C R E AT E O R R E P L AC E
TRIGGER
INSERT INTO projects_details (project_id, project_name, project_start_Date,
customer_id)
VAL U E S (
t r g _ c us t _ p r oj _ v i ew _ i n s e r t
: new.project_id ,
I N S T E AD O F I N S E R T O N
c u s t om e r _ pr oj e c t s _ v i ew
D E C L AR E
d u p l i c a t e_i nf o E X C E P T I O N ;
: n e w. p r o j e c t _ n a m e ,
: new.project_start_Date ,
: new.customer_id );
P R AG M A E X C E P T I O N _ I NI T
( d u p l i c a t e _ i nf o , - 0 0 0 0 1 ) ;
EXCEPTION
BEGIN
WHEN duplicate_info THEN
R AI S E _ AP P L I C AT I O N _ E R R O R (
num=> -20107,
msg=> 'Duplicate customer or project id');
END trg_cust_proj_view_insert;

50.

1. Создать таблицу Sclad, состоящую из одного
поля
–
для
хранения
количества
книг.
Создайте триггер, с помощью к оторого при
добавлении
книги
изменяется
значение
к оличества книг в таблице Sclad
2. Создайте
Задание
значения
триггер,
который
ключевого
при
изменении
значения
автора
происходит автоматическ ое изменение всех
кодовых значений автора в таблице book
3. Создайте
таблицу
Log
–
журнал
событий
(Номер операции, Операция, Дата). Создайте
триггер
для
журналирования
–
при
выполнении операции вставки, изменения и
удаления записи добавлять номер записи,
дат у и время, тип операции

51.

1. Создайте
триггер
для
автоматизации
заполнения полей БД ИЗ
2. Создайте триггер для хранения л огов
ДЗ
3. Триггер,
который
объединяет
неск ольких операций
4. DDL триггер
5. Триггер INSTEAD
6. Триггер события базы данных
действия

52.

Задание для самостоятельной работы
(пример)
Д а н ы т а бл и ц ы : М АГА З И Н ( К о д _ м а г а з и н а , Н а з ва н и е , Ад р е с , О б о р от ) , ТО ВАР Ы ( К о д _ т о в а р а ,
То ва р , Ц е н а ) , З АК А З Ы ( К о д _ м а г а з и н а , К о д _ т о ва р а , Д а т а _ з а к а з а , К ол и ч е с т в о , С ум м а ) .
1.
С о з д а й т е т р и г г е р п о п о д д е р ж а н и ю з н ач е н и й в н е ш н и х к л юч е й
2.
С о з д а й т е т р и г г е р , к от о р ы й п р и д о б а вл е н и и н о во г о т о ва р а в т а бл и ц у З АК А З Ы б уд е т
з а п ол н я т ь з н ач е н и е п ол я С У М М А.

53.

1. С дел ат ь табл иц у с историе й и змен ени й co mp an y_n ame , в
к оторую п ри об но влени и чер ез т риг ге р за писы ваются
прежн ие з наче ния и дата о к о н чани я их де йст вий (д ата
о бно влен ия) .
С дел айте
зап рос,
к отор ый
п ок аз ы вает
з н ачен ия д ля к о нк ретно го к о ртежа н а з ад анны й мом ен т
вр е м е н и .
Задания
2. Создат ь фун к ц ию с вход ны м пар а метр о м - и мя а к те ра .
Ес ли а к тер с та ки м и мен ем не най ден, фун кция д олжна
вер н ут ь 0. Есл и найд ен, то вы чис ли т ь ч е рез pers on_i nfo
b irth date ( info _t ype_ id =21) и deat h date ( info _t ype_ id = 23 )
воз рас т а к те ра . Ес ли а к тер еще не ум ер, то вы ч исли т ь,
с к ол ь к о сей ч ас лет. Ф ун к ция д олж на верн ут ь цел о е чис л о
п р ожит ых л ет.
3.
Ис пол ьзуя
фун кцию,
с оздан н ую
р ане е,
с озд айте
пр оцед уру, к оторая по и мен и а к тера выводи т в л о г те к с т
с л ед ую щего с од е р жани я :
Ес ли а к тер не на йде н или не уда л ос ь опр едели т ь е го
пе р вую рол ь, то процед ур а д олжн а за верш и т ься с о шиб к ой
и вы ве с т и в л о г " I n va l i d d a t a "

54.

РАБОТА В СУБД
POSTGRESQL
В простейшем
операции
случае
транзакция
состоит
из
1
Транзакция
–
это
средство
обеспечение
согласованности (непротиворечивости) базы данных,
наряду с ограничениями цел остности.
Транзакция
переводит
базу
данных
согласованного
состояния
в
другое
согласованное состояние
из
одного
так ое
же
Транзакция – это
последовательность действий с
БД, в которой либо все действия
выполняются успешно, либо не
выполняется ни одно из них
Суть транзакции в том, что она
объединяет последовательность
действий в одну операцию «всё
или ничего»

55.

Atomicity (Атомарность) гарантирует, что результаты
работы транзакции не будет зафиксированы в системе
частично. Будут либо выполнены все её операции, либо
не выполнено ни одной
ACID
Consistency
(Согласованность)
после
выполнения
транзакции база данных должна быть в с огласованном
(цел остном)
с остоянии.
Во
время
выполнения
транзакции (после выполнения отдельных операций в
рамк ах транзакции) с огласованность не требуется
Isolation (Изолированность) во время выполнения
транзакции
другие
транзакции
(выполняющиеся
параллельно) не должны ок азывать влияние на результат
транзакции
Durability
(Устойчивость,
долговечность)
при
успешном завершении транзакции результаты ее работы
должны остаться в системе независимо от возможных
сбоев оборудования, системы и тд

56.

В PostgreSQL
транзакция
определяется
BEGIN;
набором SQL-
Изменение в бд
команд,
-- ...
окружённым
COMMIT;
командами BEGIN
и COMMIT

57.

PostgreSQL
Система
транзакций
поддерживает
очень
мощную
и
надежную модель транзакций
Код
приложения
последовательность
определяет
выполняемых
л огическую
операций,
результаты к оторой должны быть сохранены
или отменены

58.

Транзакция
начинается с
Завершается
- Выполнением Commit или Rollback
- Нормальным
выполнения
пользователя
первого sql
- Возникновение
оператора
завершением
(автоматически
сеанса
фиксируются
все изменения пользователя)
ошибок
сервера
или
аварийное завершение сеанса пользователя
(отменяются все изменения)

59.

C OM M I T с о хр а н яет ( ф и к с ир ует ) вс е из ме н е н и я , вн е с е н н ы е
с о вр е м е н и вы п олн е н и я п о сл едн е й к о ма н ды c o mmi t ил и
r o l l b a c k , с н и м а ет вс е бл о к и р о в ки
S AV E P O I N T ус тан а вл и вает точ к у с охран ен и я , пос ле ч его
с т а н о в и тс я во з мож н ы м ч а с т и ч н ы й от к ат т р а н за к ц и и
Команды для
работы с
транзакциями
R O L L B AC K TO S AV E P O I NT от ме н яет вс е из м е н е н и я с о
в р е ме н и ус та но в к и по с л едн е й точ к и с о хр а н е н и я и с н им а ет
вс е бл о к и р о в ки , ус т а н о вл е н н ы е в эт о й ч а с т и к од а
R O L L B AC K
от ме н я ет
( от к ат ы ва ет )
вс е
из ме н е н и я,
в н е с е н н ы е с о вр е ме н и в ы полн е н и я п о с ледн е й к о ма н ды
c o m m i t и л и r o l l b a c k , с н и м а ет вс е бл о к и р о в ки
S E T TR AN S AC TI O N поз вол я ет н ач ат ь с е а нс ч те н и я и л и
ч те н и я - з а п ис и, ус та н о в ит ь ур о ве н ь изол я ц ии и л и с вяз ат ь
т е к ущ ую т р а н з а кц и ю с з а д а н н ы м с е г м е н то м от к ата
LO C K TAB L E поз вол я ет з абл о к ир о ват ь вс ю та блиц у в
ук аз а н н о м р еж и м е . ( П о умолч а н ию к табл ице о б ы ч н о
п р и м е н я етс я бл о к и р о в к а н а ур о в н е с т р о к . )

60.

Фиксирует все изменения, внесенные в базу
данных в ходе сеанса тек ущей транзакцией
Команда
COMMIT
Снимает все бл окировки таблиц
После выполнения этой к оманды изменения
становятся видимыми для других сеансов или
пользователей и отк ат невозможен
Синтак сис этой к оманды:
COMMIT [WORK] [COMMENT текст];
[w ork] необязательно, для упрощения чтения
к ода

61.

О пер ато ра ми в тр ан за кци и м ожно та кже уп р а вл ят ь на
б ол е е д ета ль н ом ур о вн е , и с п оль зуя точ к и с ох ранения
Команда
SAVEPOINT
Точ к и сох ранени я п озволяют в ыб орочно отме ня ть
некоторые ча с ти тран закц и и и фикси рова ть все
ос та ль н ы е
Ус тана вли вает в т ран за кц ии и мен о ванн ый мар к ер,
поз воляю щий в с л уч ае необход и м ос ти вып олн ить отк ат
д о от м еч е н ной точ к и с охр а н е ния
Пр и та к о м от к ате от ме ня ются все и з ме нения и
уд а ля ются все бл ок иро вки пос ле этой точки, но
с охр аня ются
и змен ен ия
и
бл ок ировк и,
п р ед ш ест вовавш ие е й
С и н та к си с к о м а н д ы
SAVEPO I N T и мя _ точ к и _ с охранения ;
Пр и уда лен ии или отк ате к точ к е с охр анен ия все точ ки
с охр ане ния,
о предел ённы е
пос ле
этой
точк и
с охр а н ен ия, а вто м ат и ч еск и ун и ч тож аются

62.

Отменяет (полностью или частично) изменения,
внесенные в базу данных в тек ущей транзакции
Существует
Команда
две
основные
без
параметров и с секцией TO, ук азывающей, до
к ак ой точки с охранения следует произвести отк ат
Непосредственно
ROLLBACK
разновидности
перед
выполнением
команд
insert, update, merge или delete – автоматически
устанавливается неявная точк а с охранения, если
к оманда завершается с ошибк ой, выполняется
ок ат до окманды
Синтак сис к оманды
ROLLBACK
[WORK]
имя_точки_сохранения ];
[TO
[SAVEPOINT]

63.

Пример

64.

65.

Begin
-- операторы sql1
Savepoint a1;
-- операторы sql2
Savepoint a2;
Пример
-- операторы sql3
Savepoint a3;
Rollback to {a1|a2|a3};
Commit;
End;

66.

Позволяет начать сеанс чтения или чтениязаписи,
установить
уровень
Команда SET
связать
тек ущую
транзакцию
TRANSACTION
Эта
задает характеристики
текущей транзакции
изоляции
или
с
заданным
должна
быть
первой
транзакции
и
дважды
сегментом отк ата
к оманда
к омандой
использоваться в ходе одной транзакции не
может

67.

READ
Виды команды SET
TRANSACTION
для установки
COMMITTED
оператор
может
видеть
только строки, зафик сированные до его начал,
значение по умолчанию
REPEATABLE READ все операторы тек ущей
транзакции
могут
видеть
тольк о
строки,
уровня изоляции
зафик сированные
транзакции или
транзакции был выполнен первый запрос или
связывания
того,
к ак
в
этой
оператор изменения данных
SERIALIZABLE определяет способ выполнения
транзакции с
транзакции,
заданным сегментом
Все
отката
до
модифицирующей
операторы
видеть
тольк о
тек ущей
строки,
базу
данных.
транзакции
могут
зафиксированные
до
того, к ак в этой транзакции был выполнен
первый
данных
запрос
или
оператор
изменения

68.

Команда блокирует всю таблицу базы данных в
ук азанном режиме
Бл окировк а
запрещает
или
разрешает
модифик ацию данных таблицы со стороны других
транзакций на то время, пок а вы с ней работаете
LOCK TABLE
Синтак сис к оманды
LOCK
список_таблиц
TABLE
IN
режим_блокировки MODE [NOWAIT];
R OW
SH AR E,
ROW
EXC L U SI VE,
SHAR E
U PD ATE,
SH AR E, SH AR E R O W EXC L U SI VE, EXC L U SI VE
При
В
приложениях
следует
использовать только в крайних
случаях
и
с
величайшей
осторожностью
ук азании
NOWAIT
база
данных
не
ждет
снятия блокировки в том случае, если нужная
таблица заблокирована другим пользователем, и
выдает с ообщение об ошибк е

69.

Примеры
транзакций

70.

Пр имер
х ран имо й
функци и,
котор ая
добавляем з аписи в т аблицу tab (id ,
na me ),
п ровер яет
количе ство
обновленных ст р ок, вст авленных ил и
уда ленных
от
предыдущего
запр о с а .
Затем про из водит ся провер ка, е сли вс е
ст рочки
добавлены,
то
т р анз акция
пр именяет ся, е сли нет, то про изводит ся
откат т ранзакци и

71.

Создайте
процедуру,
к оторая
добавляет
данные в таблицу Книги
Задание
Напишите
применением
анонимный
данной
PL/SQL
процедуры
код
с
тольк о
в
случае, если дата сдачи меньше даты выдачи

72.

Использование курсоров и
точек сохранения – внести
изменение в таблицу, в
случае, если количество
книг автора меньше или
равно 2-м

73.

Автономная транзакция — это независимая транзакция,
запускаемая внутри родительской транзакции
В отличие от подтранзакций, которые могут фиксироваться только
вместе с транзакцией, к которой они относятся, автономные
транзакции должны фиксироваться или отменяться до завершения
их родительской транзакции
Автономные
транзакции
Автономная
транзакция
должна
быть завершена либо commit либо
rollback, иначе возникнет ошибк а
и
автономная
транзакция
отк атывается
Процедуру
данных
для
можно
добавления
определить
к ак
автономную транзакцию
программа обновляет таблицу, а затем, не зафиксировав изменения, вызывает автономную
процедуру, которая должна внести изменения в ту же таблицу – возникнет ошибка