Autonomous Transactions from oracle doc。At times, you may want to commit or roll back some changes to a table independently of a primary transaction's final outcome. For example, in a stock purchase transaction, you may want to commit a customer's information regardless of whether the overall stock purchase actually goes through. Or, while running that same transaction, you may want to log error messages to a debug table even if the overall transaction rolls back. Autonomous transactions allow you to do such tasks. An autonomous transaction (AT) is an independent transaction started by another transaction, the main transaction (MT). It lets you suspend the main transaction, do SQL operations, commit, or roll back those operations, then resume the main transaction. An autonomous transaction executes within an autonomous scope. An autonomous scope is a routine you with the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to a routine as autonomous (independent). In this context, the term routine includes: Top-level (not nested) anonymous PL/SQL blocks Local, stand-alone, and packaged functions and procedures Methods of a SQL object type PL/SQL triggers Example 1: Using an Autonomous Trigger Among other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit inserts into the shadow table whether or not you commit inserts into the main table. -- create a main table and its shadow table CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15)); CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15));-- ceate an autonomous trigger that inserts into the -- shadow table before each insert into the main table CREATE TRIGGER parts_trig BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; END;-- insert a row into the main table, and then commit the insert INSERT INTO parts VALUES (1040, 'Head Gasket'); COMMIT;-- insert another row, but then roll back the insert INSERT INTO parts VALUES (2075, 'Oil Pan'); ROLLBACK;-- show that only committed inserts add rows to the main table SELECT * FROM parts ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket-- show that both committed and rolled-back inserts add rows -- to the shadow table SELECT * FROM parts_log ORDER BY pnum; PNUM PNAME ------- --------------- 1040 Head Gasket 2075 Oil PanExample 2: Calling an Autonomous Function from SQL A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Sides Effects".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES, which instructs the compiler to report reads of/writes to database tables, package variables, or both. (See Oracle8i Application Developer's Guide - Fundamentals.) However, all autonomous routines have read/write access to the database. So, they never violate the rules "read no database state" and "write no database state." This can be useful, as the example below shows. When you call the packaged function log_msg from a query, it inserts a message into database table debug_output without violating the rule "write no database state." -- create the debug table CREATE TABLE debug_output (msg VARCHAR2(200));-- create the package spec CREATE PACKAGE debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS); END debugging;-- create the package body CREATE PACKAGE BODY debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- the following insert does not violate the constraint -- WNDS because this is an autonomous routine INSERT INTO debug_output VALUES (msg); COMMIT; RETURN msg; END; END debugging;-- call the packaged function from a query DECLARE my_empno NUMBER(4); my_ename VARCHAR2(15); BEGIN ... SELECT debugging.log_msg(ename) INTO my_ename FROM emp WHERE empno = my_empno; -- even if you roll back in this scope, the insert -- into 'debug_output' remains committed because -- it is part of an autonomous transaction IF ... THEN ROLLBACK; END IF; END;
ExampleIf a problem occurs, only changes since beta will be rolled back.BEGIN SAVEPOINT alpha; ... SAVEPOINT beta; ... ... -- statements that do database changes ... IF condition THEN ROLLBACK TO beta; END IF; END;
这个新创建的事物进行操作，同时保持原有事物不变
Among other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In the example below, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit inserts into the shadow table whether or not you commit inserts into the main table. -- create a main table and its shadow table
CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15));
CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15));-- ceate an autonomous trigger that inserts into the
-- shadow table before each insert into the main table
CREATE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT;
END;-- insert a row into the main table, and then commit the insert
INSERT INTO parts VALUES (1040, 'Head Gasket');
COMMIT;-- insert another row, but then roll back the insert
INSERT INTO parts VALUES (2075, 'Oil Pan');
ROLLBACK;-- show that only committed inserts add rows to the main table
SELECT * FROM parts ORDER BY pnum;
PNUM PNAME
------- ---------------
1040 Head Gasket-- show that both committed and rolled-back inserts add rows
-- to the shadow table
SELECT * FROM parts_log ORDER BY pnum;
PNUM PNAME
------- ---------------
1040 Head Gasket
2075 Oil PanExample 2: Calling an Autonomous Function from SQL
A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Sides Effects".) To check for violations of the rules, you can use the pragma RESTRICT_REFERENCES, which instructs the compiler to report reads of/writes to database tables, package variables, or both. (See Oracle8i Application Developer's Guide - Fundamentals.) However, all autonomous routines have read/write access to the database. So, they never violate the rules "read no database state" and "write no database state." This can be useful, as the example below shows. When you call the packaged function log_msg from a query, it inserts a message into database table debug_output without violating the rule "write no database state." -- create the debug table
CREATE TABLE debug_output (msg VARCHAR2(200));-- create the package spec
CREATE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;-- create the package body
CREATE PACKAGE BODY debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- the following insert does not violate the constraint
-- WNDS because this is an autonomous routine
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END;
END debugging;-- call the packaged function from a query
DECLARE
my_empno NUMBER(4);
my_ename VARCHAR2(15);
BEGIN
...
SELECT debugging.log_msg(ename) INTO my_ename FROM emp
WHERE empno = my_empno;
-- even if you roll back in this scope, the insert
-- into 'debug_output' remains committed because
-- it is part of an autonomous transaction
IF ... THEN
ROLLBACK;
END IF;
END;
SAVEPOINT alpha;
...
SAVEPOINT beta;
...
... -- statements that do database changes
...
IF condition THEN
ROLLBACK TO beta;
END IF;
END;