Oracle 的 Autonomous_transaction( 自由性交易)

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

Delcare or replace procedure procedure_name(params, types)

AS

PRAGMA AUTONOMOUS_TRANSACTION;

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

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

[@more@]

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-830834/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-830834/