Autonomous Transaction in Oracle PL/SQL: Commit, Rollback

What are TCL Statements in PL/SQL?

TCL stands for Transaction Control Statements. It will either save the pending transactions or roll back the pending transaction. These statements play the vital role because unless the transaction is saved the changes through DML statements will not be saved in the database. Below are the different TCL statements.

The transaction will be complete under the following scenarios.

• When any of the above statements is issued (except SAVEPOINT)
• When DDL statements are issued. (DDL are auto-commit statements)
• WHEN DCL statements are issued. (DCL are auto-commit statements)

What is Autonomous Transaction

In PL/SQL, all the modifications done on data will be termed as a transaction. A transaction is considered as complete when the save/discard is applied to it. If no save/discard is given, then the transaction will not be considered as complete and the modifications done on the data will not be made permanent on the server.

Irrespective of some modifications done during a session, PL/SQL will treat the whole modification as a single transaction and saving/discard this transaction affects to the entire pending changes in that session. Autonomous Transaction provides a functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction.

• This autonomous transaction can be specified at subprogram level.
• To make any subprogram to work in a different transaction, the keyword 'PRAGMA AUTONOMOUS_TRANSATION' should be given in the declarative section of that block.
• It will instruct that compiler to treat this as the separate transaction and saving/discarding inside this block will not reflect in the main transaction.
• Issuing COMMIT or ROLLBACK is mandatory before going out of this autonomous transaction to the main transaction because at any time only one transaction can be active.
• So once we made an autonomous transaction we need to save it and complete the transaction then only we can move back to the main transaction.

Syntax:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; 
.
BEGIN
<executin_part>
[COMMIT|ROLLBACK]
END;
/

• In the above syntax, the block has been made as an autonomous transaction.

Example 1: In this example, we are going to understand how the autonomous transaction is working.

DECLARE
   l_salary   NUMBER;
   PROCEDURE nested_block IS
   PRAGMA autonomous_transaction;
    BEGIN
     UPDATE emp
       SET salary = salary + 15000
       WHERE emp_no = 1002;
   COMMIT;
   END;
BEGIN
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
   dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
   dbms_output.put_line('Before Salary of 1002 is'|| l_salary);    
   UPDATE emp 
   SET salary = salary + 5000 
   WHERE emp_no = 1001;

nested_block;
ROLLBACK;

 SELECT salary INTO  l_salary FROM emp WHERE emp_no = 1001;
 dbms_output.put_line('After Salary of 1001 is'|| l_salary);
 SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
 dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;

Output

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000

Code Explanation:

• Code line 2: Declaring l_salary as NUMBER.
• Code line 3: Declaring nested_block procedure
• Code line 4: Making nested_block procedure as 'AUTONOMOUS_TRANSACTION'.
• Code line 7-9: Increasing the salary for employee number 1002 by 15000.
• Code line 10: Committing the transaction.
• Code line 13-16: Printing the salary details of employee 1001 and 1002 before changes.
• Code line 17-19: Increasing the salary for employee number 1001 by 5000.
• Code line 20: Calling the nested_block procedure;
• Code line 21: Discarding the main transaction.
• Code line 22-25: Printing the salary details of employee 1001 and 1002 after changes.
• The salary increase for employee number 1001 is not reflected because the main transaction has been discarded. The salary increase for employee number 1002 is reflected because that block has been made as a separate transaction and saved at the end.
• So irrespective of the save/discard at main transaction the changes at autonomous transaction has been saved without affecting the main transaction changes.