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.

Similar Posts