Function Based Indexes, by Tomas Kyte

Эта статья впервые была размещена на сайте http://www.oramag.ru/

От редакции Russian Oracle Magazine
 
В этом номере ROIM с любезного разрешения автора начинается публикация серии статей Т.Кайта (Tomas Kyte), известного автора и ведущего колонки "Ask Tom" журнала Oracle Magazine. Не часто можно встретить столь глубокое знание предмета и искреннее желание поделиться с читателями. Мы продолжим перевод и публикацию работ Т.Кайта в следующих номерах журнала. Самые нетерпеливые наши читатели могут ознакомиться с творчеством Т.Кайта на его личном сайте http://govt.oracle.com/~tkyte , могут обратиться к нему с вопросами в Oracle Magazine http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.main. Я хотел бы, чтобы Вы при чтении этих статей испытали такую же радость, что и я при подготовке их к печати. Анатолий Бачин ВведениеАвтономные транзакции доступны в 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       1100SQL> 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       2200SQL> 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       3000SQL> 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       3000SQL> 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-99SQL> 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, она была встроена в ядро в течении длительного времени (а значит хорошо протестирована).

Скриптыhttp://govt.oracle.com/~tkyte/autonomous/demo.tar

По этому адресу вы можете найти демонстрационные скрипты, использованные в этой статье. Они удаляют и создают пользователей, вызывают "demo_autonomous", "demo_ddl", and "demo_hierarchy".