
Полная версия:
Язык PL/SQL
Для первого варианта порядка обработки строк (1,3,2,0,4) SQL-запрос в теле триггера возвращает число 3 для COUNT(*), для второго варианта (1,4,0,2,3) – число 2 (3<>2). То есть один и тот же запрос при одинаковом исходном содержимом таблицы в ходе срабатывания триггера на одной и той же строке может вернуть различные результаты.
Неоднозначности в результатах выполнения предложений SQL быть не должно, поэтому выполнение предложений SQL к мутирующим таблицам в триггерах уровня строки не допускается.
Запрет доступа к мутирующим таблицам относится только к триггерам уровня строки. Триггеры уровня предложения SQL могут и считывать, и записывать данные мутирующей таблицы. Это понятно – перед триггером уровня предложения «лежит» множество всех строк, обрабатываемых предложением SQL. Для AFTER-триггера они все уже обработаны, для BEFORE-триггера они все еще не обработаны. В таких условиях действия с данными в мутирующей таблице в триггере при любом исходном порядке строк в таблице будут завершаться с одинаковыми результатами.
Исключение из запрета доступа к мутирующим таблицам
У запрета доступа к мутирующим таблицам из триггеров уровня строки есть исключение.
Рассуждения строятся следующим образом. Запрет введен для недопущения неоднозначности результатов обращений из триггера к мутирующей таблице из-за отсутствия порядка обработки строк. Понятно, что этой неоднозначности не будет, если предложение SQL обрабатывает ровно одну строку – в этом вырожденном случае обработка строк, очевидно, упорядочена. Таким предложением SQL является предложение INSERT.
Для предложений UPDATE и DELETE понять, сколько строк они обработают, находясь на первой из обработанных им строк, нельзя. Неясно, будет ли после этой строки потом обработана еще вторая, третья и последующие строки. В то же время сам синтаксис предложения INSERT предусматривает, что оно добавляет в таблицу ровно одну строку, поэтому в BEFORE-триггерах уровня строки для таких предложений INSERT можно обращаться к мутирующей таблице:
SQL> CREATE TABLE tab3 (at1 INTEGER);
Table created.
SQL> CREATE OR REPLACE TRIGGER tr$tab3$i
2 BEFORE INSERT ON tab3 FOR EACH ROW
3 DECLARE
4 l_count INTEGER;
5 BEGIN
6 SELECT count(*) INTO l_count FROM tab3;
7 END;
8 /
Trigger created.
SQL> INSERT INTO tab3 VALUES (1);
1 row created.
В то же время, если выполнить специальную форму предложения INSERT – INSERT SELECT, которая потенциально может добавить в таблицу не одну, а несколько строк, произойдет ошибка.
SQL> INSERT INTO tab3 SELECT * FROM tab3;
INSERT INTO tab3 SELECT * FROM tab3
*
ERROR at line 1:
ORA-04091: table U1.TAB3 is mutating, trigger/function may not see it
ORA-06512: at "U1.TR$TAB3$I", line 4
ORA-04088: error during execution of trigger 'U1.TR$TAB3$I'
Для срабатывания триггера на эту форму предложения INSERT запрет обращения к мутирующей таблице распространяется. Таким образом, исключение для однострочных предложений INSERT как нельзя лучше подтверждает общее правило.
Решения проблемы мутирующей таблицы
Для решения проблемы мутирующей таблицы применяются три основных способа:
использование в триггерах автономных транзакций;
использование составных триггеров (compound triggers);
перенос логики триггеров уровня строки в триггеры уровня предложения SQL.
Существует известная техника решения проблемы мутирующей таблицы с условным наименованием «один пакет и три триггера»:
создать BEFORE-триггер уровня предложения, который обнуляет «индекс» таблицы PL/SQL, объявленной как глобальная переменная в спецификации пакета;
создать BEFORE-триггер уровня строки, который для каждой обработанной предложением SQL строки запоминает требуемые значения в записи таблицы PL/SQL;
создать AFTER-триггер уровня предложения, выполняющий требуемые изменения по значениям, запомненным в таблице PL/SQL.
Авторы хотят предостеречь читателя от применения подобных способов, особенно автономных транзакций (autonomous transactions in triggers are pure evil). Они работоспособны только в условиях однопользовательской обработки. Возникновение проблемы мутирующей таблицы, если ее не удалось решить изменением логики в коде самого триггера, следует рассматривать как повод для решения вовсе отказаться от триггера в этом случае и переработать логику обработки данных без него.
Реализация динамических ограничений целостности
Помимо бизнес-логики, триггеры используются для реализации динамических ограничений целостности.
Динамическим ограничение целостности (dynamic integrity constraint) называется динамически проверяемое ограничение, определяющее возможность перехода моделируемой предметной области из одного состояния в другое состояние. Это такие ограничения, которые невозможно реализовать в виде статических ограничений целостности для таблиц (первичных и внешних ключей, ограничений на уникальность и ограничений целостности, задаваемых предикатом CHECK). Динамические ограничения целостности являются более сложными – не декларируемыми, а программируемыми. Рассмотрим пример такого ограничения.
Пусть в базе данных хранятся сведения о договорах клиентов и их лицевых счетах. Отношение между договорами и счетами – «один ко многим», то есть для одного договора есть несколько лицевых счетов.
CREATE TABLE contracts
(id INTEGER PRIMARY KEY,
num VARCHAR2(10),
status VARCHAR2(10));
CREATE TABLE accounts
(id INTEGER,
num VARCHAR2(10),
r$contract$id INTEGER REFERENCES contracts,
status VARCHAR2(10));
INSERT INTO contracts VALUES(12,'562/323-21','operating');
INSERT INTO accounts VALUES(45,'321/21-1',12,'operating');
INSERT INTO accounts VALUES(46,'321/21-2',12,'closed');
Пусть имеется динамическое ограничение целостности – запрет закрытия контракта клиента до тех пор, пока не закрыты все его лицевые счета. Такое ограничение целостности можно реализовать BEFORE-триггером уровня строки.
SQL> CREATE OR REPLACE TRIGGER tr$contracts$u
2 BEFORE UPDATE ON contracts FOR EACH ROW WHEN (NEW.status = 'closed')
3 DECLARE
4 l_account_count INTEGER;
5 BEGIN
6
7 SELECT count(*) INTO l_account_count
8 FROM accounts WHERE accounts.r$contract$id = :NEW.id
9 AND accounts.status <> 'closed';
10
11 IF l_account_count > 0 THEN
12 RAISE_APPLICATION_ERROR(-20001,
13 'У контракта '||:NEW.id||' имеются незакрытые лицевые счета');
14 END IF;
15
16 END;
17 /
Trigger created.
SQL> UPDATE contracts SET status='closed' WHERE contracts.id=12;
UPDATE contracts SET status='closed' WHERE contracts.id=12
*
ERROR at line 1:
ORA-20001: У контракта 12 имеются незакрытые лицевые счета
ORA-06512: at "U1.TR$CONTRACTS$U", line 10
ORA-04088: error during execution of trigger 'U1.TR$CONTRACTS$U'
– закрываем лицевые счет 12-го контракта
SQL> UPDATE accounts SET status='closed' WHERE r$contract$id=12;
2 rows updated.
– теперь закрыть контракт можно
SQL> UPDATE contracts SET status='closed' WHERE contracts.id=12;
1 row updated.
Еще раз отметим, что использование триггеров, в том числе и для реализации динамических ограничений целостности, следует рассматривать только в случае крайней необходимости. Так, если ограничение целостности нельзя реализовать статически для существующей схемы базы данных, но такая возможность появится после внесения в схему изменений, то следует об этом подумать. Особенно на этапе проектирования, пока от схемы базы данных еще не стали зависеть приложения и другие компоненты системы.
Триггеры на создание, изменение и удаление объектов базы данных
Это относительно новый вид триггеров, срабатывающих при выполнении DDL-команд. Ранее рассматривались триггеры на события с данными в таблицах, эти же триггеры запускаются при событиях с самими таблицами, а также представлениями, последовательностями и другими объектами баз данных.
Команда создания триггера на создание, изменение и удаление объектов базы данных имеет следующий синтаксис:
CREATE [OR REPLACE] TRIGGER имя триггера
{BEFORE | AFTER} – тип срабатывания
{событие с объектом базы данных } ON {база данных | схема}
[WHEN (…)] – дополнительное логическое условие
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
Под событиями с объектами базы данных понимается выполнение команд из фиксированного перечня: CREATE, ALTER, DROP, GRANT, REVOKE, TRUNCATE TABLE и некоторые другие.
Для получения в триггерах информации об объектах баз данных и о типах происходящих с ними событий, предназначены атрибутные функции.
В версии Oracle 12с имеется 20 атрибутных функций, приведем описание некоторых из них.
Таблица 7. Атрибутные функции.
Атрибутная функция
Описание функции
ORA_CLIENT_IP_ADDRESS
IP-адрес клиента
ORA_DICT_OBJ_NAME
имя объекта базы данных, связанного с DDL-командой, которая вызвала срабатывание триггера
ORA_DICT_OBJ_OWNER
владелец объекта, связанного с DDL-командой, которая вызвала срабатывание триггера
ORA_DICT_OBJ_TYPE
тип объекта, связанного с DDL-командой, которая вызвала срабатывание триггера
ORA_SYSEVENT
тип события, вызвавшего срабатывание триггера (например, CREATE, DROP или ALTER)
Триггеры рассматриваемого типа позволяют эффективно дополнить программной логикой имеющиеся в Oracle средства управления доступом на основе классических дискреционной, ролевой и мандатной моделей. Для иллюстрации этого с помощью триггеров внесем две настройки:
запретим удаление любых таблиц (триггер tr$drop_table$disable);
разрешим назначение привилегий только при подключениях к серверу баз данных сети с конкретного IP-адреса в локальной сети (триггер tr$check_grantee_ip).
Такого вида триггеры могут создаваться администраторами баз данных (администраторами безопасности) для повышения степени контроля за системой:
«чтобы никто ни одной таблички не смог удалить ни при каких обстоятельствах – только я, причем несколько раз подумав и предварительно отключив триггер»;
«назначать привилегии можно было только с моей рабочей станции».
SQL> CREATE OR REPLACE TRIGGER tr$drop_table$disable
2 BEFORE DROP ON DATABASE
3 BEGIN
4 IF ORA_SYSEVENT = 'DROP'
5 AND ORA_DICT_OBJ_TYPE = 'TABLE' THEN
6 RAISE_APPLICATION_ERROR (
7 -20000,
8 'ERROR : Tables cannot be dropped in my database!');
9 END IF;
10 END;
11 /
Trigger created.
SQL> DROP TABLE tab1;
DROP TABLE tab1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: ERROR : Tables cannot be dropped in my database!
ORA-06512: at line 4
SQL> CREATE OR REPLACE TRIGGER tr$check_grantee_ip
2 BEFORE GRANT ON DATABASE
3 DECLARE
4 c_valid_ip CONSTANT VARCHAR2(20) := '192.168.0.8';
5 l_current_ip VARCHAR2(20);
6 BEGIN
7 l_current_client_ip := sys_context('USERENV','IP_ADDRESS');
8 IF ORA_SYSEVENT = 'GRANT'
9 AND l_current_client_ip <> c_valid_ip THEN
10 RAISE_APPLICATION_ERROR (
11 -20000,
12 'ERROR: Grants from '||l_current_ip||' not allowed');
13 END IF;
14 END;
15 /
Trigger created.
SQL> GRANT SELECT ON tab1 TO u1;
GRANT SELECT ON tab1 TO u1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: ERROR: Grants from 127.0.0.1 not allowed
ORA-06512: at line 8
Триггеры на события базы данных
Триггеры на события базы данных запускаются при возникновении событий уровня базы данных. В Oracle 12c восемь таких типов событий, перечислим некоторые из них:
STARTUP – открытие базы данных;
SHUTDOWN – нормальное закрытие базы данных;
SERVERERROR – возникновение ошибки;
LOGON – создание сеанса;
LOGOFF – нормальное завершение сеанса.
Ясно, что триггеры на эти события в основном используются для решения задач администрирования и обеспечения безопасности. Например, в триггерах на LOGON могут осуществляться дополнительные проверки правомерности создания сеанса (проверяться могут время создания сессии, IP-адрес клиента, название клиентского приложения), или устанавливаться переменные окружения сессии пользователя. В триггере на завершение сессии может собираться статистика о выполненных в ходе этой сессии операциях.
Триггеры на события базы данных обычно создаются самими администраторами баз данных или самыми опытными разработчиками прикладных систем.
Защита исходного кода
При создании хранимых программ PL/SQL в словаре-справочнике базы данных Oracle сохраняются и байт-код программ и их исходный код, который доступен для построчного просмотра в представлениях словаря-справочника данных DBA_SOURCE, ALL_SOURCE и USER_SOURCE.
SQL> CREATE OR REPLACE PROCEDURE test AS
2 i INTEGER := 1;
3 BEGIN
4 SELECT i+1 INTO i FROM dual;
5 END;
6 /
Procedure created.
SQL> SELECT TEXT FROM USER_SOURCE
2 WHERE name='TEST' AND type='PROCEDURE'
3 ORDER BY line
4 /
TEXT
–
PROCEDURE test AS
i INTEGER := 1;
BEGIN
SELECT i+1 INTO i FROM dual;
END;
Именно из этих представлений словаря-справочника данных формируют исходный код хранимых программ GUI-клиенты (Quest SQL Navigator, TOAD, PL/SQL Developer) при открытии программ в Stored Program Editor и выполнении операций Extract DDL. Администратор базы данных в представлении DBA_SOURCE может посмотреть исходный код любой хранимой программы и фактически получается так, что все программное обеспечение, написанное на PL/SQL, является open-source software.
Разобраться в коде PL/SQL довольно легко и у кого-то может возникнуть желание внести изменения в логику чужой программы или в ее настройки. Довольно часто настройки программ PL/SQL «зашивают» в коде как локальные константы тел пакетов, и может возникнуть желание несанкционированно их поменять. Помимо этого на исходный код программ PL/SQL могут иметься права как на интеллектуальную собственность.
По этим причинам возникает необходимость защитить исходный код программ PL/SQL от чтения и изменения. Сделать так, чтобы исходный код не распространялся вместе с программами нельзя, но можно его специальным образом обработать – привести к нечитаемому виду. Мы будем использовать термин «нечитаемый код» («скрытый код»), а процесс приведения кода к нечитаемому виду называть сокрытием кода.
Для приведения кода PL/SQL к нечитаемому виду есть три средства:
утилита wrap;
встроенный пакет DBMS_DDL;
встроенный пакет DBMS_WRAP (с Oracle 10g Release 2).
Общая схема сокрытия исходного кода PL/SQL следующая:
программист разрабатывает программу PL/SQL как обычно;
выгружает исходный код программы в текстовый файл (операция Extract DDL средства разработки на PL/SQL);
обрабатывает исходный код в файле утилитой wrap;
полученный DDL-скрипт с нечитаемым исходным кодом программы PL/SQL доставляется администратору, который «прогоняет» его в базе данных.
Утилита wrap работает из командной строки операционной системы. Для Windows ее исполняемый файл wrap.exe нужно запустить из подкаталога BIN домашнего каталога инсталляции Oracle. Формат вызова утилиты:
wrap iname=исходный_файл [oname=обработанный файл]
Обработаем исходный код нашей процедуры test.
C:\Temp>wrap.exe iname=test.sql oname=test_wrapped.sql
PL/SQL Wrapper: Release 11.2.0.2.0-Production on Wed Jan 13 17:51:44 2015
Copyright (c) 1993, 2009, Oracle. All rights reserved.
Processing test.sql to test_wrapped.sql
Можно сразу посмотреть в текстовом редакторе получившийся файл test_wrapped.sql, но лучше мы сначала пересоздадим нашу процедуру, а потом посмотрим ее код в USER_SOURCES.
C:\Temp\sqlplus.exe u1/u1password @test_wrapped.sql
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 13 17:57:02 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Procedure created.
SQL> SELECT text FROM USER_SOURCE
2 WHERE name='TEST' AND type='PROCEDURE'
3 ORDER BY line
4 /
TEXT
–
PROCEDURE test wrapped
a000000
ab
abcd
…
abcd
7
51 8d
suQy2odKI6E8Sra9zNpbNb8IqrAwg5SXf8upynREacFBBrvc2NNbRvLsynXhgKU2wLXjHtmY
DTDLTIiGQltxbGfKvlKRlWFqVHtX8kGHrS0gyD3ka0e/J74vRQynV1uZUJv7WQW2hw==
Нечитаемый исходный код еще называют «wrap’ленный исходник». Отметим его следующие свойства:
исходный текст процедуры test стал совершенно нечитаемым;
после названия хранимой процедуры test есть слово wrapped, говорящее о проведенном сокрытии кода.
При сокрытии исходного кода PL/SQL следует учитывать следующие обстоятельства:
сокрытие кода никак не влияет на функциональность программы PL/SQL, просто становится нечитаемым ее исходный код;
общепринято оставлять читаемым спецификации пакетов (то есть интерфейсную часть) и «закрывать» их тела (в частности, так сделано практически для всех встроенных пакетов);
нельзя делать нечитаемым исходный код триггеров (для этого следует вынести логику из триггеров в хранимые процедуры и функции с нечитаемым кодом и в триггерах только вызывать их);
программы с нечитаемым кодом не могут быть созданы в базе данных версии ниже той, чем версия утилиты, которой они были обработаны;
на каждую версию wrap рано или поздно найдется unwrap или rewrap.
Известно несколько программ, выполняющих обратное преобразование нечитаемого кода, для которых надо только аккуратно выгрузить нечитаемый код из словаря-справочника данных (обычная операция Extract DDL здесь непригодна). Имеются и публикации на эту тему.
Встроенные пакеты
В базах данных Oracle имеется большое количество встроенных пакетов PL/SQL, предназначенных для облегчения и унификации процесса разработки на PL/SQL. Эти пакеты следует рассматривать как стандартные библиотеки, имеющиеся в других языках программирования, например, в C++ и Python. Встроенные пакеты PL/SQL также иногда называют поставляемыми пакетами (Oracle Supplied PL/SQL Packages).
Встроенные пакеты находятся в схеме пользователя SYS и имеют имена с префиксами DBMS и UTL. Число встроенных пакетов растет с выходом каждой новой версии сервера Oracle, вместе с Oracle 11g поставляется свыше 230 встроенных пакетов.
Некоторые встроенные пакеты, например, DBMS_SQL и DBMS_LOB используются при разработке программ PL/SQL очень часто, другие встроенные пакеты используются относительно редко. В литературе по PL/SQL обычно рассматриваются встроенные пакеты, предназначенные для организации ввода-вывода из программ PL/SQL и некоторые встроенные пакеты, предназначенные для автоматизации администрирования баз данных.
Далее приведены примеры использования следующих четырех встроенных пакетов:
DBMS_JOB (управление заданиями);
UTL_FILE (файловый ввод-вывод);
DBMS_LOB (работа с большими объектами);
DBMS_SQL (динамический SQL).
Управление заданиями
В большинстве вычислительных систем существует механизм, который позволяет автоматически запускать задания. Например, в операционной системе Microsoft Windows есть «Планировщик задач» (Task scheduler), который предоставляет возможность запланировать запуск программ в определенные моменты времени или через заданные временные интервалы. В UNIX-подобных операционных системах (Linux, Oracle Solaris) есть cron – классический демон-планировщик задач, использующийся для запуска заданий в определенное время.
Для сервера Oracle задания запускаются специальными фоновыми процессами (Job queue processes, Jnnn), а для управления заданиями предназначен встроенный пакет DBMS_JOB. Число фоновых процессов Jnnn настраивается параметром экземпляра job_queue_processes. Если задания не запускаются в назначенное им время, то следует проверить значение этого параметра, выставить при необходимости для него ненулевое значение и перезапустить экземпляр Oracle.
Автоматический запуск заданий в Oracle может использоваться для решения задач администрирования баз данных, например, автоматического сбора статистики или анализа журналов аудита. Также в заданные моменты времени могут автоматически запускаться и прикладные задачи, такие, например, как загрузка данных, поступивших из внешних источников, формирование месячных счетов к оплате или расчет клиентской задолженности. Ресурсоемкие операции, такие как подготовка аналитических отчетов, следует запускать в то время, когда серверы баз данных загружены меньше – ночью или в выходные дни. Понятно, что такие задания также должны запускаться автоматически, без участия человека.
При использовании пакета DBMS_JOB можно поставить в очередь заданий новое задание, которое будет автоматически выполнено в указанные моменты времени. Заданием в Oracle является анонимный блок PL/SQL, в котором, как правило, вызываются хранимые программы.
Распространено мнение, что среди всех встроенных пакетов DBMS_JOB – наиболее часто используемый администраторами баз данных Oracle.
Таблица 8. Программы пакета DBMS_JOB.
Программа
Описание программы
SUBMIT (процедура)
отправляет новое задание в очередь заданий
CHANGE (процедура)
изменяет параметры задания
WHAT (процедура)
изменяет описание задания
NEXT_DATE (процедура)
изменяет следующее время выполнения задания
REMOVE (процедура)
удаляет задание из очереди
RUN (процедура)
указывает немедленно выполнить задание
INTERVAL (процедура)
изменяет интервал между запусками задания
Будем рассматривать в качестве задания анонимный блок PL/SQL, запускающий процедуру p1.
CREATE TABLE job_test (insert_date DATE);
CREATE PROCEDURE p1 AS
BEGIN
INSERT INTO job_test VALUES(SYSDATE);
COMMIT;
END;
Отправим задание в очередь со следующими параметрами:
SQL> DECLARE
2 l_job_num INTEGER;
3 BEGIN
4 DBMS_JOB.submit(job =>l_job_num,
5 what=>'BEGIN p1; END;',
6 next_date=>sysdate,
7 interval=>'sysdate+10/24/60/60');
8 COMMIT;
9 DBMS_OUTPUT.PUT_LINE(l_job_num);
10 END;
11 /
23
PL/SQL procedure successfully completed.
В очередь будет помещено новое задание BEGIN p1; END; с немедленным выполнением после помещения в очередь и последующим выполнением каждые десять секунд, что задается выражением sysdate+10/24/60/60. На экран выводится уникальный номер задания, назначенный ему при постановке в очередь (23). Важно отметить, что после вызова процедуры DBMS_JOB.submit необходимо явно зафиксировать транзакцию, в противном случае задание в очередь поставлено не будет.
Посмотрим на результаты работы процедуры p1 и убедимся, что задание запускается каждые десять секунд:
SQL> SELECT TO_CHAR(insert_date,'DD.MM.YYYY HH24:MI:SS') AS insert_date
2 FROM job_test ORDER BY insert_date;
INSERT_DATE
–
25.01.2015 11:26:18
25.01.2015 11:26:28
25.01.2015 11:26:38
Данные о заданиях пользователя, которые в данный момент находятся в очереди, можно просмотреть в представлении словаря-справочника данных USER_JOBS:
SQL> SELECT job,log_user,last_sec,next_sec,broken,interval,what
2 FROM USER_JOBS;
JOB LAST_SEC NEXT_SEC BROKEN INTERVAL WHAT
– – – – – –
23 11:26:51 11:27:01 N sysdate+10/24/60/60 BEGIN p1; END;
В первом столбце отображаются номера заданий, назначаемые им при постановке в очередь. В следующих столбцах отображаются время последнего и следующего выполнения задания, интервал выполнения и анонимный блок PL/SQL для выполнения задания. Меткой BROKEN (заблокировано) помечаются те задания, при шестнадцати попытках выполнения которых произошли ошибки. Заблокированные задания перестают выполняться, поэтому администратору баз данных следует контролировать содержание представления DBA_JOBS, в котором отображаются сведения обо всех заданиях. Если в задании временно отпала необходимость, то его можно пометить как заблокированное специально, используя для этого процедуру DBMS_JOB.broken.