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 or replace PACKAGE genderS as
2 PROCEDURE get_code_descr(
3 ain_id in gender.id%TYPE,
4 aov_code out gender.code%TYPE,
5 aov_description out gender.description%TYPE);
6
7 PROCEDURE get_code_id_descr(
8 aiov_code in out gender.code%TYPE,
9 aon_id out gender.id%TYPE,
10 aov_description out gender.description%TYPE,
11 aid_on in gender.active_date%TYPE);
12
13 PROCEDURE get_code_id_descr(
14 aiov_code in out gender.code%TYPE,
15 aon_id out gender.id%TYPE,
16 aov_description out gender.description%TYPE);
17
18 FUNCTION get_id
19 return gender.id%TYPE;
20
21 FUNCTION get_id(
22 aiv_code in gender.code%TYPE)
23 return gender.id%TYPE;
24
25 end genderS;
26 /
Package created.
SQL>
SQL>
SQL> create or replace PACKAGE BODY genderS as
2 FUNCTION get_id
3 return gender.id%TYPE is
4 n_id gender.id%TYPE;
5 begin
6 select 1 into n_id from SYS.DUAL;
7 return n_id;
8 end get_id;
9
10
11 FUNCTION get_id(aiv_code in gender.code%TYPE )
12 return gender.id%TYPE is
13
14 n_id gender.id%TYPE;
15
16 begin
17 select id into n_id from gender where code = aiv_code;
18
19 return n_id;
20 end get_id;
21
22 PROCEDURE get_code_descr(
23 ain_id in gender.id%TYPE,
24 aov_code out gender.code%TYPE,
25 aov_description out gender.description%TYPE ) is
26
27 begin
28 select code,description into aov_code,aov_description
29 from gender
30 where id = ain_id;
31 end get_code_descr;
32
33
34 PROCEDURE get_code_id_descr(
35 aiov_code in out gender.code%TYPE,
36 aon_id out gender.id%TYPE,
37 aov_description out gender.description%TYPE,
38 aid_on in gender.active_date%TYPE ) is
39
40 v_code gender.code%TYPE;
41
42 begin
43 select id,description into aon_id,aov_description
44 from gender
45 where code = aiov_code
46 and aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
47 exception
48 when NO_DATA_FOUND then
49 select id, code,description
50 into aon_id,v_code,aov_description
51 from gender
52 where code like aiov_code||'%'
53 and aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
54
55 aiov_code := v_code;
56 end get_code_id_descr;
57
58
59 PROCEDURE get_code_id_descr(
60 aiov_code in out gender.code%TYPE,
61 aon_id out gender.id%TYPE,
62 aov_description out gender.description%TYPE ) is
63
64 begin
65 get_code_id_descr(
66 aiov_code,
67 aon_id,
68 aov_description,
69 SYSDATE );
70 end get_code_id_descr;
71 end genderS;
72 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table gender;
Table dropped.
SQL>