建立自由性交易, 使用名为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 v
--test autonomous transaction
CREATE TABLE logging_infos(log_info VARCHAR2(200), log_time DATE);
COMMIT ;
/* Formatted on 2006/04/27 14:05 (Formatter Plus v
--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 ('
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博客 ” ，链接：https://blog.itpub.net/226700/viewspace-830834/ ，如需转载，请注明出处，否则将追究法律责任。
