Цель работы - научиться создавать последовательности (Sequence), триггеры (Trigger) и заполнять таблицы данными, пользуясь оператором Insert и созданной системой триггеров.
2.1 КРАТКАЯ ТЕОРИЯ
Последовательность – объект базы данных, посредством которого пользователи могут генерировать уникальные целые значения. Как правило, последовательности используют для автоматической генерации уникальных ключей.
Значения для каждой последовательности автоматически генерируются специальными подпрограммами ORACLE и, следовательно, позволяют избежать неизбежных потерь в производительности в результате реализации последовательностей на уровне приложений. Например, обычным способом такой реализации является блокирование таблицы, хранящей номер, в каждой транзакции в момент приращения последовательности. В результате только один номер последовательности может быть сгенерирован в каждый момент времени. Последовательности же ORACLE позволяют одновременное генерирование нескольких номеров последовательности, гарантируя при этом, что каждый номер последовательности будет уникальным.
Когда генерируются номера последовательности, осуществляется приращение этой последовательности независимо от того, завершается эта транзакция или отменяется. Если два пользователя параллельно осуществляют приращение одной и той же последовательности, номера последовательности, видимые каждым из этих пользователей, могут иметь пропуски значений, поскольку эти значения были сгенерированы другим пользователем. Одному пользователю никогда не может встретиться номер последовательности, сгенерированный другим пользователем. Если значение последовательности сгенерировано каким-либо пользователем, этот пользователь может использовать это значение независимо от того, осуществляется ли приращение последовательности другими пользователями.
Так как номера последовательности генерируются независимо от таблиц, одна и та же последовательность может использоваться для одной или более таблиц. Возможно, что отдельные номера последовательности будут вообще пропущены из-за того, что они могли быть сгенерированы и использованы в транзакции, которая, в конечном счете, была отменена.
Если в операторе CREATE SEQUENCE кроме имени не указать ни одного предложения, то по умолчанию создается возрастающая последовательность, которая начинается с 1, имеет шаг 1 и не имеет верхнего предела. Если указать только предложение INCREMENT BY -1, то создается убывающая последовательность, которая начинается с -1 и не имеет нижнего предела.
Можно создать последовательность так, чтобы приращение ее значений происходило одним из следующих способов:
приращение значений последовательности продолжается неограниченно;
приращение значений последовательности происходит до
определенного предела и затем прекращается;
приращение значений последовательности происходит до
определенного предела и затем начинается заново.
Чтобы создать последовательность, приращение значений которой продолжается неограниченно, необходимо опустить параметр MAXVALUE или указать параметр NOMAXVALUE для возрастающей последовательности. Для убывающей последовательности опустить параметр MINVALUE или указать параметр NOMINVALUE.
Чтобы создать последовательность, приращение значений которой прекращается на определенном значении, необходимо указать значение для параметра MAXVALUE (если последовательность возрастающая) или значение для параметра MINVALUE (если последовательность убывающая). При этом необходимо указать также параметр NOCYCLE. Любая попытка сгенерировать номер последовательности, если последовательность уже достигла своего предела, приведет к ошибке.
Чтобы создать последовательность, приращение значений которой начинается заново после достижения определенного значения, необходимо указать значения для обоих параметров, MAXVALUE и MINVALUE, а также параметр CYCLE.
Значение параметра START WITH устанавливает начальное значение, генерируемое последовательностью, после того как ее создали. Следует обратить внимание, что это не обязательно именно то значение, с которого циклическая последовательность начинается заново после достижения своего максимального или минимального значения.
Количество кэшируемых значений для последовательности определяется значением параметра CACHE. Кэшированные последовательности обеспечивают быструю генерацию номеров последовательности. Кеш для данной последовательности заполняется при первом запросе номера этой последовательности. Кеш регенерируется после количества запросов, указанного в параметре CACHE. В случае сбоя системы все копированные номера последовательности, которые не были использованы в завершенных операторах языка манипулирования данными, теряются. Возможное количество потерянных значений равно величине параметра CACHE. По умолчанию ORACLE кэширует 20 номеров последовательности.
Триггер базы данных (database trigger) – это хранимый блок PL/SQL, который ассоциирован с таблицей. В триггере посредством PL/SQL описаны действия, которые необходимо выполнить при обработке операторов SQL (таких как Insert, Update, Delete и т.д.) для данной таблицы. Oracle автоматически выполняет триггер, во время обработки оператора SQL.
2.2 ЗАДАНИЯ К РАБОТЕ И ПОРЯДОК ВЫПОЛНЕНИЯ
Задание 1. Создать последовательности для автоматического заполнения ключевых полей при добавлении новых записей.
Порядок выполнения задания 1.
1. Для создания сиквенса необходимо в навигаторе найти пункт Sequences и правой клавишей мыши вызвать для него контекстное меню. В меню выбрать пункт New Sequence…. В появившемся диалоговом окне (рис. 2.1) указать имя сиквенса в следующем формате: S_ИМЯ, где ИМЯ – это название таблицы, для которой создается сиквенс.
2. Далее необходимо заполнить остальные поля в окне (рис. 2.1).
Рис. 2.1- Создание последовательности
Min value (MINVALUE) – указывает минимальное значение последовательности (как правило, = 1).
Max value (MAXVALUE) – указывает максимальное значение последовательности, которое она может сгенерировать.
Start with (START WITH) – указывает значение первого генерируемого номера последовательности. Для возрастающей последовательности значением по умолчанию для этого параметра является ее минимальное значение, для убывающей последовательности – ее максимальное значение.
Increment (INCREMENT BY) – указывает интервал между числами последовательности. Это значение может быть любым положительным или отрицательным целым числом в стандарте Oracle, но не может быть нулем. Если значение отрицательное, это – убывающая последовательность, если положительное – возрастающая последовательность. Если в поле ничего не указывать, значение будет принято равным 1.
Cache size (CACHE) – указывает, как много значений последовательности Oracle генерирует заранее и держит в памяти для быстрого доступа. Минимальное значение для этого параметра – 2. Для циклических последовательностей это значение должно быть меньше, чем количество значений в цикле. Пример записи в скрипте: CACHE 10. Если в данном поле указать 0, то это будет означать отсутствие кэширования значений последовательности (пример записи в скрипте: NOCACHE). Можно также выбрать NOCACHE в поле Cache.
Cycle (CYCLE, NOCYCLE) – указывает, что последовательность продолжает циклически генерировать значения после достижения своего минимального или максимального значения. После того как возрастающая последовательность достигла своего максимального значения, она генерирует свое минимальное значение. Для убывающей последовательности – наоборот.
Order (ORDER, NOORDER) – гарантирует, что номера последовательности генерируются в порядке поступления запросов. Этот параметр может быть полезным при использовании номеров последовательности в качестве временных отметок. Гарантирование порядка, как правило, не является важным условием для последовательностей, используемых для генерирования первичных ключей (по умолчанию – NOORDER).
3. После заполнения соответствующих полей рекомендуется просмотреть автоматически созданный скрипт посредством перехода на закладку DDL. Дале необходимо нажать на кнопку OK, и автоматически сгенерированный скрипт по созданию последовательности выполнится. Задание необходимо повторить для всех таблиц, где есть первичные ключи!
Задание 2. Создать систему триггеров для таблиц базы данных.
Порядок выполнения задания 2.
1. Для создания триггера необходимо в навигаторе найти пункт Triggers и правой клавишей мыши вызвать для него контекстное меню. В меню выбрать пункт New Trigger…. В появившемся диалоговом окне заполнить поля (рис. 2.2). Имя триггера должно быть в следующем формате: T_Имя таблицы.
Trigger type – вид триггера. Триггер может быть на таблицу, обзор, схему или базу данный. В нашем случае необходимо указать TABLE, то есть табличный триггер.
Table Name – имя таблицы, с которой будет ассоциироваться триггер, т.е. таблица, для которой мы собственно и создаем триггер.
Before – указывает, что триггер срабатывает до выполнения оператора, вызывающего этот триггер.
After – указывает, что триггер срабатывает после выполнения оператора, вызывающего этот триггер.
Delete – указывает, что триггер срабатывает всегда, когда какой-либо оператор DELETE удаляет строки из ассоциированной с триггером таблицы.
Insert – указывает, что триггер срабатывает всегда, когда какой-либо оператор INSERT добавляет строки к ассоциированной с триггером таблице.
Рис. 2.2 – Создание триггера
Update – указывает, что триггер срабатывает всегда, когда какой-либо оператор UPDATE изменяет значение какого-либо столбца ассоциированной с триггером таблицы.
В данном примере рассматривается следующая комбинация: Before Insert, т.е. мы будем обрабатывать событие перед вставкой новой записи.
Заполнив указанные поля, нажать клавишу OK.
Рис. 2.3 – Пример шаблона тела триггера
2. После этого откроется окно редактирования с загруженным в него шаблоном будущего триггера (рис. 2.3). Этот шаблон необходимо отредактировать, добавив туда текст PL/SQL.
Задание 3. Создать систему хранения данных аудита.
Порядок выполнения задания 3.
1. В каждой таблице необходимо создать два поля: ID_CRE (тип varchar2) и DAT_CRE (тип date). В эти поля при вставке каждой записи автоматически должны прописываться имя текущего пользователя и текущая дата. Также необходимо предусмотреть вставку значения первичного ключа для таблиц, где он присутствует. Вернемся теперь к редактору: ключевое слово FOR EACH ROW указывает, что данный триггер – строковый. Триггер срабатывает один раз для каждой строки (записи), на которую действует оператор, запускающий триггер, и которая удовлетворяет условиям ограничений, определенных для этого триггера в предложении WHEN. В нашем случае это предложение опущено, значит, триггер действует для каждой строки (записи).
При необходимости можно перед ключевым словом BEGIN вставить ключевое слово DECLARE, после которого можно декларировать локальные переменные (с типами данных Oracle). В данном случае нам это не потребуется.
2. Теперь в разделе BEGIN – END вместо оператора NULL; необходимо написать скрипт на PL/SQL, выполняющий нужные нам действия по вставке значений данных в поля. Рассмотрим, как это сделать на примере таблицы STUDENT. Предположим, для таблицы уже создан сиквенс с названием S_STUDENT. Скрипт PL/SQL, описывающий нашу задачу, будет выглядеть следующим образом:
select S_STUDENT.nextval into :new.STUDENT_ID from dual;
:new.ID_CRE:=user;
:new.DAT_CRE:=sysdate;
Первая строка заполняет значение ключевого поля, значение извлекается из последовательности S_STUDENT. NEXTVAL возвращает очередное значение последовательности а dual – это зарезервированное слово, означающее псевдотаблицу и необходимое для завершенности синтаксиса оператора Select.
NEW – это параметр (зарезервированное ключевое слово), означающий новую строку, поэтому обращение к нему идет через двоеточие, далее через точку идет обращение к нужным нам полям этой строки.
USER – системная переменная, возвращающая имя текущего пользователя в строковом формате.
SYSDATE – системная переменная, возвращающая текущую дату.
Таким образом, соответствующие поля в новой (вставляемой) строке будет заполнены, и после окончания работы триггера в таблицу добавится полностью сформированная и заполненная строка (оставшиеся поля должны будут заполняться в операторе Insert)!
3. После окончания редактирования скрипта триггера его необходимо скомпилировать: (Ctrl+Shift+F9) или кнопка  на панели инструментов. При успешной компиляции триггер добавится к ассоциированной с ним таблице. Его можно будет просмотреть в редакторе таблицы на закладке Triggers или в одноименном пункте навигатора.
4. Задание следует выполнить для каждой таблицы. Для таблиц, где есть первичный ключ, в триггер нужно добавлять строчку для заполнения ключевого поля. Необходимо создать триггеры двух типов, реагирующих на вставку записи (Insert) и на ее изменение (Update). При изменении записи в триггере должны заполняться только поля ID_CRE и DAT_CRE.
Задание 4. Организовать заполнение таблиц данными.
Порядок выполнения задания 4.
Создать несколько скриптов с операторами Insert. При написании оператора Insert поля, которые являются первичными ключами, и поля ID_CRE и DAT_CRE заполнять не следует – они будут заполняться автоматически триггерами. Создать не более 5-ти операторов для добавления записей по каждой таблице. Просмотреть, как работает система триггеров и как формируются значения первичных ключей.
2.3 СОДЕРЖАНИЕ ОТЧЁТА
Готовится один отчёт на бригаду в печатном виде. Он должен содержать следующие разделы:
- задание;
- описание процесса создания последовательностей (приводится скрипт DDL опреации);
- описание триггеров базы данных (приводится скрипт DDL операции).
Приём отчёта осуществляется в индивидуальном порядке. Ответы на контрольные вопросы даются устно.
2.4 КОНТРОЛЬНЫЕ ВОПРОСЫ
Для чего нужны последовательности?
Объяснить назначение параметров при создании сиквенса. Каково назначение параметра Cache?
Для чего предназначены триггеры?
Объяснить смысл скрипта PL/SQL в теле любого из созданных триггеров.
|
Скачать 1.79 Mb.