TCL Commands in Oracle

 TCL Commands in Oracle

What is TCL Command

TCL Command means Transaction Control Language which are used to maintain transaction processing in Databases. TCL commands are required when wrong DML commands are fired or transaction failure occurred and we want undo the changes which are recently done or restore last records before processing the wrong commands. I hope now you understood why TCL Commands are highly required in Relational Database Management System.

In TCL, There are three commands-

1. SAVEPOINT - This command is used to create a point and assumed till that point all the data are correct and there was no problem with that data. If we fire further DML queries on Table and suppose there is some kind of failure occurred or executed DML query is incorrect than we can undo the changes which are wrong and get our last correct data by reaching the point called SAVEPOINT.

Syntax of SAVEPOINT creation-

    CREATE SAVEPOINT Savepoint_Name;

2. ROLLBACK- This Command is used to jump to the point called SAVEPOINT which is provided at the time of Rollback command execution. After creation the SAVEPOINT there are some DML queries are fired but in these queries if any query executed wrongly than we uses the rollback to get the last committed data.

Syntax of Rollback Command-

    ROLLBACK TO Savepoint_Name;

3. COMMIT- This command is used when we ensure about the DML queries fired correctly and there is not any kind of failure occurred. In other words we can say that we want to save data permanently after the updates that occurred due to the execution of DML Queries on Table.

Syntax of Commit Command-

    COMMIT;

Let us understand step by step by taking an example- 

1. Create a table called Employee by given command-

    CREATE TABLE Employee

    (

        EmpID INT PRIMARY KEY,

        Name VARCHAR(20) NOT NULL,

        Mobile NUMBER(10) UNIQUE,

        Salary NUMERIC(10,2) NOT NULL,

        City CHAR(20)

    );

    after execution Employee table will be created.

2. Insert to records inside Employee table by given commands-

    INSERT INTO Employee VALUES ( 1,'BHUMIKA', 1234567890, 16000, 'BHOPAL');

    INSERT INTO Employee VALUES ( 2,'MONIKA', 1234567892, 18000, 'INDORE');

    after executing each command one row will be created hence to rows will be created overall.

3. Now we want to see the record of Employee table by given command-

    SELECT * FROM Employee;

    Now we get the result like this-

              

EmpID	Name	Mobile	Salary	City
1	BHUMIKA	1234567890	16000	BHOPAL
2	MONIKA	1234567892	18000	INDORE

4. Now we are going to create a SAVEPOINT called S1 by given command-

    SAVEPOINT S1;

    after executing this command we get a SAVEPOINT created message.

5. Now we are going to execute update (any DML) command by given below-

    UPDATE Employee SET Salary=20000 WHERE EmpID=1;

   

    when we execute this command we get a message of successful update. To confirm this we can select

    Record from Employee Table by given below command-

    SELECT * FROM Employee;

EmpID	Name	Mobile	Salary	City
1	BHUMIKA	1234567890	20000	BHOPAL
2	MONIKA	1234567892	18000	INDORE

As seen the above table that the record is updated but at this point we realize that there is no need to increase the salary of Employee whose EmpID is 1 than we called Rollback command to get our record back as before the creation of SAVEPOINT S1.

6. Now we called Rollback Command by given below-

    ROLLBACK TO S1;

    After executing this query Rollback completed message will be appeared. For confirmation rollback

    is done or not we can fire select command again by following below command-

    SELECT * FROM Employee;

 

EmpID	Name	Mobile	Salary	City
1	BHUMIKA	1234567890	16000	BHOPAL
2	MONIKA	1234567892	18000	INDORE

    As we can see the after the rollback command we get the same record as before the SAVEPOINT

    creation means there is no affect of update command after rollback.

    But in case if we want to save data permanently after the update command than we can use the

    commit command at the place of Rollback command (step 6) as given below-

    COMMIT;

    This command permanently save the updated record after this command execution, Rollback

    command is useless and will generate an error.  

Let us Check These Commands in Run SQL Command Line-

1. Below Image show the use of Rollback Command-

TCL Command Rollback Example

2. Below Image show the use of Commit Command-

TCL Command Commit Example

As we can see there is an error after commit command when we use rollback command.