PL/SQL>SQL>Transaction Control Language

Transaction Control Language(commit,rollback,savepoint)

COMMIT

This will be used to save the work.
Commit is of two types.

  1. Implicit
  2. Explicit

Implicit:

This will be issued by oracle internally in two situations.
      - When any DDL operation is performed.
      - When you are exiting from SQL * PLUS.

Explicit:

This will be issued by the user.

Syntax:
      Commit or commit work;
      * Whenever you have committed then the transaction was completed.

ROLL BACK

This will undo the operation.
This will be applied in two methods.
      1 Upto previous commit
      2 Upto previous rollback

Syntax:
       Roll or roll work; (Or)
       Rollback or rollback work;
* While process is going on, if suddenly power goes then oracle will rollback the transaction.

SAVE POINT

You can use savepoints to rollback portions of your current set of transactions.

Syntax:
      Savepoint savepoint_name;

Example:
      SQL> savepoint s1;
      SQL> insert into Graduates values(1, a, 100);
      SQL> savepoint s2;
      SQL> insert into Graduates values(2, b, 200);
      SQL> savepoint s3;
      SQL> insert into Graduates values(3, c, 300);
      SQL> savepoint s4;
      SQL> insert into Graduates values(4, d, 400);
Before rollback

      SQL> select * from Graduates;

      NO   DNAME   MARKS
      --------   --------   ----
      1        a        100
      2        b        200
      3        c        300
      4        d        400

      SQL> rollback to savepoint s3; (Or)
      SQL> rollback to s3;

This will rollback last two records.

      SQL> select * from Graduates;

      NO   DNAME   MARKS
      --------   --------   ----
      1        a        100
      2        b        200

Go Back