Explain COMMIT, ROLLBACK, and SAVEPOINT statements with examples in PL/SQL.
Explain COMMIT, ROLLBACK, and SAVEPOINT statements with examples in PL/SQL.
1212
04-Apr-2023
Updated on 04-Apr-2023
Krishnapriya Rajeev
04-Apr-2023In PL/SQL, COMMIT, ROLLBACK, and SAVEPOINT statements are TCL commands that are used to manage transactions. Transactions are a set of operations that are executed as a single unit of work. The COMMIT statement saves the changes made during a transaction, while the ROLLBACK statement undoes the changes made during a transaction. The SAVEPOINT statement creates a point within a transaction that can be used to roll back to a specific point in the transaction.
Given below is a demonstration of how to use each of these commands:
The COMMIT statement is used to permanently save the changes made during a transaction. For example, if we want to insert some data into a table and commit the changes, we would use the following syntax:
In this example, the INSERT statement adds a new record to the "employee" table. The COMMIT statement then saves the changes made by the INSERT statement.
The ROLLBACK statement is used to undo the changes made during a transaction. For example, if we want to update some data in a table but then decide to undo the changes, we would use the following syntax:
In this example, the UPDATE statement changes the salary of the employee with id 1. The ROLLBACK statement then undoes the changes made by the UPDATE statement.
SAVEPOINT statement:
The SAVEPOINT statement is used to create a point within a transaction that can be used to roll back to a specific point in the transaction. For example, if we want to update some data in a table and create a savepoint before doing so, we would use the following syntax:
In this example, the SAVEPOINT statement creates a savepoint called "sp1". The UPDATE statement changes the salary of the employee with id 1. The ROLLBACK TO statement then rolls back to the savepoint created by the SAVEPOINT statement, undoing the changes made by the UPDATE statement.