Автономные транзакции 1179

нять только операторы SELECT или даже простые операторы типа INSERT, но на самом деле необходимо вызывать хранимые процедуры, а этого подобные средства не позволяют. Автономные же транзакции позволяют вызывать любую хранимую процедуру или функцию с помощью SQL-оператора SELECT.

Предположим, создана хранимая процедура, вставляющая ряд значений в таблицу для того, чтобы на их основе ограничить набор строк, выдаваемых последующим запросом в том же сеансе. Если значений в этой таблице нет, отчет создать нельзя. Работать приходится в среде, не позволяющей выполнять хранимые процедуры, а только обтчные SQL-операторы. Как же поступить, если выполнить эту процедуру необходимо. Следующий пример демонстрирует возможное решение:

tk5te@TKYTE816> create table report parm table

2 (sessionid number,

3 arg1 number,

4 arg2 date

Table created.

tkyte@TKYTE816> create or replace

2 procedure set upreport(parg1 in number, parg2 in date)

3 as

4 begin

5 delete from report parm table

6 where session id = sys context(userenv,sessionid);

8 insert into report parm table

9 (session id, arg1, arg2)

10 values

11 (sys context(userenv,sessionid), p arg1, p arg2);

12 end;

13 / Procedure created.

Итак, имеется хранимая процедура, изменяющая состояние базы данных; она входит в уже существующую систему. Мы хотим вызывать ее из SQL-оператора SELECT, поскольку это единственно доступный способ. Процедуру SET UP REPORT необходимо обернуть в небольшую PL/SQL-функцию, поскольку в SQL-операторах можно вызывать только функции. Кроме того, такая обертка нужна, чтобы можно было задать прагму AUTONOMOUS TRANSACTION:

tkyte@TKYTE816> create or replace

2 function set up report F(p argl in number, p arg2 in date)

3 return number

4 as

5 pragma autonomous transaction;

6 begin

7 set up report(p arg1, p arg2) ;

8 commit;

1180 Глава 15

9 return 1;

10 exception

11 when others then

12 rollback;

13 return 0;

14 end;

15 /

Function created.

tkyte@TKYTE816> select set up report F(l, sysdate) from dual

2 /

SET UP REPORT F(1,SYSDATE)

tkyte@TKYTE816> select * from report parm table 2

Интересно посмотреть, что произойдет, если попытаться вызвать эту функцию в SQL-операторе, не объявив ее как автономную транзакцию. Если перекомпилировать представленную выше функцию без прагмы, при выполнении оператора будут получены следующие сообщения:

tkyte@TKYTE816> select set up report F(1, sysdate) from dual 2 /

select set up report F(l, sysdate) from dual *

ERROR at line 1:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML ORA-06512: at TKYTE.SET UP REPORT F , line 10 ORA-14551: cannot perform a DML operation inside a query

ORA-06512: at line 1

Именно этого и позволяет избежать автономная транзакция. Итак, мы создали функцию, которую можно вызывать из SQL-операторов и которая вставляет строку в базу данных. Важно помнить, что эта функция должна обязательно зафиксировать (или откатить) транзакцию до завершения работы - в противном случае будет получено сообщение об ошибке ORA-06519 (подробнее см. далее в разделе Возможные сообщения об ошибках ). Кроме того, функция обязательно должна возвращать значение. Моя функция возвращает 1 в случае успешного выполнения и 0 - в случае неудачи. Кроме того, надо учитывать, что функция может иметь только параметры, передаваемые в режиме IN, - никаких параметров IN/OUT или OUT. Дело в том, что SQL не позволяет задавать параметры с этими режимами передачи.

Я хочу рассказать о проблемах при использовании описанного подхода. Обтчно я описываю проблемы в конце главы, но в данном случае проблемы непосредственно связаны с изменением базы данных операторами SELECT. У таких изменений могут бгть опасные побочные эффекты, связанные со способом оптимизации и выполнения запросов. Представленный выше пример б1л достаточно безопасен. Таблица DUAL - однострочная, мы выбирали значение функции, и вызываться функция будет только один раз. Не было никаких соединений, предикатов, сортировок и побочных эффектов. Она

Автономные транзакции 1181

должна работать надежно. Иногда функция вызывается меньшее или большее количество раз, чем предполагалось. Чтобы продемонстрировать это, я прибегну к несколько надуманному примеру. Используем простую таблицу COUNTER, которую автономная транзакция будет обновлять при каждом выполнении. Таким образом, мы сможем выполнять запросы и видеть, сколько раз вызывалась функция:

tkyte@TKYTE816> create table counter (x int) ;

Table created.

tkyte@TKYTE816> insert into counter values (0); 1 row created.

tkyte@TKYTE816> create or replace function f return number

2 as

3 pragma autonomous transaction;

4 begin

5 update counter set x = x+1;

6 commit;

7 return 1;

8 end;

Function created.

Итак, мы создали таблицу COUNTER и функцию. При каждом вызове функции F значение X будет увеличиваться на 1. Давайте попробуем:

tkyte@TKYTE816> select count(*)

2 from (select * from emp)

COUNT(*)

tkyte@TKYTE816> select * from counter;

Как видите, функция ни разу не вызывалась, хотя должна бтла вызываться 14 раз. Чтобы продемонстрировать, что функция F работает, выполним следующий оператор:

tkyte@TKYTE816> select count(*)

2 from (select f from emp union select f from emp)

COUNT(*)

tkyte@TKYTE816> select * from counter;