Лабораторная работа №1. Последовательности и триггеры.

Цель работы: научиться создавать и применять последовательности, получить первое представление о подпрограммах, написанных на PL/SQL, на примере триггеров.

Последовательность (SEQUENCE) — это объект БД, который генерирует последовательный ряд уникальных целых чисел. Обычно значения последовательности используются в качестве значений первичных и уникальных ключей таблиц БД. Использование такого генератора значений, как последовательность, для заполнения первичных ключей гарантирует, что таблица всегда будет находиться в целостном состоянии (???). В отличие от других команд SQL и PL/SQL, доступных пользователю БД, работа последовательностями происходит вне транзакций, поэтому сгенерированное последовательностью число не может быть изменено откатом транзакции. Для создания последовательности применяется команда CREATE SEQUENCE:

CREATE SEQUENCE SCHEME.имя_последовательности

INCREMENT BY n

START WITH n

MAXVALUE n | NOMAXVALUE

MINVALUE n | NOMINVALUE

CYCLE | NOCYCLE

CACHE 20 | n | NOCACHE

ORDER | NOORDER.

SCHEME — схема, в которой будет создана последовательность. Если схему опустить, то по умолчанию последовательность будет создана в схеме текущего пользователя (в схеме пользователя, который выполняет команду).

INCREMENT BY — параметр, который задает приращение для генерируемых последовательностью чисел. Число N может иметь либо положительное значение, либо отрицательное. Если число N положительное, то значения, генерируемые последовательностью, будут возрастать; если число N отрицательное — уменьшаться. По умолчанию значение данного параметра равно единице, что соответствует возрастающей последовательности.

START WITH — параметр, который задает начальное значение последовательности. Если данный параметр не указан, то подразумевается, что начальным значением для восходящей последовательности будет MINVALUE, а для нисходящей последовательности — MAXVALUE.

MAXVALUE — параметр, который задает максимальное значение последовательности. Если используется ключевое слово NOMAXVALUE, то для восходящих последовательностей максимальное значение равно 10²⁷, а для нисходящих последовательностей — -1.

MINVALUE — параметр, который задает минимальное значение последовательности. Если используется ключевое слово NOMINVALUE, то для восходящих последовательностей максимальное значение равно 1, а для нисходящих последовательностей — -10²⁶.

CYCLE | NOCYCLE — атрибут последовательности, который может иметь два значения. Если задан параметр CYCLE, то после выдачи последовательностью MAXVALUE-значения восходящая последовательность будет повторно генерировать значения, начиная с MINVALUE-значения, а нисходящая последовательность – начиная с MAXVALUE-значения. Если задан параметр NOCYCLE, что принимается по умолчанию, то генерация значений данной последовательностью прекратится после выдачи максимального или минимального значения в зависимости от типа последовательности.

CACHE — параметр CACHE вызывает опережающее размещение генерируемых значений последовательности в оперативной памяти СУБД, что приводит к некоторому увеличению скорости доступа к последовательности. Значение N должно быть меньше разности MAXVALUE-значения и MINVALUE-значения. По умолчанию параметр CACHE равен двадцати.

ORDER | NOORDER — параметр ORDER гарантирует, что значения будут генерироваться соответственно порядку обращения к последовательности. Параметр ORDER обычно не используется для последовательностей, которые используются для генерации значений первичного ключа.

Выполните самостоятельно в программе следующие запросы:

1. create sequence test_seq increment by 1 start with 0 minvalue 0 maxvalue 5 cycle nocache order;

2. create sequence regseq increment by -1 start with 1000 minvalue 100 maxvalue 1200 nocycle cache 20 NOorder.

Для удаления последовательности используется команда DROP sequence. Выполните самостоятельно в программе следующие запросы:

1. DROP sequence test_seq;

2. DROP sequence regseq.

В языке SQL можно для получения и генерации значений последовательности используются следующие псевдоколонки:

1. CURRVAL - возвращает текущее значение последовательности;

2. NEXTVAL - генерирует и возвращает следующее значение последовательности.

Использовать псевдоколонки необходимо только с именем последовательности, например, test_seq.CURRVAL и regseq.nextval. Чтобы получить текущее значение только что созданной последовательности, ее необходимо инициализировать вызовом NEXTVAL!

Выполните самостоятельно в программе следующие запросы:

1. SELECT test_seq.NEXTVAL FROM DUAL;

2. SELECT test_seq.CURRVAL FROM DUAL;

Таблица DUAL - это небольшая таблица, существующая в БД ORACLE. Таблица DUAL имеет одну колонку с именем DUMMY и одну запись, которая имеет значение X. Пользователи БД могут считывать данные из этой таблицы с гарантированным результатом (одна строка). Обычно эта таблица используется для получения одиночных значений из БД, например, как в примере выше, для генерации и чтения значений последовательностей.

Чтобы использовать последовательность, расположенной в схеме другого пользователя, необходимо иметь на нее право выборки (SELECT). Администратор БД либо владелец последовательности может предоставить такое право (привилегию) другим пользователям. Теме управления привилегиями для доступа к объектам БД посвящена одна из следующих лабораторных работ.

Одним из самых больших преимуществ реализаций современных реляционных СУБД является их способность компилировать и выполнять на стороне сервера внутренние модули (хранимые процедуры, функции, триггеры и т.д.), представленные пользователями БД в виде исходных текстов. СУБД ORACLE имеет в своем составе язык PL/SQL (Procedural Language / Structured Query Language) — простой, но мощный набор расширений языка SQL, который объединяется с обычными операторами языка манипулирования данными (DML) для создания компилированных модулей, хранящихся в БД. В этой работе знакомство с языком PL/SQL начнется с изучения такого объекта БД, как триггер.

