Введение Автономные транзакции доступны в Oracle8i, Oracle8i EE и
Oracle8i Workstation.
Автономные транзакции предоставляют новый метод контролирования транзакций в
хранимых процедурах. Автономные транзакции позволяют создавать новые
подтранзакции (subtransaction), которые могут сохранять или отменять
изменения вне зависимости от родительской транзакции. Мы рассмотрим, разбирая
конкретные примеры:
Небольшой пример и объяснение того, что происходит,
лучше раскрывают эту функциональность:
SQL> create table t ( x int ); Table
created.
SQL> SQL> create or replace procedure insert_into_t
2 as 3 pragma autonomous_transaction; 4 begin 5
insert into t values ( 1 ); 6 commit; 7 end; 8 / Procedure
created.
SQL> select * from t; no rows selected
SQL> begin 2 insert into t values ( -1 ); 3
insert_into_t; 4 rollback; 5 end; 6 / PL/SQL
procedure successfully completed.
SQL> select * from t; X ----------
1 |
В вышеприведенном примере, мы создали процедуру INSERT_INTO_T. В этой
процедуре используется новая прагма AUTONOMOUS_TRANSACTION. Эта директива
сообщает базе данных, что данная процедура будет выполняться как новая
подтранзакция, независимая от родительской транзакции. Эта процедура просто
вставляет запись со значением '1' в таблицу T и сохраняет изменения. Затем мы
создаем анонимный PL/SQL-блок, в котором вставляется значение -1 в таблицу T,
вызывается хранимая процедура INSERT_INTO_T и rollback - откат изменений. До
внедрения автономных транзакций, оператор commit в процедуре INSERT_INTO_T
сохранял бы не только ту работу, которую выполнила процедура (вставка '1'), но и
любую внешнюю работу, выполненную сессией, но еще не сохраненную (вставка '-1' в
анонимном блоке). Оператору Rollback нечего было делать, поскольку оператор
commit в процедуре сохранил обе вставки. Мы же видим, что в случае с автономными
транзакциями это не так. Работа, выполненная в процедуре, помеченной
AUTONOMOUS_TRANSACTION, была сохранена, в то время как работа, выполненная вне
автономной транзакции, была отменена.
Предыдущие версии Oracle поддерживали внутренние автономные транзакции. Они
известны как рекурсивные SQL-операции. Например, при выборе из некэшируемой
последовательности, выполняется рекурсивная транзакция для немедленного
увеличения последовательности. Это обновление последовательности сразу же
сохраняется, и становится видимым для других транзакций, при этом для всей
транзакции сохранение еще не выполнялось. Кроме того, если вы откатите
транзакцию, то увеличение последовательности останется неизменным, оно не будет
откачено вашей транзакцией, поскольку эти изменения уже сохранены. Управление
памятью и другие внутренние операции выполняются аналогичным рекурсивным
способом.
Теперь, когда мы выяснили, что такое автономные транзакции, рассмотрим
причины, по которым их стоит использовать.
Почему имеет смысл использовать эту возможностьИтак, зачем вам может
потребоваться использование автономных транзакций? На то есть много ситуаций: от
реализации аудита, чтобы не потерять аудит-записи (audit trail records) в случае
отката клиентской транзакции, до выполнения DDL в триггерах; от записи статуса
базы данных функцией, вызванной из SQL-предложения, до построения более
модульных приложений. Мы рассмотрим несколько примеров, чтобы понять как это
можно реализовать.
Аудит, который нельзя откатить
Раньше разработчики приложений часто задавали вопрос: 'Как можно надежно
отследить попытку модифицировать информацию?'. Для этого многие пытались (и
безуспешно) использовать триггеры. Триггер должен обнаружить обновление, и если
пользователь изменяет данные, которые он не имет права менять, триггер должен
создавать аудит-запись и прерывать обновление. К сожалению, при откате
обновления, откатывается также и аудит-запись, то есть все или ничего, ошибка
должна реализоваться или о ней не будет никаких сведений. Теперь, с появлением
автономных транзакций, мы сможем надежно зафиксировать в аудит-таблице попытку
выполнить операцию, а также откатить саму операцию. И это даст нам возможность
сказать конечному пользователю, что он не может изменять эти данные и что у
нас есть запись о пытке это сделать.
Вот небольшой пример:
SQL> REM Создадим для своей работы
копию демонстрационной таблицы EMP. SQL> REM Предоставим
всем пользователям право модифицировать эту SQL> REM
таблицу. SQL> create table emp as select * from scott.emp;
Table created.
SQL> grant all on emp to public; Grant succeeded.
SQL> REM Это аудит-таблица. Мы будем фиксировать
SQL> REM имя пользователя, дату предпринятой попытки и
SQL> REM некоторое сообщение, описываещие операцию,
SQL> REM которую пытались выполнить над таблицей emp.
SQL> create table audit_tab (uname varchar2(30), dt date, msg
varchar2(4000)); Table created.
SQL> create or replace trigger emp_trigger 2 before update of
SAL on emp 3 for each row 4 declare 5 -- эта прагма
позволит нашему триггеру сохранить 6 -- запись в
аудит-журнале. 7 -- Мы можем затем прервать выполнение
оператора, 8 -- вызвавшего триггер, не допустив
обновления 9 pragma autonomous_transaction; 10 l_cnt
number; 11 begin 12 -- Этот запрос проверяет, действительно ли
работник, 13 -- данные о котором меняются, подчиняется
сотруднику,
- -- выполняющему обновление.Для построения иерархии
удобно
- -- использовать конструкцию connect by.
15 --
Поскольку предложение where обрабатывается после того, 16
-- как иерархия построена,здесь можно использовать exists 17
select count(*) 18 into l_cnt 19 from dual 20 where exists (
select empno 21 from emp 22 where empno = :new.empno 23 start
with mgr = 24 (select empno from emp where ename=USER) 25 connect
by prior empno = mgr ); 26 27 -- Если exists ничего не
возвращает, значит мы пытаемся 30 -- обновить данные о
работнике, нам не подчиненного. 28 -- Необходимо
зафиксировать попытку и прервать выполнение 29 -- этой
операции. Зарплата сотрудника не будет обновлена, 31 -- а
у нас останется запись об этой попытке изменения. 32 if (
l_cnt = 0 ) 33 then 34 insert into audit_tab values ( user,
sysdate, 35 'Попытка обновления зарплаты ' || 36 :new.ename || '-'
|| :new.empno); 37 commit; 38 39
raise_application_error( -20001,
- 'Вы пытаетесь сделать то, что вы не имеете права
'||
41 ' делать, и мы знаем об
этом'); 42 end if; 43 end; 44 / Trigger
created. |
Итак, вот что мы имеем на данный момент: таблицу EMP, данные которой мы хотим
защитить, таблицу AUDIT_TAB, в которую мы будем записывать неудачные попытки
обновления данных (попытки, которые мы предотвратили), и триггер, который
использует автономную транзакцию для выполнения своей работы. Теперь попробуем
выполнить некоторые DML операции, используя учетные записи различных
пользователей, и посмотрим, что получится:
SQL> show user USER is
"DEMO_AUTONOMOUS" SQL> REM Сначала мы попытаемся обновить
запись, используя SQL> REM учетную запись demo. Это
нам не удастся, как покажет SQL> REM результат выборки
из emp, приведенный ниже, однако, SQL> REM запись о
попытке будет присутствовать в аудит-таблице. SQL> select
empno, ename, mgr, sal 2 from emp where ename = 'ADAMS'; EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100
SQL> update emp set sal = sal*2 where ename = 'ADAMS'; update
emp set sal = sal*2 where ename = 'ADAMS' * ERROR at line 1:
ORA-20001: Вы пытаетесь сделать то, что вы не имеете права
делать, и мы знаем об этом ORA-06512: at
"DEMO_AUTONOMOUS.EMP_TRIGGER", line 36 ORA-04088: error during
execution of trigger 'DEMO_AUTONOMOUS.EMP_TRIGGER'
SQL> select empno, ename, mgr, sal 2 from emp where ename =
'ADAMS'; EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100
SQL> select * from audit_tab;
UNAME DT MSG ------------------------------ ---------
------------------------------ DEMO_AUTONOMOUS 10-JUN-99 Попытка
обновления зарплаты
ADAMS-7876 |
Поскольку пользователь DEMO_AUTONOMOUS не имеет подчиненных сотрудников в
таблице EMP, это обновление завершается неудачно. Выборка (SELECT) из таблицы
EMP демонстрирует, что обновление не было произведено, а выборка из таблицы
AUDIT_TAB показывает, что нам удалось обнаружить и зафиксировать попытку
обновления.
Теперь, рассмотрим пользователя, который может обновлять некоторые данные.
Пользователь SCOTT имеет одного работника (ADAMS), который подчиняется ему.
SQL> show user USER is
"SCOTT"
SQL> REM Теперь, попробуем сделать то же самое,
используя SQL> REM учетную запись пользователя,
который имеет подчиненных SQL> REM ему работников.Эти
действия будут успешными, как SQL> REM показано
ниже. SQL> select empno, ename, mgr, sal 2 from
demo_autonomous.emp where ename = 'ADAMS'; EMPNO ENAME MGR SAL br>
---------- ---------- ---------- ----------
7876 ADAMS 7788 1100 SQL>
update demo_autonomous.emp set sal = sal*2 where ename = 'ADAMS'; 1
row updated.
SQL> select empno, ename, mgr, sal 2 from demo_autonomous.emp
where ename = 'ADAMS'; EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7876 ADAMS 7788 2200 SQL>
REM Попробуем теперь обновить запись, которую мы не имеем
SQL> REM права обновлять (нашу собственную зарплату), и тут
мы SQL> REM будем схвачены. SQL> select
empno, ename, mgr, sal 2 from demo_autonomous.emp where ename =
'SCOTT'; EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7788 SCOTT 7566 3000 SQL>
update demo_autonomous.emp set sal = sal*2 where ename = 'SCOTT';
update demo_autonomous.emp set sal = sal*2 where ename = 'SCOTT' *
ERROR at line 1: ORA-20001: Вы пытаетесь сделать то, что вы
не имеете права делать, и мы знаем об этом ORA-06512: at
"DEMO_AUTONOMOUS.EMP_TRIGGER", line 36 ORA-04088: error during
execution of trigger 'DEMO_AUTONOMOUS.EMP_TRIGGER'
SQL> select empno, ename, mgr, sal 2 from demo_autonomous.emp
where ename = 'SCOTT'; EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7788 SCOTT 7566 3000 SQL>
connect demo_autonomous/demo_autonomous Connected.
SQL> select * from audit_tab;
UNAME DT MSG ------------------------------ ---------
------------------------------ DEMO_AUTONOMOUS 01-JUN-99 Попытка
обновления зарплаты ADAMS-7876 SCOTT 01-JUN-99 Попытка
обновления зарплаты
SCOTT-7788 |
Итак, здесь показано, что SCOTT может обновлять некоторые данные, но вновь,
при попытке обновления данных, которые он не имеет права обновлять, SCOTT был
схвачен.
Выполнение DDL в триггерахЧасто спрашивают: 'Как создать объект базы
данных, когда я вставляю строку в такую-то и такую-то таблицу'. Объект базы
данных изменяется от вопроса к вопросу. Иногда при вставке в некоторую таблицу
некто хочет создать ПОЛЬЗОВАТЕЛЯ (USER) базы данных, иногда - создать таблицу
или последовательность. Что бы там ни было, автономные транзакции позволяют это
делать.
Раньше нужно было использовать пакет DBMS_JOB, чтобы спалировать выполнение
DDL-предложений после фиксации (commit) транзакции. Этот способ доступен
и сейчас, и во многих случаях он является по-прежнему правильным
решением. Привлекательной стороной использования DBMS_JOB для планирования
выполнения DDL-предложений является то, что это позволяет включить
DDL-предложения в транзакцию. Если триггер ставит работу (job) в очередь на
выполнение, а эта работа создает пользователя, то при откате родительской
транзакции, работа, создающая пользователя, будет также отменена. Ни записей в
вашей таблице пользователей, ни пользователя базы данных. Используя же в этом
сценарии автономные транзакции, вы создадите пользователя базы данных, но не
вставите запись в таблицу жителей. [Прим. редактора: то есть в ту
таблицу, вставка в которую была причиной возбуждения автономной транзакции.
Автор здесь предупреждает о возможном нарушении целостности данных.]
Принимать решение о том, какой именно метод использовать, нужно в зависимости от
требований к системе.
Вот небольшой пример, который демонстрирует создание учетной записи
пользователя базы данных при вставке пользовательской записи в таблицу
"APPLICATION_USERS". Обратите внимание, что создатель этого триггера должен
иметь привилегию "CREATE USER", выданную напрямую, а не через роль.
SQL> create user demo_ddl identified by
demo_ddl; User created.
SQL> REM В триггере приведенном ниже, мы хотим
предоставить SQL> REM привилегии CONNECT и RESOURCE
другим пользователям. SQL> REM Поэтому, у нашего
пользователя должны права на connect SQL> REM и
resource с параметром WITH ADMIN OPTION, так чтобы он
SQL> REM мог передавать эти привилегии другим
пользователям.
SQL> grant connect, resource to demo_ddl with admin
option; Grant succeeded.
SQL> REM Кроме того, поскольку мы хотим создавать и удалять
SQL> REM пользователей в триггере, мы должны иметь
привилегии SQL> REM CREATE и DROP USER, выданные
напрямую. Во время SQL> REM выполнения триггера роли
не доступны. SQL> REM Роли могут быть доступны во время
выполнения процедуры SQL> REM или функции, но не
триггера.
SQL> grant create user to demo_ddl; Grant
succeeded.
SQL> grant drop user to demo_ddl; Grant
succeeded.
SQL> connect demo_ddl/demo_ddl Connected.
SQL> REM Создание таблицы для хранения наших пользователей.
Мы SQL> REM создадим триггер на эту таблицу,
срабатывающий после SQL> REM вставки для каждой строки
(after insert for each row), SQL> REM для создания
учетных записей. Мы в дальнейшем можем (но SQL> REM не
будем этого делать) расширить пример и сделать SQL> REM
триггер, срабатывающий после обновления для каждой
SQL> REM строки (after update for each row), чтобы
разрешить SQL> REM изменение паролей и ролей. Мы также
создадим триггер SQL> REM (delete for each row),
срабатывающий после удаления SQL> REM любой строки
этой таблицы.
SQL> create table application_users ( uname varchar2(30), pw
varchar2(30), 2 role_to_grant varchar2(4000) ); Table
created.
SQL> create or replace trigger application_users_aifer 2
after insert on application_users 3 for each row
4 declare 5 -- эта прагма позволит нашем триггеру выполнять
DDL 6 pragma autonomous_transaction; 7 begin
- -- Динамический sql будет рассмотрен в другом разделе
9 10 execute immediate 11 'grant ' ||
:new.role_to_grant || 12 ' to ' || :new.uname || 13 ' identified
by ' || :new.pw; 14 end; 15 / Trigger
created. |
Этот триггер строчного уровня объявлен как автономная транзакция. Это
позволяет данному триггеру выполнять DDL-предложения. Мы также используем в этом
примере новую возможность задействования динамического sql, появившуюся в
PL/SQL, которую мы рассмотрим подробнее в другом разделе. Когда этот триггер
сработает, он будет выполнять оператор следующего вида "grant connect, resource
to some_username identified by some_password". Этот оператор выполняет команды
CREATE USER и GRANT за один проход. Преимуществом этого является то, что если
одно из вышеуказанных простых предложений прервется, мы прервем также и
родительскую вставку, строка не будет вставлена в таблицу APPLICATION_USERS, и
мы сохраним условие целостности. С другой стороны, если бы мы использовали два
предложения для создания и предоставления привилегий пользователю, то оператор
CREATE USER мог завершиться успешно, а оператор GRANT мог завершиться неудачно.
Неудачное завершение оператора GRANT должно вызвать откат вставки, оставляя нас
в состоянии, когда учетная запись пользователя создана, привилегии же не
предоставлены, и записи в таблице APPLICATION_USERS не существует. Имейте в
виду, что многострочная вставка в таблицу APPLICATION_USERS может поставить нас
в такое же затруднительное положение, и в этом заключается одна из проблем,
связанных с автономными транзакциями. Это похоже на проблему с
последовательностями: откат не отменяет увеличение значения в
последовательности. Это делает последовательности чрезвычайно удобными для
параллельного выполнения (многие пользователи могут одновременно выбирать из них
значения), но делает их непригодными для генерации непрерывных
последовательностей чисел (откат транзакции после выборки NEXTVAL из
последовательности всегда будет оставлять дырку). Вы, как разработчик, должны
осознавать это и разрабатывать свои приложения, принимая это во внимание.
Теперь давайте закончим наше приложение:
SQL> create or replace trigger
application_users_adfer 2 after delete on
application_users 3 for each row 4 declare
5 -- эта прагма позволит нашем триггеру выполнять DDL 6
pragma autonomous_transaction; 7 begin 8 execute immediate 'drop
user ' || :old.uname; 9 end; 10 / Trigger created.
SQL> REM Проверим, вставив пользователя, которого хотим
создать SQL> insert into application_users values 2 (
'NewUser', 'NewPW', 'connect, resource' ); 1 row created.
SQL> REM Для проверки сделанного посмотрим, существует ли
новая SQL> REM учетная запись и затем присоединимся,
как новый SQL> REM пользователь
SQL> select * from all_users where username =
'NEWUSER';<> USERNAME USER_ID CREATED
------------------------------ ---------- ---------
NEWUSER 414 03-JUN-99 SQL>
connect newuser/newpw Connected.
SQL> select * from session_roles;
ROLE ------------------------------ CONNECT
RESOURCE
SQL> REM Выше показано, что пользователь с указанным
паролем SQL> REM создан и соответствующие роли ему
предоставлены. SQL> REM Теперь, попробуем проверить
'удаление' пользователя
SQL> connect demo_ddl/demo_ddl Connected.
SQL> delete from application_users; 1 row deleted.
SQL> commit; Commit complete.
SQL> select * from all_users where username = 'NEWUSER'; no rows
selected |
Итак, вот он - триггер, способный создавать и удалять пользователей при
вставке и удалении из таблицы базы данных.
Запись в структуру базы данных в функциях, вызываемых из SQLВремя от
времени возникает необходимость выполнять DML-операции из среды, в которой могут
выполняться только SQL-операции select. Это часто случается при работе с
инструментами для написания отчетов. Например, в группу internet-новостей
comp.databases.oracle.misc пришло письмо следующего содержания:
>Привет, > >Вот непростой вопрос: > >- Я
использую приложение, которое может выполнять только >- SQL предложения.
>- SQL предложения могут вызывать функции, определенные >-
пользователем. >- Эти функции, в свою очередь, могут вызывать процедуры.
> > Однако, я обнаружил, что процедура, которая вызывается, не
может > выполнять обновления, вставку и удаления. Как можно обойти это?
> >Например: >select myfunc(parent) from dual; >
>Function myfunc >..... >bom_exploder(parent) <---- эта
процедура выполняет деталировку >ведомости материалов (BOM explosion),
которая вставляет записи в >таблицу temp. >Здесь вопрошающий
хочет перенести выжимку из ведомости материалов (BOM - Bill Of Materials) в
другую таблицу. Затем данные BOM-выжимки могут быть выбраны и отображены
инструментарием построения отчетов. Создание такой выжимки является процедурной
операцией, она не может быть эффективно выполнена с помощью представления или
одиночного запроса. Инструментарий для построения отчетов может выполнять только
предложения SELECT. Сегодня эта проблема, используя автономные транзакции,
решается легко. Ниже приводится пример этого. Вместо выжимки из ведомости
материалов мы создадим некую иерархию, основанную на таблице EMP. Мы напишем
функцию, которая будет брать номер отдела, строить иерархию сотрудников, которые
управляют людьми, работающими в этом отделе, и записывать эту иерархию во
временную таблицу. Кроме того, появится возможность сортировать на каждом из
уровней иерархии по любому столбцу, как вы пожелаете (этого нельзя сделать в
запросе, использующем connect by). Эта функция будет возвращать сообщение об
успешном или неудачном завершении. В случае успешного завершения, может быть
выдан другой select для выбора результирующего множества. В итоге, используя
только предложение SELECT, мы сможем вставить данные:
SQL> REM создание таблицы
демонстрационных (demo) данных SQL> create table emp as select
* from scott.emp; Table created.
SQL> REM Мы будем использовать временную таблицу для
хранения SQL> REM наших данных. Поскольку мы
используем АВТОНОМНЫЕ SQL> REM транзакции для
заполнения этой таблицы, мы *должны* SQL> REM
использовать временную таблицу уровня сессии, SQL> REM
а не временную таблицу уровня транзакции, так как наша
SQL> REM автономная транзакция должна сохранить
изменения SQL> REM (commit). SQL>
REM Таблица уровня транзакции будет всегда выглядеть пустой
SQL> REM для родительской транзакции
SQL> create global temporary table hierarchy 2 on commit
preserve rows 3 as select 0 seq, 0 lev, emp.* from emp where
1=0; Table created. |
Таблица EMP, созданная выше, содержит данные нашего приложения. Мы напишем к
ней запросы, чтобы получить иерархию сотрудников. Временная таблица HIERARCHY
действительно является временной таблицей. Способ, которым мы ее определили -
"on commit preserve rows", позволяет нашей сессии (и всем транзакциям в этой
сессии) увидеть данные сессии, записанные в эту таблицу. Эта таблица будет
выглядеть пустой для всех других сессий, до тех пор, пока они не запишут в нее
свои данные. При построении иерархии таблицы EMP, с помощью процедуры, описанной
ниже, мы запишем построенную иерархию во временную таблицу.
Итак, перейдем к коду, который будет строить иерархию для заданного отдела.
Мы должны иметь возможность вызывать эту функцию из SQL, а функция будет
вставлять данные во временную таблицу. Раньше это невозможно было
сделать. Если процедура изменяла состояние базы данных (выполняла
вставку, обновление, удаление), она не могла быть вызвана из SQL. Прагма
autonomous_transaction позволяет преодолеть это.
SQL> REM Наша функция заполняет
временную таблицу. SQL> REM Эта функция принимает на входе
номер отдела. Мы начнем с SQL> REM менеджеров данного
отдела (то есть сотрудников, управляющих SQL> REM
кем-либо в этого отделе). Мы сможем также поддерживать
SQL> REM возможность сортировки. SQL> REM
Результат этой процедуры похож на запрос с connect by, но он
SQL> REM позволяет упорядочивать данные на любом уровне и
подуровне, SQL> REM что невозможно сделать, используя
connect by. SQL> REM Эта процедура *похожа* на запрос:
SQL> REM select * from emp SQL> REM start with
empno = :x SQL> REM connect by prior mgr = empno
SQL> REM (order by something) SQL> REM Это
отличается от случая, когда order by используется для
SQL> REM каждого поддерева иерархии, а НЕ для всей
иерархии!!!
SQL> create or replace 2 function create_hierarchy( p_deptno in
number, 3 p_order_by in varchar2 default NULL ) 4 return varchar2
5 as 6 pragma autonomous_transaction; 7 8 -- Нам
придется динамически открывать наши курсоры, так как 9 --
во время компиляции мы не знаем предложения "order by".
10 -- Следовательно, необходимо использовать ref cursor.
11 type refCur is ref cursor; 12
- -- l_seq используется для сохранения порядка строк в
таблице
14 -- temp при их выборке. 15 l_seq
number default 0; 16 l_cur refCur; 17 18 19
-- Эта процедура внутри функции выполняет всю работу. 20 -- Это
рекурсивная процедура. Она берет открытый курсор и 21 --
для каждой строки из этого курсора добавляет ее в 22 --
результирующий набор, а затем рекурсивно обрабатывает 23
-- людей, которые работают под руководством этого сотрудника. 24
procedure explode( p_cur in out refcur, 25 p_level in number ) 26
is 27 l_rec emp%rowtype; 28 l_cur refCur; 29 begin 30 loop
31 fetch p_cur into l_rec; 32 exit when p_cur%notfound;
33 34 l_seq := l_seq+1; 35 insert into hierarchy 36
values ( l_seq, p_level, 37 l_rec.empno, l_rec.ename, l_rec.job,
l_rec.mgr, 38 l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno );
39 40 open l_cur for 'select * 41 from emp 42 where
mgr = :x ' || 43 p_order_by 44 USING l_rec.empno; 45
46 explode( l_cur, p_level+1 ); 47 end loop; 48 close p_cur;
49 end; 50 51 begin 52 -- Начнем с очистки нашей
временной таблицы. На всякий 53 -- случай, вдруг мы уже
запускали такой запрос в этой сессии. 54 55 delete
from hierarchy; 56 57 -- Первоначальным набором людей
будут те, кто управляет кем- 58 -- либо в интересующем
нас отделе 59 60 open l_cur for 'select * 61
from emp 62 where empno in ( select mgr 63 from emp 64 where
deptno = :x ) ' || 65 p_order_by 66 USING p_deptno; 67
68 -- Детализировать этот набор (и каждый поднабор) 69
explode( l_cur, 1 ); 70
- -- то будет выдано сообщение
- -- ORA-06519: active autonomous transaction detected and
rolled
- -- back
- -- (обнаружена активная автономная транзакция и выполнен
откат),
- -- которое удалит все наши строки!
76 commit; 77 78 -- В случае успешного
завершения, возвращаем соответствующее
- -- сообщение...
- -- Обработчик исключений, представленный ниже,
возвращает
- -- сообщение об ошибке, в случае неудачного завершения.
- return 'Ok, результирующий набор создан';
82 exception
83 when others then 84 rollback; 85 return sqlerrm; 86
end; 87 / Function created. |
Итак, вот наша процедура. Она начинает работу с запроса "select * from emp
where empno in ( select ALL mgr сотрудников в отделе X ) order by
<что-нибудь>". Этот запрос открывается в главном блоке и передается в
процедуру 'explode'. Процедура принимает этот запрос и выбирает из него данные.
Для каждой строки из этого результирующего набора, процедура строит другой
запрос: на этот раз набор всех работников, менеджером которых является текущий
сотрудник. Этот запрос передается процедуре, которая делает тоже самое снова (и
снова) до тех пор, пока не будет достигнут конец дерева. Рекурсия
'разворачивается', и функция завершается. В иерархической таблице создан
результирующий набор. Выбрать его теперь легко. Вот несколько примеров,
использующих эту технику:
SQL> REM Теперь проверим это. Начнем
с отдела номер 20. Будем SQL> REM упорядочивать по
имени работника (ename) на каждом SQL> REM уровне
иерархии SQL> select create_hierarchy( 20, 'order by
ename' ) msg from dual; MSG ------------------------------
Ok, результирующий набор создан
SQL> REM Выведем теперь результат на экран. Должны быть
выведены SQL> REM все руководители отдела 20,
упорядоченные по имени - SQL> REM под каждым из них мы
увидим их подчиненных SQL> REM (упорядоченных по
имени) и так далее и так далее...
SQL> select lpad(' ',lev*2,' ')|| ename ename, hiredate, job, deptno
2 from hierarchy 3 order by seq; ENAME HIREDATE JOB DEPTNO
-------------------- --------- --------- ----------
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
KING 17-NOV-81 PRESIDENT 10
BLAKE 01-MAY-81 MANAGER 30
ALLEN 20-FEB-81 SALESMAN 30
JAMES 03-DEC-81 CLERK 30
MARTIN 28-SEP-81 SALESMAN 30
TURNER 08-SEP-81 SALESMAN 30
WARD 22-FEB-81 SALESMAN 30
CLARK 09-JUN-81 MANAGER 10
MILLER 23-JAN-82 CLERK 10
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
23 rows selected. |
SQL> REM Выполним то же самое,
упорядочив на этот раз по дате SQL> REM приема на
работу.
SQL> select create_hierarchy( 20, 'order by hiredate' ) msg from
dual; MSG ------------------------------
Ok, результирующий набор создан SQL> select lpad(' ',lev*2,'
')|| ename ename, 2 lpad(' ',lev*2,' ')|| hiredate hiredate_str, job,
deptno 3 from hierarchy 4 order by seq; ENAME HIREDATE_STR JOB DEPTNO
---------------- -------------------- --------- ------
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
KING 17-NOV-81 PRESIDENT 10
JONES 02-APR-81 MANAGER 20
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
BLAKE 01-MAY-81 MANAGER 30
ALLEN 20-FEB-81 SALESMAN 30
WARD 22-FEB-81 SALESMAN 30
TURNER 08-SEP-81 SALESMAN 30
MARTIN 28-SEP-81 SALESMAN 30
JAMES 03-DEC-81 CLERK 30
CLARK 09-JUN-81 MANAGER 10
MILLER 23-JAN-82 CLERK 10
FORD 03-DEC-81 ANALYST 20
SMITH 17-DEC-80 CLERK 20
SCOTT 09-DEC-82 ANALYST 20
ADAMS 12-JAN-83 CLERK 20
23 rows selected. SQL> REM Проверим, что получится, если
задать неверный параметр: SQL> select create_hierarchy(
20, 'order by bogus' ) msg from dual; MSG
------------------------------
ORA-00904: invalid column name |
Как использовать автономные транзакцииИспользовать автономные
транзакции очень просто, никаких специальных параметров init.ora, ни событий
сессии - просто прагма autonomous_transaction в PL/SQL-блоке. Программист должен
заботиться о выполнении сохранения изменений или их откате в автономной
транзакции. Если он не делает этого, то возникает ошибка :
SQL> declare 2 pragma
autonomous_transaction; 3 begin 4 insert into t values ( 1 );
5 end; 6 / declare * ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled
back ORA-06512: at line 4 |
Кроме того, программист должен осознавать, что при использовании автономных
транзакций, транзакции могут заблокировать друг друга. Так как автономные
транзакции запускаются в отдельном контексте транзакций, они не могут
заблокировать никакую строку из тех, что заблокированы родительской
транзакцией. Например:
SQL> REM Создадим демонстрационную
таблицу с первичным ключом SQL> create table t ( x int
primary key ); Table created.
SQL> REM Вставим туда некоторые данные: SQL> insert
into t values ( 1 ); 1 row created.
SQL> REM Теперь, в автономной транзакции попытаемся
вставить SQL> REM такую же запись. Поскольку
автономная транзакция не SQL> REM может 'видеть'
несохраненные данные своей родительской SQL> REM
транзакции, мы заблокируем сами себя.
SQL> declare 2 pragma autonomous_transaction; 3 begin 4
insert into t values ( 1 ); 5 commit; 6 end; 7 /
declare * ERROR at line 1: ORA-00060: deadlock detected
while waiting for resource (обнаружена блокировка при ожидании
ресурса) ORA-06512: at line 4 |
За и противНе так уж много отрицательных сторон использования
автономных транзакций приходит в голову. Стоит лишь однажды разобраться, как они
работают и для чего могут быть полезны, и их можно использовать без всяких
проблем. Автономные транзакции имеют очень мало ограничений и, поскольку они
давно реализованы в Oracle как рекурсивный SQL, они очень хорошо протестированы.
За |
Против |
Разрешает выполнять commit в триггере |
Параллельные запросы не могут выполняться в
автономных транзакциях. Эти запросы будут выполняться
последовательно. |
Позволяет выполнять DML из SELECT |
Блокировки могут возникать чаще, так как теперь
отдельный пользователь может блокировать сам себя. |
Предоставляет возможность создавать более
модульные программы с меньшим побочным эффектом (позволяет избегать
ситуаций типа "Эй - вы откатили мою работу!") |
Должны быть использованы на верхнем уровне
анонимного блока, процедуры или функции. Не могут включаться во вложенные
PL/SQL-блоки. |
Позволяет реализовывать аудит, который не может
быть отменен |
|
Поскольку эта возможность является расширением
рекурсивного SQL, она была встроена в ядро в течении длительного времени
(а значит хорошо протестирована). |
|
Литература по Oracle
|