Aim:
To create and execute the savepoint and rollback commands.
Description
Savepoint:
savepoint marks and saves the current point in the processing of a transaction. When a savepoint is used with a rollback statement, parts of a transaction can be undone.
Syntax:
Savepoint <savepointname>;
Commit:
A commit ends the current transaction and makes permanent any changes made during the transaction.
Syntax:
commit;
Rollback:
A rollback does exactly the opposite to commit. It ends the transaction but undoes any changes made during the transaction.
Syntax:
rollback to savepoint <savepointname>;
Example
CREATION OF A TABLE
SQL> create table banktab(accno int, cusname varchar(30), balance int);
Table created.
SQL> insert into banktab values(&accno, ‘&cusname’, &balance);
Enter value for accno: 121
Enter value for cusname: hari
Enter value for balance: 50000
old 1: insert into banktab values(&accno, ‘&cusname’, &balance)
new 1: insert into banktab values(121, ‘hari’, 50000)
1 row created.
SQL> /
Enter value for accno: 122
Enter value for cusname: mahi
Enter value for balance: 4000
old 1: insert into banktab values(&accno, ‘&cusname’, &balance)
new 1: insert into banktab values(122, ‘mahi’, 4000)
1 row created.
SQL> /
Enter value for accno: 123
Enter value for cusname: priya
Enter value for balance: 45000
old 1: insert into banktab values(&accno, ‘&cusname’, &balance)
new 1: insert into banktab values(123, ‘priya’, 45000)
1 row created.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- —————– ————–
121 hari 50000
122 mahi 4000
123 priya 45000
CREATING SAVEPOINT
SQL> savepoint e;
Savepoint created.
SQL> insert into banktab values(124, ‘arun’, 54000);
1 row created.
SQL> update banktab set balance=5000 where accno=122;
1 row updated.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- —————- ————–
121 hari 50000
122 mahi 5000
123 priya 45000
124 arun 54000
ROLLBACK TO SAVEPOINT
SQL> rollback to e;
Rollback complete.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- ————– ————-
121 hari 50000
122 mahi 4000
123 priya 45000
COMMIT
SQL> insert into banktab values(125, ‘prema’, 70000);
1 row created.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- ————— ————–
121 hari 50000
122 mahi 4000
123 priya 45000
125 prema 70000
SQL> update banktab set balance=60000 where accno=125;
1 row updated.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- ————— ————–
121 hari 50000
122 mahi 4000
123 priya 45000
125 prema 60000
SQL> commit;
Commit complete.
SQL> insert into banktab values(127,’jani’,45000);
1 row created.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- ————— ————–
121 hari 50000
122 mahi 4000
123 priya 45000
125 prema 60000
127 jani 45000
ROLLBACK AFTER COMMIT
SQL> rollback;
Rollback complete.
SQL> select * from banktab;
ACCNO CUSNAME BALANCE
———- ————— ————–
121 hari 50000
122 mahi 4000
123 priya 45000
125 prema 60000
Result:
Thus the savepoint, rollback and commit commands are executed successfully.
