|
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> 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 n_count number := 0;
3
4 FUNCTION add_worker(
5 aiv_first_name employee.first_name%TYPE,
6 aiv_middle_name employee.middle_name%TYPE,
7 aiv_last_name employee.last_name%TYPE,
8 aid_birth_date employee.birth_date%TYPE,
9 aiv_gender_code gender.code%TYPE,
10 aiv_employee_type_code employee_type.code%TYPE)
11 return number is
12
13 v_name employee.name%TYPE;
14
15 begin
16 v_name := rtrim(aiv_last_name||', '||aiv_first_name||' '||aiv_middle_name);
17 begin
18 insert into employee (
19 id,
20 employee_type_id,
21 external_id,
22 first_name,
23 middle_name,
24 last_name,
25 name,
26 birth_date,
27 gender_id )
28 select 1,
29 myCursor.id,
30 lpad(to_char(1), 9, '0'),
31 aiv_first_name,
32 aiv_middle_name,
33 aiv_last_name,
34 v_name,
35 aid_birth_date,
36 c2.id
37 from employee_type myCursor,
38 gender c2
39 where myCursor.code = aiv_employee_type_code
40 and c2.code = aiv_gender_code
41 and not exists (
42 select 1
43 from employee x
44 where x.name = v_name
45 and x.birth_date = aid_birth_date
46 and x.gender_id = c2.id );
47
48 return sql%rowcount;
49 exception
50 when OTHERS then
51 raise_application_error(-20001, SQLERRM||' on insert employee'||' in add_worker');
52 end;
53 end add_worker;
54
55 begin
56 n_count := n_count + add_worker('JOHN', 'J.', 'DOE', to_date('19800101', 'YYYYMMDD'), 'M', 'C');
57 n_count := n_count + add_worker('JANE', 'J.', 'DOE', to_date('19800101', 'YYYYMMDD'), 'F', 'E');
58 n_count := n_count + add_worker('JOHNNY', 'E.', 'DOE', to_date('19980101', 'YYYYMMDD'), 'M', 'E');
59 n_count := n_count + add_worker('JANIE', 'E.', 'DOE', to_date('19980101', 'YYYYMMDD'), 'F', 'E');
60 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||' row(s) inserted.');
61 end;
62 /
4 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>
|