操作環境 oracle11g
CREATE TABLE EMPLOYEE_T
(
EMPLOYEE_ID VARCHAR2(20),
EMPLOYEE_NAME VARCHAR2(20)
);
在存儲過程中，尤其是一組相互調用的存儲過程中如果要為其中的每個存儲過程記錄執行日志時會存在比較麻煩的問題。即在操作出現異常時如何記錄相關異常日志(這個時候的日志應該才是最重要的吧>_<!!!)，此時如果調用一般的事務方式進行commit以保存日志則腳本編寫會相當煩瑣且維護性差。此時可以考慮采用自治事務的方式來提交執行日志
自治事務相當于與當前事務并行的另一個事務，其提交與否并不影響當前主要事務的提交與回滾，通常定義在函數與存儲過程之中方式如下
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_AUTONOMOUS
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('007','TS5');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('008','TS6');
COMMIT;
END SP_EMPLOYEE_AUTONOMOUS;
配合實現方式為
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE
IS
BEGIN
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('003','TS1');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('004','TS2');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('005','TS3');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('006','TS4');
SP_EMPLOYEE_AUTONOMOUS();
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('009','TS7');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('0010','TS8');
ROLLBACK;
END SP_EMPLOYEE;
則執行的結果則只插入007與008兩條記錄
PS:一種錯誤的實現方式，如下
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_AUTONOMOUS
IS
BEGIN
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('001','TS1');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('002','TS2');
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('007','TS5');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('008','TS6');
COMMIT;
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('003','TS7');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('004','TS8');
ROLLBACK;
END SP_EMPLOYEE_AUTONOMOUS;
此時編譯與執行SP_EMPLOYEE_AUTONOMOUS且不報錯，但是執行的結果就~~~~~~~~~~~~~~~~~ ^_^