COMMIT, ROLLBACK & SAVEPOINT

You can use the COMMIT, ROLLBACK and SAVEPOINT  command to control the transaction.

1. COMMIT : COMMIT command to make changes permanent save to a database during the current transaction.
2. ROLLBACK : ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction.
3. SAVEPOINT : SAVEPOINT command save the current point with the unique name in the processing of a transaction.
4. AUTOCOMMIT : Set AUTOCOMMIT ON to execute COMMIT Statement automatically.
5. SET TRANSACTION : PL/SQL SET TRANSACTION command set the transaction properties such as read-write/read only access.

Commit

The COMMIT statement to make changes permanent save to a database during the current transaction and visible to other users,

Commit Syntax :

SQL>COMMIT [COMMENT "comment text"];

Commit comments are only supported for backward compatibility. In a future release commit comment will come to a deprecated.

Commit Example :

SQL>BEGIN
 UPDATE emp_information SET emp_dept='Web Developer'
  WHERE emp_name='Saulin';
    COMMIT;
END;
/

Rollback

The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data. If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.

ROLLBACK Syntax:

SQL>ROLLBACK [To SAVEPOINT_NAME];

ROLLBACK Example :

SQL>DECLARE
   emp_id  emp.empno%TYPE;
BEGIN
   SAVEPOINT dup_found;
   UPDATE emp SET eno=1 
     WHERE empname = 'Forbs ross'
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO dup_found;
END;
/

Above example statement is exception raised because eno = 1 is already so DUP_ON_INDEX exception rise and rollback to the dup_found savepoint named.

Savepoint

SAVEPOINT savepoint_names marks the current point in the processing of a transaction. Savepoints let you rollback part of a transaction instead of the whole transaction.

SAVEPOINT Syntax :

SQL>SAVEPOINT SAVEPOINT_NAME;

SAVEPOINT Example :

SQL>DECLARE
   emp_id  emp.empno%TYPE;
BEGIN
   SAVEPOINT dup_found;
   UPDATE emp SET eno=1 
     WHERE empname = 'Forbs ross'
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO dup_found;
END;
/

Autocommit

No need to execute COMMIT statement every time. You just set AUTOCOMMIT ON to execute COMMIT Statement automatically. It's automatic execute for each DML statement. set auto commit on using following statement,

AUTOCOMMIT Example :

SQL>SET AUTOCOMMIT ON;

You can also set auto commit off,

SQL>SET AUTOCOMMIT OFF;

<< BACK HOME >>