Триггер — подпрограмма, связанная с таблицей (представлением), которая автоматически выполняет некоторые действия, когда новая запись добавляется в данную таблицу (представление), либо существующая запись обновляется или удаляется (в зависимости от типа триггера). Триггер является объектом БД. В отличие от хранимых процедур триггер никогда не вызывается явно! Когда пользователь или прикладная программа пытаются выполнить операторы INSERT, UPDATE или DELETE к таблице, то в этом момент автоматически вызываются триггеры, связанные с этой таблицей. Перечислим основные направления применения триггеров:

· для проверки исходных данных;

· для конвертирования входных данных, например, при конвертировании единиц измерения;

· для поддержания целостности данных;

· для создания сложных изменяемых представлений.

Триггеры создаются с помощью команды CREATE TRIGGER:

/* Секция ЗАГОЛОВКА */

CREATE OR REPLACE TRIGGER ИМЯ_ТРИГГЕРА BEFORE (AFTER)

INSERT OR UPDATE OR DELETE ON ТАБЛИЦА FOR EACH ROW

/* Секция объявлений */

begin

/* команды pl/sql —

ВЫПОЛНЯМАЯ СЕКЦИЯ /*

end;

Выше приведена типовая структура любой подпрограммы PL/SQL, которая состоит из четырех секций:

1. заголовочная секция;

2. секция объявлений;

3. выполняемая секция;

4. секция исключений.

Различные типы подпрограмм (триггеры, хранимые процедуры, функции и т.д.) имеют отличные заголовочные секции. Заголовочная секция является обязательной.

Секция объявлений является необязательной. В случае использования она начинается после секции заголовка и оканчивается перед ключевым словом BEGIN. В этой секции содержатся объявления локальных переменных, констант, курсоров, исключений, которые будут использоваться в выполняемой секции и секции исключений.

 

 

Выполняемая секция является обязательной. В выполняемой секции реализуют весь алгоритм, который должен решать задачи данной подпрограммы. Выполняемая секция начинается с ключевого слова BEGIN и заканчивается либо секцией исключений (ключевое слово EXCEPTION), либо ключевым словом END. В выполняемой секции размещаются операторы PL/SQL, которые выполняются последовательно, от BEGIN к END.

Секция исключений является необязательной. Обработке исключений в подпрограммах PL/SQL посвящена одна из следующих работ.

При компиляции триггера в SQL*Plus необходимо после команды CREATE TRIGGER ввести оператор «/». Ключевое слово REPLACE говорит о том, если триггер уже создан на момент вызова оператора, то в этом случае существующий триггер будет заменен на новый. Параметр (BEFORE или AFТER) определяет, когда вызывается триггер: до или после события. Параметры (INSERT, UPDATE, DELETE) определяют типы события, при наступлении которых будет вызываться создаваемый триггер.

В теле триггер можно использовать контекстные переменные NEW и OLD, которые хранят соответственно новые и старые значения записи. Переменная NEW доступна только для триггеров типа INSERT и UPDATE. Логические контекстные переменные INSERТING, UPDATING и DELETING сигнализируют о типе события, вызвавшего триггер.

Рассмотрим несколько примеров:

1. create table cars (pk integer not null primary key, car_name varchar2(80));

2. create sequence cars_seq increment by 1 start with 0 minvalue 0;

3. create or replace trigger cars_bit before insert on cars for each row declare i integer;

begin

select cars_seq.nextval into i from dual;

:new.pk:= i;

end;

4. insert into cars(car_name) values (‘BMW’)

5. create table car_models(fk integer not null, model_name varchar2(30), foreign key(fk) references cars(pk));

6. select car_name, model_name from car_models m left outer join cars c on m.fk = c.pk;

7. create or replace trigger cars_bf before delete on cars for each row begin

delete from car_models

where fk =:old.pk;

end;

8. DELETE FROM CARS.

 

Задания для самостоятельной работы:

1. Создайте таблицу-справочник футбольных команд, в которой предусмотрено три колонки: ID (первичный ключ), INSNAME (название команды), KOL (количество игроков).

2. Создайте возрастающую последовательность. Напиши два запроса, получающие сгенерированное новое значение последовательности и текущее значение последовательности. Выполните запросы по несколько раз и проследите за изменением значений последовательности.

3. Создайте триггер к таблице команд, который присваивает при вставке первичному ключу сгенерированное последовательностью значение.

4. Напиши запрос, добавляющий три записи в таблицу команд без ручного заполнения первичного ключа (ID).

5. Создайте таблицу игроков с полями ID (первичный ключ), INAME (имя игрока), CID (поле-внешний ключ к таблице команд, обязательный для заполнения).

6. Создайте убывающую последовательность. Напиши два запроса, получающие сгенерированное новое значение последовательности и текущее значение последовательности. Выполните запросы по несколько раз и проследите за изменением значений последовательности.

7. Создайте триггер к таблице команд, который присваивает при вставке первичному ключу сгенерированное убывающей последовательностью значение.

8. Добавьте в таблицу игроков пять записей, привязанных к командам.

9. Создайте триггер к таблице команд, который бы удалял связанные записи в таблице игроков при удалении родительской записи (событие before delete и псевдоколонка OLD).

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