Explain the Commit statement.

by

Following conditions are true for the Commit statement:

  • Other users can see the data changes made by the transaction.
  • The locks acquired by the transaction are released.
  • The work done by the transaction becomes permanent.

In PL/SQL (Procedural Language/Structured Query Language), the COMMIT statement is used to make the changes performed in the current transaction permanent.

When you execute SQL statements in a PL/SQL block, those statements are considered part of a transaction. By default, changes made within a transaction are not permanent until a COMMIT statement is encountered. The COMMIT statement marks the end of the transaction and makes all changes made within the transaction permanent.

Here’s a brief explanation of how the COMMIT statement works:

  1. Start of Transaction:
    • When a PL/SQL block begins, a transaction implicitly starts.
  2. Execution of SQL Statements:
    • Within the transaction, you can execute various SQL statements (e.g., INSERT, UPDATE, DELETE) that modify the database data.
  3. Committing the Transaction:
    • Once you reach a point in your PL/SQL block where you want to make all the changes permanent, you use the COMMIT statement.
    • The COMMIT statement ends the transaction and commits (saves) all the changes made within the transaction to the database.

Example:

BEGIN
-- SQL statements modifying data
INSERT INTO employees VALUES (101, 'John Doe', 'Manager');
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';— Committing the transaction to make changes permanent
COMMIT;
END;

  1. Rollback (Optional):
    • If an error occurs or if you want to discard the changes made during the transaction, you can use the ROLLBACK statement instead of COMMIT. This will undo all the changes made within the transaction.
BEGIN
-- SQL statements modifying data
INSERT INTO employees VALUES (101, 'John Doe', 'Manager');
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';— Rolling back the transaction to discard changes
ROLLBACK;
END;

It’s important to note that committing a transaction affects all the changes made within that transaction, and those changes become permanent and visible to other users. Therefore, you should use the COMMIT statement judiciously based on your business logic and transaction requirements.