Explain the Rollback statement?

by

The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement:

  • The work done in a transition is undone as if it was never issued.
  • All locks acquired by transaction are released.

In PL/SQL (Procedural Language/Structured Query Language), the ROLLBACK statement is used to undo the changes made in the current transaction. Transactions in a database are units of work that consist of one or more SQL statements. The ROLLBACK statement is essential in maintaining the consistency and integrity of the database.

When a ROLLBACK statement is executed, it:

  1. Undoes Changes: Reverts any modifications made to the data during the current transaction. This includes changes made by INSERT, UPDATE, or DELETE statements.
  2. Releases Locks: If any locks were acquired during the transaction, they are released, allowing other transactions to access the locked resources.
  3. Restores Savepoints: If savepoints were defined during the transaction, the ROLLBACK statement can be used to roll back to a specific savepoint, undoing changes up to that point.

Here’s a simple example:

-- Starting a transaction
BEGIN
-- Some SQL statements that modify data
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
DELETE FROM employees WHERE employee_id = 101;— Creating a savepoint
SAVEPOINT my_savepoint;

— More SQL statements
UPDATE employees SET commission_pct = 0.15 WHERE department_id = 20;

— Rolling back to the savepoint
ROLLBACK TO my_savepoint;

— Other SQL statements
— …

— Committing the transaction (or can be rolled back entirely)
COMMIT;
EXCEPTION
WHEN OTHERS THEN
— Handling exceptions, logging, etc.
ROLLBACK; — Ensure a rollback in case of an error
END;

In this example, if an exception occurs, the ROLLBACK statement is used to undo any changes made in the transaction, ensuring that the database remains in a consistent state. The COMMIT statement is used to permanently apply the changes, but it’s usually placed outside the exception block to avoid committing if an error occurs.