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> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 d_birth_date employee.birth_date%TYPE;
4 n_gender_id employee.gender_id%TYPE;
5 n_selected number := -1;
6 n_id employee.id%TYPE;
7 v_first_name employee.first_name%TYPE;
8 v_last_name employee.last_name%TYPE;
9 v_middle_name employee.middle_name%TYPE;
10 v_name employee.name%TYPE;
11
12 begin
13 v_first_name := 'JOHN';
14 v_middle_name := 'J.';
15 v_last_name := 'DOUGH';
16 v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
17 d_birth_date := to_date('19800101', 'YYYYMMDD');
18
19 begin
20 select id into n_gender_id from gender where code = 'M';
21 exception
22 when OTHERS then
23 raise_application_error(-20001, SQLERRM||' on select gender');
24 end;
25
26 begin
27 select id
28 into n_id
29 from employee
30 where name = v_name
31 and birth_date = d_birth_date
32 and gender_id = n_gender_id;
33
34 n_selected := sql%rowcount;
35 exception
36 when NO_DATA_FOUND then
37 n_selected := sql%rowcount;
38 DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
39 when OTHERS then
40 raise_application_error(-20002, SQLERRM||' on select employee');
41 end;
42
43 DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||' row(s) selected.');
44 end;
45 /
Caught raised exception NO_DATA_FOUND
0 row(s) selected.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>