PL/SQL by Example / Savepoints

Savepoints are a mechanism within a PL/SQL block that allows you to divide the transaction being executed into partial units, or milestones. You can then easily return to these milestones without losing the entire transaction.

First, let’s define a helper function that returns the salary of a specific employee.

CREATE OR REPLACE FUNCTION sf_ReturnSalary(
    p_Employee_ID NUMBER
) RETURN NUMBER
IS
  l_Salary NUMBER;
BEGIN
  SELECT salary
    INTO l_Salary
    FROM employees
    WHERE employee_id=p_Employee_ID
  ;
  RETURN l_Salary;
END;
/

And one more that updates the salary.

CREATE OR REPLACE PROCEDURE sp_SetSalary(
    p_Employee_ID NUMBER
  , p_Salary NUMBER
)
IS
BEGIN
  UPDATE employees
    SET salary=p_Salary
    WHERE (employee_id=p_Employee_ID)
  ;
END;
/

In the first step, we set the initial salary to 24000, which starts the transaction. We don’t want to lose this value, no matter what happens later in the code. Therefore, we create a recovery point and name it outer_savepoint.

The second step sets the salary again, this time to 25500.

For illustration, an anonymous block follows, which creates “its own” milestone named inner_savepoint before making changes, sets the salary to 26000, and raises an exception. This is caught, and a partial rollback of changes is performed to the state before the anonymous block began.

In the final phase, an exception is raised again, and a rollback to the savepoint occurs, which also nullifies the salary update from the second step.

DECLARE
  L_EMPLOYEE_ID CONSTANT NUMBER:=100; -- test employee
  CUSTOM_EXCEPTION EXCEPTION;
BEGIN
  sp_SetSalary(L_EMPLOYEE_ID, 24000);
  dbms_output.put_line(sf_ReturnSalary(L_EMPLOYEE_ID)); -- 24000

  SAVEPOINT outer_savepoint;
  sp_SetSalary(L_EMPLOYEE_ID, 25500);
  dbms_output.put_line(sf_ReturnSalary(L_EMPLOYEE_ID)); -- 25500

  /* START OF INNER BLOCK */
  BEGIN
    SAVEPOINT inner_savepoint;
    sp_SetSalary(L_EMPLOYEE_ID, 26000);
    dbms_output.put_line(sf_ReturnSalary(L_EMPLOYEE_ID)); -- 26000
    RAISE CUSTOM_EXCEPTION;
  EXCEPTION
    WHEN CUSTOM_EXCEPTION THEN
      ROLLBACK TO SAVEPOINT inner_savepoint;
  END;
  /* END OF INNER BLOCK */

  dbms_output.put_line(sf_ReturnSalary(L_EMPLOYEE_ID)); -- 25500
  RAISE CUSTOM_EXCEPTION;
EXCEPTION
  WHEN CUSTOM_EXCEPTION THEN
    ROLLBACK TO SAVEPOINT outer_savepoint;
    dbms_output.put_line(sf_ReturnSalary(L_EMPLOYEE_ID)); -- 24000
END;
/