Oracle: SAVEPOINT

SAVEPOINT in Oracle is a TCL Command. TCL here stands for Transaction Control Language command.

In the previous article, we have discussed ROLLBACK and COMMIT Command. Make sure to know these two before stepping into the SAVEPOINT statement.

SAVEPOINT in Oracle is a statement that is used to store the partial changes done in the database. Also, make sure to use COMMIT after the rollback on SAVEPOINTS is done.

Syntax

SAVEPOINT Name_of_savepoint

Example

To check the concept of SAVEPOINT, let's create a table SP_Stu that contains the Name and Marks of Students. While creating we have created different savepoints. Every SAVEPOINT is introduced after 2 insertions.

After the first 2 insertions, a SAVEPOINT named SP_1 is created, after the second 2 insertions SP_2 is created and for the other two insertions, SP_3 is created. Follow the code for the same:

--------------------------SAVEPOINTS--------------------------------------------
Create Table SP_Stu(Name Varchar(20), Marks number(20));

Insert into SP_Stu Values('Bhoomi',80);
Insert into SP_Stu Values('Charu',90);
Savepoint SP_1;

Insert into SP_Stu Values('Disha',60);
Insert into SP_Stu Values('Soniya',50);
Savepoint SP_2;

Insert into SP_Stu Values('himanshu',56);
Insert into SP_Stu Values('Dev',40);
Savepoint SP_3;

Insert into SP_Stu Values('Rashi',70);
Insert into SP_Stu Values('Mohit',10);

Output

SAVEPOINT1

Moving ahead, if you want to ROLLBACK up the last two insertions done, you can use SAVEPOINT SP_3 to ROLLBACK. Follow the code below for the same:

Rollback to SP_3;
Commit;
Select * from SP_Stu;

Output

SAVEPOINT2

Also, below is the Code and Output for what happens if we ROLLBACK to SP_1.

Rollback to SP_1;
Commit;
Select * from SP_Stu;

Output