Live ORACLE

DROP TABLE emp_name CASCADE CONSTRAINTS PURGE;


DROP TABLE emp_sal CASCADE CONSTRAINTS PURGE;


DROP TABLE emp_job CASCADE CONSTRAINTS PURGE;


DROP INDEX empname_ix;


DROP INDEX empsal_ix;


DROP INDEX empjobid_ix;


CREATE TABLE emp_name
AS
SELECT employee_id,
       last_name
FROM   employees;


CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id);


CREATE TABLE emp_sal
AS
SELECT employee_id,
       salary
FROM   employees;


CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id);


CREATE TABLE emp_job
AS
SELECT employee_id,
       job_id
FROM   employees;


CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id);


DECLARE

emp_id        NUMBER(6);

 emp_lastname  VARCHAR2(25);

 emp_salary    NUMBER(8,2);

 emp_jobid     VARCHAR2(10);

BEGIN

SELECT employee_id,

       last_name,

       salary,

       job_id

INTO   emp_id,emp_lastname,emp_salary,emp_jobid

FROM   employees

WHERE  employee_id = 120;



INSERT INTO emp_name

VALUES     (emp_id,

            emp_lastname);



INSERT INTO emp_sal

VALUES     (emp_id,

            emp_salary);



INSERT INTO emp_job

VALUES     (emp_id,

            emp_jobid);

EXCEPTION

WHEN dup_val_on_index THEN

  ROLLBACK;



  dbms_output.Put_line('Inserts have been rolled back');

END;

/

Inserts have been rolled back

Using ROLLBACK