操作環(huán)境 oracle11g
CREATE TABLE EMPLOYEE_T
(
EMPLOYEE_ID VARCHAR2(20),
EMPLOYEE_NAME VARCHAR2(20)
);
在存儲過程中，尤其是一組相互調(diào)用的存儲過程中如果要為其中的每個存儲過程記錄執(zhí)行日志時會存在比較麻煩的問題。即在操作出現(xiàn)異常時如何記錄相關(guān)異常日志(這個時候的日志應(yīng)該才是最重要的吧>_<!!!)，此時如果調(diào)用一般的事務(wù)方式進(jìn)行commit以保存日志則腳本編寫會相當(dāng)煩瑣且維護(hù)性差。此時可以考慮采用自治事務(wù)的方式來提交執(zhí)行日志
自治事務(wù)相當(dāng)于與當(dāng)前事務(wù)并行的另一個事務(wù)，其提交與否并不影響當(dāng)前主要事務(wù)的提交與回滾，通常定義在函數(shù)與存儲過程之中方式如下
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;
配合實現(xiàn)方式為
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;
則執(zhí)行的結(jié)果則只插入007與008兩條記錄
PS:一種錯誤的實現(xiàn)方式，如下
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;
此時編譯與執(zhí)行SP_EMPLOYEE_AUTONOMOUS且不報錯，但是執(zhí)行的結(jié)果就~~~~~~~~~~~~~~~~~ ^_^