ORACLE自治事务是一个不错的功能,关键字是AUTONOMOUS_TRANSACTION,下面是我试用:
1、测试:
创建一个测试表:
CREATE TABLE TBL_TEST(
TEST_ID NUMBER NOT NULL,
TEST_DESC VARCHAR2(100) NOT NULL
);
新增两条记录(注意没有进行提交):
INSERT INTO TBL_TEST VALUES(1,'DESC 1');
INSERT INTO TBL_TEST VALUES(2,'DESC 2');
通过自治事务添加6两条记录:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3..8 LOOP
INSERT INTO TBL_TEST VALUES(i,'DESC '||i);
END LOOP;
COMMIT;
END;
/
查看执行结果如下:
SQL> SELECT * FROM TBL_TEST;
TEST_ID TEST_DESC
---------- --------------------------------------------------------------------------------
1 DESC 1
2 DESC 2
3 DESC 3
4 DESC 4
5 DESC 5
6 DESC 6
7 DESC 7
8 DESC 8
8 rows selected
回滚后再次查看结果(通过结果可以看到采用自治事务的新增没有被回滚掉):
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TBL_TEST;
TEST_ID TEST_DESC
---------- --------------------------------------------------------------------------------
3 DESC 3
4 DESC 4
5 DESC 5
6 DESC 6
7 DESC 7
8 DESC 8
6 rows selected
2、应用自治事务实现日志记录:
创建一个日志表:
CREATE TABLE TBL_LOG(
LOG_ID NUMBER(10) PRIMARY KEY,
LOG_MSG VARCHAR2(4000) NOT NULL,
LOG_TIME DATE NOT NULL
);
创建一个序列:
CREATE SEQUENCE SEQ_TBL_LOG;
创建一个记录日志的存储过程:
CREATE OR REPLACE PROCEDURE DO_LOG(P_MSG IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TBL_LOG VALUES(SEQ_TBL_LOG.NEXTVAL,P_MSG,SYSDATE);
COMMIT;
END;
/
测试上面的存储过程:
BEGIN
INSERT INTO TBL_TEST VALUES (100,'DESC 100');
-- test the do_log
INSERT INTO TBL_TEST VALUES (101,NULL);
EXCEPTION
WHEN OTHERS THEN
DO_LOG(P_MSG =>SQLERRM);
ROLLBACK;
END;
/
查看运行结果:
SQL> SELECT * FROM TBL_TEST WHERE TEST_ID>=100;
TEST_ID TEST_DESC
---------- --------------------------------------------------------------------------------
SQL> SELECT * FROM TBL_LOG;
LOG_ID LOG_MSG LOG_TIME
----------- -------------------------------------------------------------------------------- -----------
1 ORA-01400: 无法将 NULL 插入 ("FWMS4ZH_TEST"."TBL_TEST"."TEST_DESC") 2009-5-15 2