Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.