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

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

tkyte@TKYTE816> create global temporary table temp

2 (x int)

3 on commit delete rows

Table created.

tkyte@TKYTE816> create or replace procedure auto procl

2 as

3 pragma autonomous transaction;

4 begin

5 insert into temp values (1);

6 commit;

7 end;

Procedure created.

tkyte@TKYTE816> create or replace procedure auto proc2

2 as

3 pragma autonomous transaction;

4 begin

5 for x in (select * from temp)

6 loop

7 null;

8 end loop;

9 commit;

10 end;

11 /

Procedure created.

tkyte@TKYTE816> insert into temp values (2); 1 row created.

tkyte@TKYTE816> exec auto proc1; BEGIN auto procl; END;

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use ORA-06512: at TKYTE.AUTO PROC1 , line 5 ORA-06512: at line 1

tkyte@TKYTE816> exec auto proc2; BEGIN auto proc2; END;

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use

ORA-06512: at TKYTE.AUTO PROC2 , line 5 ORA-06512: at line 1

1198

Глава 15

Именно это сообщение об ошибке вы и получите при попытке использовать одну и ту же временную таблицу в обеих транзакциях. Следует отметить, что это происходит только с одновременными транзакциями в одном сеансе. Несколько одновременно выполняющихся транзакций, если только каждая из них выполняется в отдельном сеансе, могут обращаться к временным таблицам уровня транзакции.

Изменяющиеся таблицы

Казалось бы, автономные транзакции позволяют решить все проблемы изменяющихся таблиц. Эти решения, однако, могут стать началом нов1х логических проблем.

Предположим, необходимо обеспечить выполнение правила, по которому максимальная зарплата сотрудника не может более чем вдвое превышать среднюю зарплату сотрудников соответствующего отдела. Можно начать с процедуры и триггера примерно следующего вида:

tkyte@TKYTE816> create or replace

begin

10 11 12 13 14

16 end;

17 /

procedure sal check(p deptno is

avg sal number; max sal number;

in number)

select avg(sal), max(sal) into avg sal, max sal from emp where deptno = p deptno;

if (max sal/2 > avg sal) then

raise application error(-20001,Rule violated);

end if;

Procedure created.

tkyte@TKYTE816> create or replace trigger sal trigger

2 after insert or update or delete on emp

3 for each row

4 begin

5 if (inserting or updating) then

6 sal check(:new.deptno);

7 end if;

9 if (updating or deleting) then

10 sal check(:old.deptno);

11 end if;

12 end;

13 /

Trigger created. tkyte@TKYTE816>

tkyte@TKYTE816> update emp set sal = sal*l.l;

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

update emp set sal = sal* 1.1

ERROR at line 1:

ORA-04091: table TKYTE.EMP is mutating, trigger/function may not see it ORA-06512: at TKYTE.SAL CHECK , line 6 ORA-06512: at TKYTE.SAL TRIGGER , line 3

ORA-04088: error during execution of trigger TKYTE.SAL TRIGGER

Не слишком удачно. Мы сразу же столкнулись с ошибкой изменяющейся таблицы, поскольку нельзя читать таблицу в процессе ее изменения. Сразу приходит в голову мысль: раз таблица изменяется, надо использовать автономную транзакцию. Это и делается:

tkyte@TKYTE816> create or replace

2 procedure sal check(p deptno in number)

3 is

4 pragma autonomous transaction;

5 avg sal number;

6 max sal number;

7 begin

Procedure created.

Кажется, что проблема решена:

tkyte@TKYTE816> update emp set sal = sal*l.l; 14 rows updated. tkyte@TKYTE816> commit; Commit complete.

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

tkyte@TKYTE816> update emp set sal = 99999.99 where ename = WARD; 1 row updated. tkyte@TKYTE816> commit; Commit complete.

tkyte@TKYTE816> exec sal check(30); BEGIN sal check(30); END;

ERROR at line 1:

ORA-20001: Rule violated

ORA-06512: at TKYTE.SAL CHECK , line 14 ORA-06512: at line 1

Я изменил запись служащего WARD, установив ему очень большую зарплату; WARD работает в отделе 30, и его зарплата теперь намного превышает среднюю зарплату по этому отделу. Триггер этого не выявил, но постфактум, выполнив тот же код, что выполняет триггер, мы обнаруживаем нарушение правила. Почему? Потому что в автоном-