PL/SQL ROLLBACK

The ROLLBACK statement in Oracle PL/SQL is a crucial part of transaction control. It allows developers to undo changes made during a transaction, ensuring data integrity and consistency. In this article, we’ll explore its syntax, use cases, and best practices.

What is ROLLBACK in PL/SQL?

In Oracle databases, transactions consist of one or more SQL statements executed as a unit. If an error occurs or if changes need to be undone, the ROLLBACK statement reverses all modifications made in the transaction.

Basic Syntax of ROLLBACK

ROLLBACK;

This simple command undoes all changes since the last COMMIT or the beginning of the transaction.

Rolling Back to a Savepoint

Oracle allows rolling back only a portion of a transaction using SAVEPOINTS. The syntax is:

ROLLBACK TO SAVEPOINT savepoint_name;

This reverts changes made after the specified savepoint, while keeping earlier changes intact.

Examples of Using ROLLBACK

1. Rolling Back an Entire Transaction

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    
    -- Suppose an error occurs, we roll back the entire transaction
    ROLLBACK;
END;

In this case, all salary updates for department 10 are undone.

2. Rolling Back to a Savepoint

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    SAVEPOINT before_bonus;
    
    UPDATE employees SET bonus = bonus + 1000 WHERE department_id = 10;
    
    -- If something goes wrong with the bonus update
    ROLLBACK TO SAVEPOINT before_bonus;
    
    COMMIT;
END;

Here, if the bonus update fails, only that part is undone while keeping the salary updates.

Key Considerations When Using ROLLBACK

Cannot Rollback After a Commit

Once a COMMIT is executed, the changes become permanent, and ROLLBACK has no effect.

Implicit Rollback in Case of Errors

If an exception is unhandled, Oracle automatically rolls back the current transaction.

Savepoints are Transaction-Specific

They exist only within the transaction and are lost after a COMMIT or full ROLLBACK.

Conclusion

The ROLLBACK statement is an essential tool for maintaining data integrity in Oracle PL/SQL. Whether undoing an entire transaction or selectively rolling back changes using SAVEPOINTS, understanding its functionality helps prevent data corruption and ensures safe database operations.