PL SQL Statements Oracle PLSQL Tutorial

SQL>
SQL> create table employee (
  2  id                  number,
  3  employee_type_id    number,
  4  external_id         varchar2(30),
  5  first_name          varchar2(30),
  6  middle_name         varchar2(30),
  7  last_name           varchar2(30),
  8  name                varchar2(100),
  9  birth_date          date,
 10  gender_id           number);
Table created.
SQL>
SQL> create table gender (
  2  id                  number,
  3  code                varchar2(30),
  4  description         varchar2(80),
  5  active_date         date        default SYSDATE  not null,
  6  inactive_date       date );
Table created.
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, 'M', 'Male' );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' );
1 row created.
SQL>
SQL> create table employee_type (
  2  id                             number                         not null,
  3  code                           varchar2(30)                   not null,
  4  description                    varchar2(80)                   not null,
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,'C','Contractor' );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,'E','Employee' );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,'U','Unknown' );
1 row created.
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2      n_id                employee.id%TYPE;
  3      n_employee_type_id  employee.employee_type_id%TYPE;
  4      v_external_id       employee.external_id%TYPE;
  5      v_first_name        employee.first_name%TYPE;
  6      v_middle_name       employee.middle_name%TYPE;
  7      v_last_name         employee.last_name%TYPE;
  8      v_name              employee.name%TYPE;
  9      d_birth_date        employee.birth_date%TYPE;
 10      n_gender_id         employee.gender_id%TYPE;
 11
 12      n_inserted          number := 0;
 13      n_updated           number := 0;
 14
 15  begin
 16    v_first_name  := 'JOHN';
 17    v_middle_name := 'J.';
 18    v_last_name   := 'DOE';
 19    v_name        := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
 20    d_birth_date  := to_date('19800101', 'YYYYMMDD');
 21
 22    begin
 23      select id into n_employee_type_id from employee_type where  code = 'C';
 24    exception
 25      when OTHERS then
 26        raise_application_error(-20002, SQLERRM||' on select employee_type');
 27    end;
 28
 29    begin
 30      select id into n_gender_id from gender where code = 'M';
 31    exception
 32      when OTHERS then
 33        raise_application_error(-20004, SQLERRM||' on select gender');
 34    end;
 35
 36    begin
 37      select id into n_id from employee
 38      where  name       = v_name
 39      and    birth_date = d_birth_date
 40      and    gender_id  = n_gender_id;
 41    exception
 42      when NO_DATA_FOUND then
 43        n_id := NULL;
 44      when OTHERS then
 45        raise_application_error(-20003, SQLERRM||' on select employee_T');
 46    end;
 47
 48    if n_id is NULL then
 49      begin
 50        select 12 into n_id from SYS.DUAL;
 51      exception
 52        when OTHERS then
 53          raise_application_error(-20004, SQLERRM||' on select 12');
 54      end;
 55
 56      begin
 57        select lpad(to_char(12), 9, '0') into v_external_id from SYS.DUAL;
 58      exception
 59        when OTHERS then
 60          raise_application_error(-20005, SQLERRM||' on select 12');
 61      end;
 62
 63      begin
 64        insert into employee (
 65               id,
 66               employee_type_id,
 67               external_id,
 68               first_name,
 69               middle_name,
 70               last_name,
 71               name,
 72               birth_date,
 73               gender_id )
 74        values (
 75               n_id,
 76               n_employee_type_id,
 77               v_external_id,
 78               v_first_name,
 79               v_middle_name,
 80               v_last_name,
 81               v_name,
 82               d_birth_date,
 83               n_gender_id );
 84
 85        n_inserted := sql%rowcount;
 86      exception
 87        when OTHERS then
 88          raise_application_error(-20006, SQLERRM||' on insert employee');
 89      end;
 90    else
 91      begin
 92        update employee
 93        set    employee_type_id  = n_employee_type_id
 94        where  id              = n_id;
 95
 96        n_updated := sql%rowcount;
 97      exception
 98        when OTHERS then
 99          raise_application_error(-20007, SQLERRM||' on update employee');
100      end;
101    end if;
102
103    DBMS_OUTPUT.PUT_LINE(to_char(n_inserted)||' row(s) inserted.');
104    DBMS_OUTPUT.PUT_LINE(to_char(n_updated)||' row(s) updated.');
105  end;
106  /
1 row(s) inserted.
0 row(s) updated.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>