SQL>
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>
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>
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>
SQL>
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>