Главная | Обратная связь | Поможем написать вашу работу!

Работа с последовательностями в оракл

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ДОНСКОЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

Кафедра «Программное обеспечение вычислительной техники

и автоматизированных систем»

Знакомство с языком PL/SQL и его управляющими конструкциями

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ № 3, 4

по дисциплине “Oracle”

Ростов-на-Дону 2010
Составитель ст. преподаватель Новиков. С.П.

Знакомство с языком PL/SQL и его управляющими конструкциями: Методические указания – Ростов н/Д: Издательский центр ДГТУ, 2010.10 с..

В методической разработке рассматриваются управляющие конструкции, приемы работы с базами данных, поддержка автоматизации расчетов и проверка целостности данных. Поясняется работа с триггерами и представлениями. Даны задания к лабораторным работам, помогающие закрепить на практике полученные знания. Методические указания предназначены для студентов специальностей: 230105 «Программное обеспечение вычислительной техники и автоматизированных систем», 090102 «Компьютерная безопасность», 010503 «Математическое обеспечение и администрирование информационных систем».

Печатается по решению методической комиссии факультета «Информатика и вычислительная техника»

Рецензент к.т.н., проф. Гранков М.В.

Научный редактор д.т.н. проф. Нейдорф Р.А.

© С.П. Новиков, 2010

© Издательский центр ДГТУ, 2010

Триггеры в Оракл

Триггер (TRIGGER) — это хранимая процедура, которая запускается (автоматически выполняется) тогда, когда происходит связанное с триггером событие. Обычно события связаны с выполнением операторов INSERT, UPDATE или DELETE в некоторой таблице. Наличие триггеров открывает практически неограниченные возможности по формированию проверок полномочий и допустимости действий, связанных с модификацией данных.

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

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

Триггер запускается при выполнении одной из трех операций изменения содержимого таблицы: INSERT, DELETE или UPDATE. Триггер может запускаться и несколькими операторами, но хотя бы один оператор из тройки должен быть обязательно указан в условии запуска триггера. Если перечень операторов, запускающих триггер, включает оператор UPDATE, то для условий срабатывания могут быть указаны конкретные изменяемые столбцы.

Код триггера может выполняться либо до, либо после тех операторов, которые инициировали запуск триггера. Например, если триггер запускается перед выполнением операции модификации данных для проверки полномочий пользователя на право выполнения операции, то, конечно, нужно использовать триггер с запуском до выполнения операции (с ключевым словом BEFORE). Если триггер применяется для формирования данных для аудиторской записи, то разумно использовать триггер с запуском после выполнения операции (с ключевым словом AFTER).

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

Чтобы создать триггер, необходимо иметь системные привилегии CREATE TRIGGER. Для создания триггера в схеме, отличной от текущей схемы пользователя, требуются системные привилегии CREATE ANY TRIGGER.

Оператор определения триггера Oracle использует следующий синтаксис:

CREATE [OR REPLACE] TRIGGER [имя_схемы.] имя_триггера {BEFORE | AFTER}

{INSERT | DELETE | UPDATE [OF имя_столбца [, имя_столбца…]] }

[OR {INSERT | DELETE | UPDATE [OF имя_столбца [, имя_столбца…]] }…] ON [имя_схемы.]{имя_таблицы | имя_представления}

[FOR EACH ROW] [WHEN условие]

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста триггера. Если ключевое слово OR REPLACE не указано и триггер определен в системе, то замещения старого значения триггера не происходит, и возвращается сообщение об ошибке.

Ключевые слова BEFORE или AFTER указывают на выполнение кода триггера либо до, либо, соответственно, после операторов манипулирования данными, инициировавших запуск триггера.

Ключевые слова INSERT, DELETE или UPDATE определяют конкретный оператор, запускающий триггер. Для оператора UPDATE могут быть указаны столбцы, изменение которых запускает триггер. Если конкретные столбцы не указаны, то триггер запускает изменение любого столбца. Необязательное ключевое слово OR присоединяет дополнительный оператор, запускающий триггер.

Ключевое слово ON задает имя таблицы или представления, ассоциированного с триггером. Необязательное ключевое слово ON EACH ROW определяет триггер как строчный. Необязательное ключевое слово WHEN задает дополнительное логическое условие, сужающее область действия триггера.

