Commit and rollback in PL/SQL
In PL/SQL, the COMMIT and ROLLBACK statements are used to manage transactions. A transaction is a sequence of one or more SQL statements that are treated as a single unit of work. The COMMIT statement is used to make permanent changes to the database, while the ROLLBACK statement is used to undo changes and return the database to its previous state.
Here’s how you can use the COMMIT and ROLLBACK statements in PL/SQL:
DECLARE
-- declare variables here
BEGIN
-- perform database operations here
INSERT INTO employees (id, name, department)
VALUES (1, 'John Doe', 'Sales');
INSERT INTO departments (id, name)
VALUES (1, 'Sales');
-- commit the changes
COMMIT;
EXCEPTION
-- handle any exceptions here
WHEN OTHERS THEN
-- rollback the transaction
ROLLBACK;
END;
In this example, we are performing two database operations (inserting a record into the “employees” table and the “departments” table). After the operations are complete, we use the COMMIT statement to commit the changes to the database. If an exception is raised during the execution of the code, the ROLLBACK statement is used to undo any changes that were made to the database.
The COMMIT statement has the following syntax:
COMMIT;
This statement is used to commit the current transaction and make all changes permanent.
The ROLLBACK statement has the following syntax:
ROLLBACK;
This statement is used to undo all changes made in the current transaction and return the database to its previous state.
It’s important to note that transactions are automatically committed when a PL/SQL block is completed, unless a ROLLBACK statement has been executed. Therefore, it’s a good practice to always explicitly use the COMMIT and ROLLBACK statements in your PL/SQL code to ensure data integrity and consistency.
Apply for PL/SQL Certification
https://www.vskills.in/certification/certified-pl-sql-developer
