oracle 自治事務 PRAGMA AUTONOMOUS_TRANSACTION

在觸發器中操作觸發此觸發器的表,用PRAGMA AUTONOMOUS_TRANSACTION選項。

15.1為何使用自治事務 無法復原的審計

一般情況下利用觸發器禁止某些對錶的更新等操作時，若記錄日誌，則觸發器最後拋出異常時會造成日誌復原。利用自治事務可防止此點。

避免變異表

即在觸發器中操作觸發此觸發器的表

在觸發器中使用DDL 寫資料庫

對資料庫有寫操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的預存程序或函數是無法簡單的用SQL來調用的，此時可以將其設為自治事務，從而避免ORA-14552（無法在一個查詢或DML中執行DDL、COMMIT、ROLLBACK）、ORA-14551（無法在一個查詢中執行DML操作）等錯誤。需要注意的是函數必須有傳回值，但僅有IN參數（不能有OUT或IN/OUT參數）。

開發更模組化的代碼

在大型開發中，自治事務可以將代碼更加模組化，失敗或成功時不會影響調用者的其它操作，代價是調用者失去了對此模組的控制，並且模組內部無法引用調用者未提交的資料。

15.2 如何工作 事務控制

DECLARE整個塊都是屬於父事務的，自治事務從離PRAGMA後的第一個BEGIN開始，只要此BEGIN塊仍在範圍，則都屬於自治事務。例如在DECLARE模組中聲明一個寫資料庫的函數，則此函數雖然在自治事務所在預存程序執行，但其屬於父事務；而自治事務中調用的任何函數和預存程序、激發的任何觸發器等均為此自治事務的一部分。

自治事務可以嵌套，嵌套深度等只受INIT.ORA參數TRANSACTIONS（同時並發的事務數，預設為SESSIONS的1.1倍）制約。

範圍

1. 包中的變數

自治事務可看到並修改父事務的變數，父事務也會察覺到這一改變，且不存在復原問題。

2. 會話設定/參數

自治事務與父事務共用同一個會話環境，通過ALTER SESSION作的修改對整個會話均有效。但SET TRANSACTION是事務級的，僅對提起修改的事務有效。

3. 資料庫修改

父事務已提交的修改對自治事務可見，未提交的對自治事務不可見，自治事務的修改對父事務是否可見取決於隔離等級（Isolation Level）。

對於遊標，取決於其開啟的位置，若其在父事務中開啟，則之前父事務未提交的修改對其是有效，在自治事務中這些修改也可見；而在自治事務中開啟，則父事務未提交的修改不可見。

若使用預設的READ COMMITTED隔離等級，則自治事務的修改對父事務可見；若改用SERIALIZABLE，則不可見。

4. 鎖

父事務與自治事務是完全不同的事務，因此無法共用鎖定等。

結束一個自治事務必須提交一個COMMIT、ROLLBACK或執行DDL。

儲存點無法在自治事務中復原到父事務中的一個儲存點，只能在內部使用儲存點。

15.3 最後說明 不支援分散式交易截至8.1.7在自治事務中不支援分散式交易

僅可用PL/SQL 全部交易回復若自治事務出錯，則全部復原，即便父事務有異常處理模組。

事務級暫存資料表每個會話僅一個事務可訪問事務級暫存資料表（多個會話中的事務可並行作業）。

15.4 可能遇到的錯誤
ORA-06519 – 檢查到活動自治事務，復原——退出自治事務時沒有提交、復原或DDL操作

ORA-14450 – 試圖訪問正在使用的事務級暫存資料表

ORA-00060 – 等待資源時檢查到死結

-- 附一簡單的例子

create or replace procedure ddl_proc(str_sql in varchar2 )
is
pragma AUTONOMOUS_TRANSACTION;
BEGIN
       execute immediate str_sql;
END;

在trigger裡面，可以用ddl_proc執行DDL,DML