TCL Commansds

 

What is Transactions commands in Oracle PL/SQL ?

Since DML commands modify data, they can be rolled back or committed using transaction control commands:

Ø  COMMIT - Saves all changes made by DML commands permanently

Ø  ROLLBACK- Reverts all uncommitted changes to the last commit.

Ø  SAVEPOINT- Creates a checkpoint to which changes can be rolled back selectively

Note : Changes will not be saved if rollback is executed before commit.

 

SET TRANSACTION READ WRITE;

INSERT INTO employees (emp_id, emp_name, salary) VALUES (101, 'Alice', 60000);

SAVEPOINT emp1_added;

INSERT INTO employees (emp_id, emp_name, salary) VALUES (102, 'Bob', 55000);

SAVEPOINT emp2_added;

INSERT INTO employees (emp_id, emp_name, salary) VALUES (103, 'Charlie', 70000);

SAVEPOINT emp3_added;

ROLLBACK TO SAVEPOINT emp2_added;

COMMIT;

1. ROLLBACK TO SAVEPOINT emp2_added removes third record while keeping Alice and Bob.
2. COMMIT ensures First two records remain permanently in the database.