Прежде чем перейти к примеру построения триггера, приведем некоторые дополнительные сведения об обработке исключительных ситуаций в Oracle. Для подключения к механизму обработки ошибок пользовательских точек входа применяется процедура RAISE_APPLICATION_ERROR. С ее помощью можно обработать до 1000 определяемых пользователем ошибок с номерами в диапазоне от -20000 до -20999.

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

В отличие от процедур, функций и пакетов сервер Oracle не хранит код триггера в виде скомпилированного блока PL/SQL. При первом запуске триггера его код считывается из словаря данных, компилируется, и скомпилированная версия сохраняется в области SGA. Поэтому для часто используемых триггеров целесообразно код, отвечающий за процедурную часть триггера, включать в хранимую процедуру, а в теле триггера оставлять только запись условий запуска и вызовы соответствующих процедур и функций.

Пример к л.р. № 3:

Создадим триггер работающий при изменении количества товара в таблице Товар. При этом в таблице Склад меняется общее количество товара.

create or replace trigger trigger1 after update of Kol on Tovar for each row

begin

update Sklad set Kol=kol-:old.kol+:new.kol; --вычисляемые поля

where Kod_Group=:new.Kod_Group;

end trigger1;

На предложения языка SQL, включенные в код триггера Oracle, наложены следующие ограничения. Тело триггера не может включать в себя явное использование управляющих операторов COMMIT, ROLLBACK и SAVEPOINT, операторов языка определения данных CREATE, ALTER и DROP, операторов, управляющих разграничением доступа GRANT и REVOKE, а также неявное выполнение перечисленных операторов через вызовы процедур и функций.

Системные привилегии, определяющие права по работе с триггерами и процедурами

Для работы с процедурами в Oracle предусмотрены системные привилегии, приведенные в таблице.

Системная привилегия	Разрешаемые системной привилегией действия
CREATE ANY PROCEDURE	Разрешает пользователю создавать хранимую процедуру, функцию или пакет в любой схеме базы данных
DROP ANY PROCEDURE	Разрешает пользователю уничтожать хранимую процедуру, функцию или пакет в любой схеме базы данных.
EXECUTE ANY PROCEDURE	Разрешает пользователю выполнять любую хранимую процедуру или функцию как входящую в состав пакета, так и одиночную, и ссылаться на любую глобальную переменную пакета из любой схемы базы данных.
ALTER ANY PROCEDURE	Разрешает пользователю изменять любую хранимую процедуру, функцию или пакет из любой схемы базы данных.

Симметричные системные привилегии предусмотрены для работы с триггерами базы данных.

Системная привилегия	Разрешаемые системной привилегией действия
CREATE ANY TRIGGER	Разрешает пользователю создавать триггер БД, ассоцииро-ванный с таблицей любой схемы, в любой схеме БД.
CREATE TRIGGER	Разрешает пользователю создавать триггер базы данных в собственной схеме базы данных.
DROP ANY TRIGGER	Разрешает пользователю уничтожать произвольный триггер в любой схеме базы данных.
ALTER ANY TRIGGER	Разрешает пользователю включать, отключать или изменять любой триггер из любой схемы базы данных.

Работа с последовательностями в оракл

Числовые последовательности в Oracle - это ряд чисел, в котором каждое последующие число больше предыдущего. Из этого следует, что каждое число в последовательности встречается всего один раз. Следовательно, последовательности очень хорошо подходят для генерации первичных ключей для идентификации объектов внутри Базы данных.

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

1) Начальное значение последовательности.

2) Шаг последовательности.

3) Минимальное значение последовательности.

Также для последовательности можно задать, циклична она или нет. Если последовательность циклична, то достигнув максимального значения последовательности, она перейдет к начальному значению последовательности. В случае, если последовательность нециклична и достигла своего конца, то будет сгенерирована ошибка (ORA-08004).

create sequence <имя>; -- создание последовательности

drop sequence <имя>; -- удаление последовательности

-- Получение следующего значения последовательности:

select <имя>.nextval from dual;

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

select <имя>.nextval into <переменная> from dual;