With SAVEPOINT, only part of transaction can be undone.

In PL/SQL, the SAVEPOINT statement is used to set a point within the current transaction to which you can later roll back. This allows you to create a named point in your transaction where you can later return if needed, rather than rolling back the entire transaction.

The basic syntax for creating a SAVEPOINT is as follows:

SAVEPOINT savepoint_name;

Here, savepoint_name is the name you give to the savepoint. Once a savepoint is established, you can perform various SQL statements, and if you encounter an issue or want to undo the changes up to the savepoint, you can use the ROLLBACK TO statement.

ROLLBACK TO savepoint_name;

This command rolls back all changes made in the transaction to the specified savepoint, but it does not end the transaction. You can continue making changes after rolling back to a savepoint.

Here’s a simple example:

BEGIN

-- Start of the transaction

SAVEPOINT start_point;

— Some SQL statements
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
DELETE FROM employees WHERE department_id = 20;— If an issue occurs, roll back to the savepoint
ROLLBACK TO start_point;

— You can continue with the transaction after rolling back
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 30;

— End the transaction
COMMIT;
END;

In this example, if there’s an issue after the first set of SQL statements, the transaction can be rolled back to the start_point savepoint, and changes made after that point will be undone. The COMMIT statement at the end finalizes the transaction if everything is successful.