Автономні транзакції, Інтеграція додатків і даних, Бази даних, статті

Введення


Автономні транзакції доступні в 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 – дані про якого змінюються, підпорядковується співробітникові,


  1. – Виконує обновленіе.Для побудови ієрархії зручно
  2. – Використовувати конструкцію 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,

  1. “Ви намагаєтеся зробити те, що ви не маєте права” / /
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


  1. – Динамічний sql буде розглянуто в іншому розділі

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 = 😡
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;

8 – Нам доведеться динамічно відкривати наші курсори, так як
9 – під час компіляції ми не знаємо пропозиції “order by”.
10 – Отже, необхідно використовувати ref cursor.
11 type refCur is ref cursor;
12 


  1. – 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 = 😡 ” //
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 = 😡 ) ” //
65 p_order_by
66 USING p_deptno;
67 
68 – Деталізувати цей набір (і кожен поднабор)
69 explode( l_cur, 1 );
70 

  1. – То буде видане повідомлення
  2. — ORA-06519: active autonomous transaction detected and rolled 
  3. — back 
  4. – (Виявлена ​​активна автономна транзакція і виконаний відкат),
  5. – Яке видалить всі наші рядки!
76 commit;
77 
78 – У випадку успішного завершення, повертаємо відповідне

  1. – Повідомлення …
  2. – Оброблювач виключень, представлений нижче, повертає
  3. – Повідомлення про помилку, у разі невдалого завершення.
  4. 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, вона була вбудована в ядро ​​протягом тривалого часу (а значить добре протестована).

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*