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当成独立存在的区域来处理.

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