操作环境 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且不报错，但是执行的结果就~~~~~~~~~~~~~~~~~ ^_^