АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2002 ГОД

Любителям ORACLE на заметку

• Все журналы \ Номер 17/188 `2002 от 22.04.2002 \ Любителям ORACLE на заметку

Олег Гопанюк gopaniouk@soft-review.kiev.ua

Данная статья предназначена для разработчиков, которые используют СУБД Oracle. В последнее время она приобретает все большую популярность, несмотря на конкуренцию со стороны Microsoft SQL Server. Иногда разработчики сталкиваются с проблемой выбора СУБД — возможно, данная статья поможет с ней справиться. В статье описаны некоторые полезные возможности СУБД Oracle. Предполагается, что читатель имеет опыт работы с базами данных и владеет основами SQL. При этом автор искренне надеется, что статья будет полезна как для опытных разработчиков приложений, так и для новичков.

Использование триггеров

В любой современной промышленной СУБД есть такое понятие как триггер. Триггер — это обработчик определенного события, возникающего на уровне таблиц, схемы или базы данных. Чаще всего используются триггера на уровне таблиц, для отслеживания событий изменения данных. В Oracle триггерный механизм очень основательно проработан. Об этом и пойдет речь в данном разделе статьи.

Триггер может использоваться для ограничения DLM операторов, реализации детального аудита, накопления статистики доступа к таблицам, автоматического присваивания значений столбцам таблицы, а также для обработки системных событий сервера. Хотя триггера являются удобным средством, используйте их лишь при необходимости. Слишком большое количество триггеров может привести к усложнению приложения, а наличие в триггерах длительных операций — существенно снизить производительность. Помните, что триггер вызывается всякий раз, когда происходит событие. Рассмотрим теперь несколько простых примеров. Допустим, вы хотите отслеживать все операции по изменению данных в таблице с регистрацией времени и пользователя. Для начала нужно создать таблицу, в которую триггер будет вставлять сообщения об изменениях данных. В листинге 1 приведен исходный код для создания такой таблицы.

Листинг 1. Создание таблицы аудита.

Поле ID является уникальным идентификатором записи аудита. Для генерации значений подобных полей обычно поступают следующим образом: создают объект последовательность (sequence) с именем <имя таблицы>_SEQ. Эти объекты базы данных Oracle используются для генерации уникальных значений. Для получения следующего значения из последовательности ACTION_AUDIT_LOG_SEQ можно использовать следующий SQL-запрос:

Для создания последовательности используют команду create sequence <имя_последовательности>. При создании последовательности можно задать дополнительные параметры, о которых можно прочитать в документации. После создания последовательности можно приступать к созданию триггера. На листинге 2 приведен исходный код триггера.

Листинг 2. Исходный код триггера для формирования уникального идентификатора записи.

Форма записи create or replace trigger позволяет перезаписать триггер без предварительного удаления. Если вы опустите фразу or replace,то в случае наличия триггера с таким именем будет выдано сообщение об ошибке. Как правило, разработчики всегда используют эту фразу. Обратите внимание на псевдозапись :new. Она используется для доступа к значениям полей таблицы на момент срабатывания триггера. Аналогично ей существует псевдозапись :old. Как видно, для доступа к новому значению поля ID используется фраза :new.id. Теперь после каждой вставки значение поля ID будет выбираться из последовательности ACTION_AUDIT_LOG_SEQ. Обратите внимание, что остальные поля, кроме поля Action, имеют значения по умолчанию. При определении таблицы ACTION_AUDIT_LOG было задано, что значение по умолчанию поля OWNER будет равно значению функции USER, которая возвращает имя пользователя (см. листинг 1). Аналогично, функция SYSDATE используется для задания даты и времени создания записи.

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

Листинг 3. Триггер аудита таблицы счетов.

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

Теперь, когда на простых примерах стала понятна идея триггеров и некоторые способы их использования, рассмотрим этот механизм более детально.

Триггер имеет три элемента: имя, тип, условие и тело. Пространство имен триггеров отличается от пространства имен других подпрограмм и таблиц. Это означает, что триггер может иметь имя, которое полностью совпадает с именем какой-либо таблицы или процедуры. Однако давать одинаковые названия триггерам и таблицам считается моветоном. Лучше применять какой-либо шаблон для имен триггеров, например такой: T_<имя таблицы>_B|A_U|I|D. Например, для создания триггера, который активизируется после вставки записи в таблицу ACCOUNT, можно выбрать имя T_ACCOUNT_AI. Однако помните, что длина имени триггера не должна превышать 30 символов.

Тип триггера определяется тем, какое событие его активизирует: INSERT (вставка), UPDATE (изменение) или DELETE (удаление). Триггеры могут активизироваться до (BEFORE) или после (AFTER) наступления события, а также для строки таблицы или оператора, вызвавшего события в целом. Если триггер является строковым (имеется в виду строка таблицы), то он активизируется один раз для каждой из строк, на которые воздействует оператор, который вызвал срабатывание триггера. Если триггер является операторным, то он активизируется один раз до или после выполнения оператора. Строковые триггеры содержат фразу FOR EACH ROW в описании триггера. В версии Oracle 8.0 и выше появился еще один вид триггеров —INSTEAD OF, которые можно создавать только для представлений (объектных или реляционных). В документации сказано, что они поддерживаются лишь в версии Enterprise. Эти триггера дают возможность модифицировать представления, которые не могут быть модифицированы с помощью SQL’ных DML-выражений, и называются так потому, что активизируются вместо операторов DML, которые вызвали их срабатывание. Триггеры INSTEAD OF должны быть строковыми.

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

Очень полезно наличие триггерных предикатов INSERTING, UPDATING и DELETING. Эти предикаты возвращают true, если активизирующий оператор —INSERT, UPDATE и DELETE соответственно. В противном случае предикат возвращает false.

Кроме указанных выше видов триггеров в версии Oracle 8i имеются еще триггеры системных и пользовательских событий. Существуют следующие системные события: старт, останов базы данных и ошибка, сообщение о которой вернул сервер Oracle. Допустим, имеется таблица для хранения всех ошибок, произошедших на сервере. На листинге 4 приведен исходный код для создания такой таблицы, а также пример триггера, который активизируется после ошибки на сервере. Таким образом, этот триггер позволяет осуществлять аудит всех ошибок сервера.

Листинг 4. Исходный код таблицы и триггера для аудита ошибок сервера.

Обратите внимание, что в данном листинге используется схема SYS. Подобный пример может быть с успехом применен во время внедрения крупных приложений для отслеживания ошибок. Как видите, функции USER и SYSDATE можно с успехом применять и в теле триггера. Функция sys.server_error позволяет вернуть код ошибки — в качестве параметра она принимает ее номер. Дело в том, что ошибки хранятся в стеке, и для получения кода последней ошибки нужно передать единицу в качестве параметра. В данном примере кроме уже знакомых функций используется функция USERENV для получения имени терминала, с которого произошло соединение пользователя с базой данных. Данная функция принимает несколько значений параметра, которые подробно описаны в документации. Обратите внимание, что триггер на листинге 4 реагирует на ошибки на уровне всей базы данных, это видно из фразы ON DATABASE объявления триггера. Вместо этой фразы можно использовать ON SCHEMA для отслеживания событий лишь на уровне схемы. В таблице 1 приведены пользовательские и системные события.

Клиентские события связаны с соединением/отсоединением пользователя, а также с DDL- и DML-операциями. Например, на листинге 5 приведен пример простого триггера, который активизируется после регистрации пользователя. Этот триггер предотвращает регистрацию пользователей SYS и SYSTEM с компьютера, имя которого отличается от ‘SR_CARD’.

Листинг 5. Триггер, который не разрешает регистрироваться пользователям sys и system удаленно.

При попытке зарегистрироваться пользователю SYS с компьютера, имя которого отличается от ‘SR_CARD’, будет выдано сообщение, показанное на рисунке 2.

Триггеры, как процедуры или функции, можно удалять. Синтаксис команды удаления триггера таков: DROP TRIGGER имя_триггера. В отличие от процедур или функций, триггер можно запретить, не удаляя из словаря данных. Для запрещения или разрешения триггера используйте следующую команду: ALTER TRIGGER имя_триггера {DISABLE|ENABLE}.

В заключении раздела о триггерах рассмотрим порядок активизации триггера. Алгоритм выполнения оператора DML таков:

1. Выполняется операторный триггер BEFORE, если он существует.

2. Для каждой строки, на которую воздействует оператор:

a) выполняется строковый триггер BEFORE, если он существует;

b) выполняется оператор;

c) выполняется строковый триггер AFTER, если он существует;

3. Выполняется операторный триггер AFTER, если он существует.

Иерархические запросы

При разработке приложений довольно часто приходится иметь дело с иерархическим представлением информации. Допустим, вы решаете задачу, в которой требуется учитывать отношения подчиненности между сотрудниками фирмы или подразделениями. В расширении языка SQL фирмы Oracle существует специальная фраза для работы с иерархическими запросами. Приятен тот факт, что возможность работать с иерархическими данными существует на уровне SQL.

Если таблица содержит иерархические данные, вы можете получить их в соответствующем виде, используя оператор Select, как показано на рисунке 3. Здесь START WITH определяет корневую запись (записи) иерархии, CONNECT BY определяет отношение между подчиненными и родительскими записями в иерархии, а WHERE накладывает дополнительные ограничения на результирующий набор строк.

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

Рассмотрим пример с использованием иерархических запросов. Приведенный на листинге 6 запрос возвращает имена всех служащих согласно служебной иерархии. Корневая запись содержит имя директора организации. В поле MNG содержится значение поля EMPNO — скажем, начальника какого-нибудь отдела.

Листинг 6. SQL запрос для получения списка служащих в иерархическом виде.

Результат приведен ниже в табл. 3.

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

Листинг 7 SQL запрос для получения списка служащих, кроме аналитиков, в иерархическом виде.

Как видно, в условиях вы можете использовать псевдостолбец LEVEL.

Использование предикатов в таблицах

В поставке Oracle Server Enterprise Edition существует пакет DBMS_RLS, который предоставляет возможность управлять доступом к таблице на уровне строк. Допустим, вы разрабатываете приложение, в котором пользователи создают документы. Но требуется, чтобы пользователь имел доступ только к своим документам или документам своих подчиненных. Обычно для реализации такой схемы доступа создают дополнительные представления, куда встраивают предикаты, отфильтровывающие данные. Иногда это перегружает базу данных дополнительными объектами словаря, зачастую усложняя разработку приложения и его сопровождение. В данном случае пакет DBMS_RLS помогает разработчику избавиться от дополнительных расходов на реализацию доступа к таблице на уровне записи. Рассмотрим пакет DBMS_RLS подробнее.

Для полноты картины на листинге 8 приведен код этого пакета.

Листинг 8 Пакет DBMS_RLS.

Первый параметр процедуры ADD_POLICY (object_schema)задает схему таблицы или представления, для которой будет создаваться политика доступа. Если этот параметр равен NULL, то будет использоваться схема пользователя, который вызывает процедуру. Второй параметр (object_name) задает имя таблицы или представления, для которой будет формироваться политика доступа. Третий параметр (policy_name) задает имя политики. Это имя должно быть уникально в пределах одной таблицы или представления. Четвертый параметр (function_schema) задает имя схемы, содержащей функцию, которая будет возвращать предикат. Если этот параметр равен NULL, то будет использоваться схема пользователя, который вызывает процедуру. Пятый параметр (policy_function) задает имя функции, которая будет возвращать предикат. Если функция, возвращающая предикат, находится в пакете, то имя пакета должно присутствовать. Шестой параметр (statement_types) может быть комбинацией SELECT, INSERT, UPDATE или DELETE. Все описанные выше параметры имеют тип VARCHAR2, как можно увидеть из описания пакета. Седьмой параметр (update_check) является параметром логического типа boolean. По умолчанию его значение false. Если значение этого параметра равно true, сервер будет проверять политику после вставки или изменения записи. Последний, восьмой параметр (enable), как и предыдущий, является параметром логического типа и указывает, разрешена ли создаваемая политика. Значение по умолчанию —true. Обратите внимание, что для пользователя SYS любые ограничения, накладываемые с помощью пакета DBMS_RLS, не имеют силы. Функция, которая генерирует предикат для таблицы или представления, вызывается сервером. Интерфейс функции, возвращающей предикат, таков:

Параметр object_schema содержит имя схемы, в которой находится таблица или представление, а параметр object_name содержит имя таблицы или представления. Максимальная длина предиката, возвращаемого функцией, равна 2000 байт. Функция, генерирующая предикат, не должна изменять данные в базе. Если таблица имеет несколько политик, и в результате генерируется несколько предикатов, то результирующий предикат будет равен конъюнкции (логическое «И») всех предикатов. Самый простой способ использования описанного выше метода для ограничения записей таблицы заключается в следующем: в ограничиваемой таблице должен быть столбец, который содержит имя пользователя, функция же возвращает предикат типа “user=<имя_столбца_имя_пользователя>”. Естественно, можно строить довольно сложные предикаты, зависящие от многих параметров.

Заключение

Автор надеется, что материал статьи поможет в разработке приложений с использованием Oracle. Возможно, данная статья поможет определиться разработчикам или их заказчикам, которые выбирают СУБД для своих задач.

Идёт загрузка...

Данную страницу никто не комментировал. Вы можете стать первым.

Ваше имя:
Ваша почта:
Комментарий:
Введите символы или вычислите пример: * Обновить