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.
|COMMIT||Saves all the pending transaction|
|ROLLBACK||Discard all the pending transaction|
|SAVEPOINT||Creates a point in the transaction till which rollback can be done later|
|ROLLBACK TO||Discard all the pending transaction till the specified <save point>|
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)
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.
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;
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 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.