|
SQL>
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>
SQL> create table employee_type (
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 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> set serveroutput on size 1000000;
SQL>
SQL> declare
2 v_first_name employee.first_name%TYPE;
3 v_middle_name employee.middle_name%TYPE;
4 v_last_name employee.last_name%TYPE;
5 v_name employee.name%TYPE;
6 d_birth_date employee.birth_date%TYPE;
7 n_count number;
8
9 begin
10 v_first_name := 'JOHN';
11 v_middle_name := 'J.';
12 v_last_name := 'DOE';
13 v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
14 d_birth_date := to_date('19800101', 'YYYYMMDD');
15
16 begin
17 insert into employee (
18 id,
19 employee_type_id,
20 external_id,
21 first_name,
22 middle_name,
23 last_name,
24 name,
25 birth_date,
26 gender_id )
27 select 12,
28 myCursor.id,
29 lpad('12', 9, '0'),
30 v_first_name,
31 v_middle_name,
32 v_last_name,
33 v_name,
34 d_birth_date,
35 c2.id
36 from employee_type myCursor,
37 gender c2
38 where myCursor.code = 'C'
39 and c2.code = 'M'
40 and not exists (
41 select 1
42 from employee x
43 where x.name = v_name
44 and x.birth_date = d_birth_date
45 and x.gender_id = c2.id );
46
47 n_count := sql%rowcount;
48 exception
49 when OTHERS then
50 raise_application_error(-20006, SQLERRM||' on insert employee');
51 end;
52
53 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||' row(s) inserted.');
54 end;
55 /
1 row(s) inserted.
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>
SQL>
|