Oracle PL/SQL - PL SQL SQL Statement ROLLBACK Statement

Introduction

The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction.

The following code inserts information about an employee into three different tables.

If an INSERT statement tries to store a duplicate employee number, PL/SQL raises the predefined exception DUP_VAL_ON_INDEX.

To ensure that changes to all three tables are undone, the exception handler runs a ROLLBACK.

Demo

SQL>
SQL> drop table emp;

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

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> DROP TABLE emp_name;

Table dropped.

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

Index created.

SQL>
SQL> DROP TABLE emp_sal;

Table dropped.

SQL> CREATE TABLE emp_sal AS
  2    SELECT empid, salary
  3    FROM emp;
SQL>
SQL> CREATE UNIQUE INDEX empsal_ix
  2  ON emp_sal (empid);

Index created.

SQL>
SQL> DROP TABLE emp_job;

Table dropped.

SQL> CREATE TABLE emp_job AS
  2    SELECT empid, job_id
  3    FROM emp;
SQL>
SQL> CREATE UNIQUE INDEX empjobid_ix
  2  ON emp_job (empid);

Index created.

SQL>
SQL>
SQL> DECLARE
  2    emp_id        NUMBER(6);
  3    emp_lastname  VARCHAR2(25);
  4    emp_salary    NUMBER(8,2);
  5    emp_jobid     VARCHAR2(10);
  6  BEGIN
  7    SELECT empid, last_name, salary, job_id
  8    INTO emp_id, emp_lastname, emp_salary, emp_jobid
  9    FROM emp
 10    WHERE empid = 120;
 11
 12    INSERT INTO emp_name (empid, last_name)
 13    VALUES (emp_id, emp_lastname);
 14
 15    INSERT INTO emp_sal (empid, salary)
 16    VALUES (emp_id, emp_salary);
 17
 18    INSERT INTO emp_job (empid, job_id)
 19    VALUES (emp_id, emp_jobid);
 20
 21  EXCEPTION
 22    WHEN DUP_VAL_ON_INDEX THEN
 23      ROLLBACK;
 24      DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
 25  END;
 26  /

SQL>

Oracle PL/SQL - PL SQL SQL Statement ROLLBACK Statement

Introduction

Demo

Related Topics