Autonomous affairs of Oracle
Autonomous transactions (autonomous transaction) allow you to create a "transaction in a transaction" that can be committed or rolled back independently of its parent transaction. With autonomous transactions, you can suspend the currently executed transaction, start a new transaction, complete some work, and then commit or roll back, all of which do not affect the state of the currently executed transaction. Autonomous transactions provide a new way to control transactions with PL/SQL, which can be used to:
Top-level anonymous block
Local (procedures in process), independent or packaged functions and procedures
Methods of object types
Database triggers.
Use examples to demonstrate how autonomous transactions work
-- create test tables to save information zx@ORCL > create table t (msg varchar2 (25)); Table created.-- to create stored procedures for autonomous transactions zx@ORCL > create or replace procedure Autonomous_Insert 2 as pragma autonomous_transaction;--- indicates autonomous transaction statements 4 begin 5 insert into t values ('Autonomous Insert'); 6 commit; 7 end 8 / Procedure created.-- create a common stored procedure zx@ORCL > create or replace procedure NonAutonomous_Insert 2 as 3 begin 4 insert into t values ('NonAutonomous Insert'); 5 commit; 6 end; 7 / Procedure created.
Observe the behavior of using non-autonomous transactions in PL/SQL code
Zx@ORCL > begin 2 insert into t values ('Anonymous Block'); 3 NonAutonomous_Insert; 4 rollback; 5 end; 6 / PL/SQL procedure successfully completed.zx@ORCL > select * from t MSG----Anonymous BlockNonAutonomous Insert
You can observe that the commit in the process of the non-autonomous transaction also commits the parent transaction that called it, while the rollback in the parent transaction does not work.
Then observe the behavior of using non-autonomous transactions in PL/SQL code
Zx@ORCL > delete from t * * 2 rows deleted.zx@ORCL > commit;Commit complete.zx@ORCL > begin insert into t values ('Anonymous Block'); Autonomous_Insert; rollback;end; 6 / PL/SQL procedure successfully completed.zx@ORCL > select * from t MSG----Autonomous Insert
As you can see, the commit in the autonomous transaction only commits its own transaction, but the statement in the parent transaction has no effect, and the rollback in the parent transaction has no effect on the statement in the autonomous transaction.
