лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Синтаксис: DROP SEQUENCE последовательность; Здесь: последовательность имя генератора последовательности. Вопросы: 1. Что такое последовательность? Когда она используется? 2. Какие у последовательности есть параметры? 3. Что такое кэширование последовательности? Зачем оно нужно? 4. Как, не увеличивая текущее значение, просмотреть следующее свободное число последовательности? 5. С какими командами и предложениями используются псевдостолбцы NEXTVAL и CURVAL? С какими предложениями эти псевдостолбцы не используются? Приведите примеры. 6. Как изменить параметры последовательности? 7. Влияет ли откат транзакции на последовательность? Задания: В таблицах, где используется счетчик в качестве первичного ключа, сгенерируйте последовательности с различными начальными значениями и шагом. Попробуйте применить транзакции с откатом. Объясните результат. Просмотрите сведения о последовательности из словаря данных. Не сохраняйте внесенные изменения, а последовательности удалите. 62 Лабораторная работа №9 Триггеры базы данных Триггеры базы данных – процедуры, которые хранятся в базе данных и неявно исполняются («возбуждаются»), когда модифицируется ассоциированная таблица. Создание триггеров Создаются триггеры командой CREATE TRIGGER, синтаксис которой имеет вид: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER} {UPDATE | DELETE | INSERT} ON имя_таблицы [FOR EACH ROW] [DECLARE {секция объявления переменных}] BEGIN {тело триггера} END / Здесь должны быть выполнены следующие условия. 1. Имена триггеров должны быть уникальными среди всех триггеров в той же схеме. Имена триггеров не обязаны быть уникальными по отношению к другим объектам схемы (таким как таблицы, обзоры, процедуры); например, таблица и триггер могут иметь одно и то же имя (хотя, во избежание путаницы, это не рекомендуется). 2. Либо опция BEFORE, либо опция AFTER должна быть указана в предложении CREATE TRIGGER, чтобы точно специфицировать, когда должно исполняться тело триггера по отношению к исполнению предложения триггера. В предложении CREATE TRIGGER опция BEFORE или AFTER задается непосредственно перед ключевым словом, обозначающим предложение триггера. Примечание. Триггеры строк AFTER несколько более эффективны, чем триггеры строк BEFORE. 3. Предложение триггера специфицирует: a. Тип предложения SQL, которое возбуждает тело триггера. Допустимыми возможностями являются DELETE, INSERT и UPDATE. В спецификацию предложения триггера могут быть включены одна, две или все три этих опции. b. Таблицу, ассоциированную с триггером. Заметьте, что в предложении триггера может быть специфицирована ровно одна таблица Примечание. Если предложение триггера специфицирует UPDATE, то в эту спецификацию может быть включен необязательный список столбцов. Если вы включаете список столбцов, то данный триггер возбуждается по предложению UPDATE лишь тогда, когда это предложение обновляет один из перечисленных столбцов. Если вы опускаете список столбцов, то триггер возбуждается при обновлении любого столбца ассоциированной таблицы. Список столбцов не может быть специфицирован для предложений триггера INSERT или DELETE. 63 4. Присутствие или отсутствие опции FOR EACH ROW определяет, является ли этот триггер триггером предложения или триггером строки. Если эта опция включена, она указывает, что тело триггера возбуждается отдельно для каждой строки таблицы, затрагиваемой предложением триггера. Отсутствие опции FOR EACH ROW означает, что данный триггер должен возбуждаться лишь один раз для предложения триггера. 5. Тело триггера – это блок PL/SQL, который может содержать предложения SQL и PL/SQL. Эти предложения исполняются тогда, когда выдано предложение триггера, и ограничение триггера (если оно есть) вычислено как TRUE. Для триггеров строк тело триггера имеет некоторые специальные конструкты, которые могут быть включены в код этого блока PL/SQL: корреляционные имена, опцию REFERENCING, а также условные предикаты INSERTING, DELETING и UPDATING. Доступ к значениям столбцов в триггерах строки Внутри тела триггера строк код PL/SQL и предложения SQL имеют доступ как к старым, так и к новым значениям столбцов текущей строки, затрагиваемой предложением триггера. Для каждого столбца модифицируемой таблицы определены два КОРРЕЛЯЦИОННЫХ ИМЕНИ: одно – для старого (old), другое – для нового значения столбца (new). В зависимости от типа предложения триггера, то или иное корреляционное имя может быть лишено смысла. Триггер, возбужденный предложением INSERT, имеет осмысленный доступ лишь к новым значениям столбцов. Поскольку строка создается предложением INSERT, старые значения столбцов пусты (NULL). Триггер, возбужденный предложением UPDATE, имеет доступ как к старым, так и к новым значениям столбцов для обоих возможных типов триггера (BEFORE или AFTER). Триггер, возбужденный предложением DELETE, имеет осмысленный доступ лишь к старым значениям столбцов. Поскольку строка перестает существовать после ее удаления, новые значения столбцов пусты (NULL). Новые значения столбцов адресуются квалификатором NEW перед именем столбца, старые – квалификатором OLD. Примечание. Старые и новые значения доступны как в триггерах BEFORE, так и в триггерах AFTER. Назначать новое значение столбца можно в триггере строк BEFORE, но не в триггере строк AFTER (потому что предложение триггера уже выполнено, прежде чем триггер AFTER получает управление). Если триггер строк BEFORE изменяет значение NEW для столбца, то триггер AFTER, возбужденный тем же самым предложением, видит значение, которое было назначено триггером BEFORE. 64 Пример Триггер на каскадное обновление таблиц Rent-Realty CREATE OR REPLACE TRIGGER CU_RENT_REALTY AFTER UPDATE ON RENT FOR EACH ROW BEGIN IF (:OLD.Type <> :NEW.Type) THEN UPDATE Realty SET Realty.Type = :NEW.Type WHERE Realty.Type = :OLD.Type; END IF; END / Триггер на каскадное удаление из таблиц Realty-Lease CREATE OR REPLACE TRIGGER CD_REALTY_LEASE BEFORE DELETE ON REALTY FOR EACH ROW BEGIN DELETE Lease WHERE Lease.AdR = :OLD.AdR; END / Триггер на генерацию значения первичного ключа для таблицы Lease CREATE OR REPLACE TRIGGER INS_LEASE BEFORE INSERT ON LEASE FOR EACH ROW BEGIN SELECT lease_sq.NEXTVAL INTO :NEW.NLease FROM DUAL; END / Условные предикаты Если триггер может быть возбужден более чем одним типом предложения DML (например, «INSERT OR DELETE OR UPDATE OF Lease»), то в теле триггера можно использовать условные предикаты INSERTING, DELETING и UPDATING, для того чтобы выполнять различные участки кода в зависимости от типа предложения, возбудившего триггер. Предположим, что предложение триггера определено следующим образом: INSERT OR UPDATE ON Lease В коде внутри тела триггера вы можете использовать следующие условия: 65 IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF; Первое условие будет вычисляться как TRUE лишь в тех случаях, когда триггер был возбужден предложением INSERT; второе условие будет вычисляться как TRUE лишь в тех случаях, когда триггер был возбужден предложением UPDATE. Примечание. Лишь один триггер каждого типа может существовать на таблицу. Это позволяет иметь для таблицы двенадцать возможных триггеров: BEFORE UPDATE строк AFTER UPDATE строк BEFORE DELETE строк AFTER DELETE строк BEFORE INSERT предложения AFTER INSERT предложения BEFORE INSERT строк AFTER INSERT строк BEFORE UPDATE предложения AFTER UPDATE предложения BEFORE DELETE предложения AFTER DELETE предложения Каждая таблица может иметь до четырех триггеров UPDATE (BEFORE/AFTER, предложения/строки), независимо от того, возбуждаются ли эти триггеры при обновлении специфических столбцов. Чтобы создать триггер в своей схеме, вы должны иметь системную привилегию CREATE TRIGGER, а также либо: владеть таблицей, специфицированной в предложении триггера; иметь привилегию ALTER для таблицы, специфицированной в предложении триггера; иметь системную привилегию ALTER ANY TABLE Чтобы создать триггер в схеме другого пользователя, вы должны иметь системную привилегию CREATE ANY TRIGGER. Эта привилегия позволять создать триггер в любой схеме и ассоциировать его с таблицей любого пользователя. Удаление триггеров Для удаления триггера из базы данных используйте команду DROP TRIGGER. Например, чтобы удалить триггер с именем REORDER, введите следующее предложение: DROP TRIGGER reorder / Чтобы удалить триггер, вы должны иметь его в своей схеме либо иметь системную привилегию DROP ANY TRIGGER. 66 Включение и выключение триггеров Триггер может находиться в одном из двух различных режимов: 1. включен – выполняет свое тело, если выдано предложение триггера и если ограничение триггера (если есть) вычисляется как TRUE; 2. выключен – не выполняет свое тело, даже если выдано предложение триггера и ограничение триггера (если есть) вычисляется как TRUE. Вы можете временно выключить триггер, если имеет место одно из следующих условий: объект, к которому обращается триггер, недоступен; вы должны выполнить массовую загрузку данных, и хотите осуществить ее быстро, не возбуждая триггеров; вы загружаете данные в таблицу, к которой применяется триггер. По умолчанию, триггер включается в момент его создания. Чтобы отключить триггер, используйте команду ALTER TRIGGER с опцией DISABLE. Пример Отключение триггера CU_RENT_REALTY: ALTER TRIGGER CU_RENT_REALTY DISABLE / Вы можете одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опциями DISABLE и ALL TRIGGERS. Пример Отключение всех триггеров, определенных для таблицы Realty: ALTER TABLE Realty DISABLE ALL TRIGGERS / По умолчанию триггер автоматически включается в момент его создания, однако позже он может быть выключен. Закончив задачу, для которой потребовалось выключать триггер, вы можете снова включить его. Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE. Пример Включение триггера CU_RENT_REALTY: ALTER TRIGGER CU_RENT_REALTY ENABLE / 67 Вы можете одновременно включить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опциями ENABLE и ALL TRIGGERS. Пример Включение всех триггеров, определенных для таблицы Realty: ALTER TABLE Realty ENABLE ALL TRIGGERS / Для включения и выключения триггеров с помощью команды ALTER TABLE вы должны либо владеть таблицей, либо иметь объектную привилегию ALTER TABLE для таблицы или системную привилегию ALTER ANY TABLE. Для включения или выключения индивидуального триггера с помощью команды ALTER TRIGGER вы должны либо владеть триггером, либо иметь системную привилегию ALTER ANY TRIGGER. Вывод информации о триггерах Следующие обзоры словаря данных раскрывают информацию о триггерах: USER_TRIGGERS; ALL_TRIGGERS; DBA_TRIGGERS. Примеры применения триггеров Вы можете использовать триггеры разнообразными способами, чтобы привязать к своим нуждам управление данными в базе данных Oracle. Например, триггеры обычно используются для: предотвращения незаконных транзакций; обеспечения ссылочной целостности между узлами в распределенной базе данных; реализации сложных организационных правил; ввода в действие комплексных правил защиты; прозрачной регистрации событий; автоматической генерации значений вычисляемых столбцов; поддержания синхронных дублирований таблиц. Вопросы 1. Что такое триггер? Для чего применяются триггеры? 2. Какие ограничения накладываются на пространство имен триггеров? 3. Какие вы знаете опции триггера? За что они отвечают? 4. Сколько и каких триггеров можно создать для одной таблицы? 5. Какими привилегиями необходимо обладать для создания триггера? Использования триггера? Удаления триггера? Изменения триггера? 68 Задания 1. Написать триггеры, обеспечивающее каскадное обновление и каскадное удаление из таблиц. Продемонстрируйте их работу. Используйте хотя бы один триггер с условным предикатом. 2. Написать триггеры, которые при добавлении новой записи автоматически генерируют значение первичного ключа (подсказка: при написании триггера можно воспользоваться последовательностью). Обеспечьте проверку правильности вводимых данных (желательно обработкой исключительной ситуации). Продемонстрируйте их работу. 3. Модифицируйте триггер на вставку новой записи в таблицу Lease так, чтобы он автоматически записывал текущую дату заключения договора в формате 'DD.MM.YYYY HH24:MI'. Продемонстрируйте его работу. 4. Выведите из словаря данных информацию о пользовательских триггерах. Отключите триггер на каскадное обновление для таблицы Realty. Отключите все триггеры для таблицы Owner. Выведите информацию о пользовательских триггерах: имя триггера, имя таблицы из предложения триггера, его статус. Включите все триггеры. Еще раз просмотрите информацию из словаря данных о триггерах. Требования к сдаче лабораторной работы 1. Продемонстрировать наличие и работу триггеров базы данных. 2. Уметь включать/отключать триггеры. 3. Уметь выводить информацию из словаря данных о триггерах. 69 Лабораторная работа №10 Создание индексов. Оптимизация работы запросов при использовании индексов Индекс сервера Oracle – это объект базы данных, с помощью которого можно ускорить поиск строк за счет использования указателя. Индексы создаются вручную или автоматически. Они прозрачны для пользователя. Если вы не создали индекс по столбцу, таблица будет просматриваться полностью. Индекс – это объект базы данных, обеспечивающий прямой и быстрый доступ к строкам в таблице. Индексы создаются для уменьшения потребности в дисковых операциях ввода-вывода за счет использования В-дерева для быстрого поиска данных. Индексы автоматически используются и поддерживаются сервером Oracle. Если индекс создан, от пользователя в дальнейшем никаких прямых действий не требуется. Индексы физически и логически независимы от индексируемой таблицы. Это означает, что индексы могут быть созданы или удалены в любое время, и это не повлияет на базовые таблицы и другие индексы. Имеется 2 типа индексов. Первый – это уникальный индекс. Сервер Oracle создает его автоматически, если для столбца в таблице задано ограничение PRIMARY KEY или UNIQUE. Имя индекса – это имя, присвоенное ограничению. Пользователь может создавать индексы еще одного типа – неуникальные индексы. Например, для увеличения скорости обработки запроса с соединением таблиц можно создать индекс по столбцу с ограничением FOREIGN KEY. Если индекс создан, то сервер обращается к нему всякий раз, когда можно ускорить доступ к данным. Использование индексов происходит автоматически. Использование индексов частично зависит от того, какой оптимизатор Oracle задействован в данный момент. Сервер поддерживает 2 метода оптимизации: оптимизацию на основе правил и оптимизацию по стоимости (более подробно об оптимизаторе и способах оптимизации будет говориться в следующей лабораторной работе). При оптимизации на основе правил сервер решает, пользоваться ли индексом, руководствуясь своими внутренними правилами. При этом сервер определяет индексированные столбцы и типы индексов. Метод оптимизации по стоимости использует для выбора плана выполнения команды SQL статические данные о таблицах и информацию о доступных индексах. Создание индекса Индекс по одному или нескольким столбцам создается с помощью команды CREATE INDEX. Сокращенный синтаксис: CREATE INDEX индекс ON таблица (столбец [, столбец]…); Здесь: индекс имя индекса; таблица имя таблицы; столбец имя столбца в индексируемой таблице. 70 Пример CREATE INDEX lease_ind ON lease (AdR) / Структура индекса и типы индексов Таблица 30. Типы индексов Тип Описание Уникальный Обеспечивает уникальность значений для указанных столбцов Неуникальный Ускоряет запросы Одностолбцовый В индексе использован только один столбец Составной или сложный Индекс может содержать до 16 столбцов для ускорения запросов или проверки уникальности. Столбцы необязательно должны быть смежными Примечание. Типы индексов не исключают друг друга. Например, можно создать уникальный составной индекс. СУБД Oracle предлагает много различных типов индексов. 1. Индексы на основе В*-дерева. Эти индексы называют «обычными». Они, несомненно, чаще всего используются в СУБД Oracle, да и в других СУБД. Аналогичные по конструкции двоичному дереву, они обеспечивают быстрый доступ по ключу к отдельной строке или диапазону строк, требуя обычно очень немного чтений для поиска соответствующей строки. Индекс на основе В*- дерева имеет несколько подтипов. а) Таблицы, организованные по индексу. Это таблицы, хранящиеся в структуре В*-дерева. б) Индексы кластера на основе В*-дерева. Они немного отличаются от обычных, используются для индексации ключей кластера и отдельно рассматриваться не будут. Они используются не для перехода от ключа к строке, а для перехода от ключа кластера к блоку, содержащему строки, связанные с этим ключом. в) Индексы с обращенным ключом. Это индексы на основе В*-дерева, байты ключа в которых инвертированы. Это используется для более равномерного распределения записей по индексу при вводе возрастающих значений ключей. Предположим, при использовании последовательности для генерации первичного ключа генерируются значения 987500, 987501, 987502 и т. д. Поскольку это последовательные значения, они будут попадать в один и тот же блок индекса, конкурируя за него. В индексе с обращенным ключом сервер Oracle будет индексировать значения 205789, 105789, 005789. Эти значения обычно будут далеко отстоять друг от друга в индексе, и вставки в индекс будут распределены по нескольким блокам. г) |