December 30, 1899 :
Name:E.Vidya Rani Date:20-02-24
Roll No:22481A1253 Expt. No:1d
Aim:Executing TCL commands
Transaction Control Language (TCL)
Transaction: A group of DML operations which ends with either commit/rollback.
Commit: to make the changes performed through DML write operations permanent in the database.
Till commit is placed, the data is available with buffer. Once the commit is applied the data will be stored in database.
Once the data is stored in database, then only modifications will be visible to the other users on the table.
Modified data will be visible to the user who has modified it. But for remaining
all the old data is visible if it is not committed.
Rollback: to revert the changes/to undo the changes
Whenever we feel the changes are not accurate we can undo the changes
without deleting the records because the changes available in buffer but not
reflected in database.
Savepoint
A temporary saving point within the transaction
Savepoints store within the buffer only(not in database)
No limit for no.ofsavepoints within the transaction.
Use unique names for savepoints (preferably sequential names)
No error will be raised if we use same names for different savepoints but the
previous save point will be erased by new Savepoint.
We need to remember the names of the savepoints because they are not stored
in the database otherwise there is no way of getting them.
Once the transaction is completed (in terms of commit/rollback) all the
savepoints will be erased. we can not refer them again.
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> select * from students1;
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
SQL>savepoint s1;
Savepoint created.
SQL> insert all
2 into students1 values(4,'Deepika',1264,'IT')
3 into students1 values(5,'Devika',1235,'IT')
4 select 1 from dual;
2 rows created.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Deepika 1264 IT
5 Devika 1235 IT
SQL> delete students1 where rno=1235;
1 row deleted.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Deepika 1264 IT
SQL>savepoint s2;
Savepoint created.
SQL> insert into students1 values(5,'Sahithi',1234,'IT');
1 row created.
SQL> update students1 set branch='CSE' where rno=1234;
1 row updated.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Deepika 1264 IT
5 Sahithi 1234 CSE
SQL>savepoint s3;
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :
Savepoint created.
SQL> delete students1 where sno=5;
1 row deleted.
SQL> delete students1 where sno=4;
1 row deleted.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
SQL> rollback to s3;
Rollback complete.
SQL> select * from students1
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Deepika 1264 IT
5 Sahithi 1234 CSE
SQL> rollback to s1;
Rollback complete.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
SQL> insert into students1 values(4,'Renuka',1221,'IT');
1 row created.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Renuka 1221 IT
SQL> update students1 set branch='CSE' where sno=4;
1 row updated.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :
3 Mamatha 6084 IOT
4 Renuka 1221 CSE
SQL> rollback;
Rollback complete.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
SQL> insert into students1 values(4,'Deepika',1264,'IT');
1 row created.
SQL> insert into students1 values(5,'Devika',1235,'IT');
1 row created.
SQL> commit;
Commit complete.
SQL> rollback;
Rollback complete.
SQL> select * from students1;
SNO NAME RNO BRANCH
---------- -------------------- ---------- --------------------
1 Vidya Rani 1253 IT
2 Padmavathi 4277 AIML
3 Mamatha 6084 IOT
4 Deepika 1264 IT
5 Devika 1235 IT
| Seshadri Rao Gudlavalleru Engineering College
