Oracle PL/SQL - Reusing SAVEPOINT with ROLLBACK

Introduction

Savepoint names are undeclared identifiers.

Reusing a savepoint name in a transaction moves the savepoint from its old position to the current point in the transaction.

A rollback to the savepoint affects only the current part of the transaction.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from ww w .  ja  v  a 2  s .  c  o  m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DROP TABLE emp_name;

Table dropped.

SQL> CREATE TABLE emp_name AS
  2    SELECT empid, last_name, salary
  3    FROM emp;
SQL>
SQL> CREATE UNIQUE INDEX empname_ix
  2  ON emp_name (empid);

Index created.

SQL>
SQL> DECLARE
  2    emp_id        emp.empid%TYPE;
  3    emp_lastname  emp.last_name%TYPE;
  4    emp_salary    emp.salary%TYPE;
  5
  6  BEGIN
  7    SELECT empid, last_name, salary
  8    INTO emp_id, emp_lastname, emp_salary
  9    FROM emp
 10    WHERE empid = 100;
 11
 12    SAVEPOINT my_savepoint;
 13
 14    UPDATE emp_name
 15    SET salary = salary * 1.1
 16    WHERE empid = emp_id;
 17
 18    DELETE FROM emp_name
 19    WHERE empid = 130;
 20
 21    SAVEPOINT my_savepoint;
 22
 23    INSERT INTO emp_name (empid, last_name, salary)
 24    VALUES (emp_id, emp_lastname, emp_salary);
 25
 26  EXCEPTION
 27    WHEN DUP_VAL_ON_INDEX THEN
 28      ROLLBACK TO my_savepoint;
 29      DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
 30  END;
 31  /
Transaction rolled back.

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL - Reusing SAVEPOINT with ROLLBACK

Introduction

Demo

Related Topic