Oracle 的 Autonomous_transaction( 自由性交易)

建立自由性交易, 使用名为autonomous_transaction的编译指示(编译命令,如, #include), 这个编译命令要放在declare里面.


Delcare or replace procedure procedure_name(params, types)


AS


PRAGMA AUTONOMOUS_TRANSACTION;


当程序执行的时候,pl/sql 会把autonomous_transaction当成独立存在的区域来处理.

用途是用在记录程序事件,不影响程序的进行.


一切用pl/sql 代码说话:


/* Formatted on 2006/04/27 14:05 (Formatter Plus v4.8.7) */


--test autonomous transaction


CREATE TABLE logging_infos(log_info VARCHAR2(200), log_time DATE);


COMMIT ;


/* Formatted on 2006/04/27 14:05 (Formatter Plus v4.8.7) */


--create autonomous transaction procedure


CREATE OR REPLACE PROCEDURE logging_info (


log_info_in VARCHAR2,


log_time_in DATE


)


--If your procedure's name is exit in oracle( DB) , then you cann't use it to declare a procedure with the same name


AS


PRAGMA AUTONOMOUS_TRANSACTION;


BEGIN


INSERT INTO logging_infos


(log_info, log_time


)


VALUES (log_info_in, log_time_in


);


COMMIT;


END;


books;


--create main procedure


CREATE OR REPLACE PROCEDURE book_insert (


isbn_in VARCHAR2,


title_in VARCHAR2,


summary_in VARCHAR2,


author_in VARCHAR2,


date_published_in DATE,


page_count_in NUMBER


)


AS


BEGIN


INSERT INTO books


(isbn, title, author, date_published,


page_count


)


VALUES (isbn_in, title_in, author_in, date_published_in,


page_count_in


);


-- call autonomous transcation


logging_info ('aaron_sys', SYSTIMESTAMP);


ROLLBACK;


--rollback for main procedure , not the autonomous transcation


EXCEPTION


WHEN OTHERS


THEN


dbms_ouput.put_line (SQLERRM);


END;


--test the main procedure


BEGIN


book_insert ('ISBN987654321',


'TEST2',


'summary:ddddd',


'Aaron',


TO_DATE ('2006-04-25', 'yyyy-mm-dd'),


88


);


END;


--successfuly completed


SELECT *


FROM books; ---doesn't have the add info


SELECT *


FROM logging_infos; --the logging info is added .


自动交易(autonomous_transaction 繁体版本的书,简体翻译成自由性交易或事前交易,还是觉得自由性交易比较好) , 一定要用commit or rollback, 如果不用的结果就是:


可以编译,但是在执行的时候会提示:


ORA-06519: active autonomous transaction detected and rolled back

ORA-06512: at "SYS.LOGGING_INFO", line 10

ORA-06512: at "SYS.BOOK_INSERT", line 15

ORA-06512: at line 2


建立自由性交易, 使用名为autonomous_transaction的编译指示(编译命令,如, #include), 这个编译命令要放在declare里面.


Delcare or replace procedure procedure_name(params, types)


AS


PRAGMA AUTONOMOUS_TRANSACTION;


当程序执行的时候,pl/sql 会把autonomous_transaction当成独立存在的区域来处理.

用途是用在记录程序事件,不影响程序的进行.