--------------------------------------------------------
-- DDL for Table PERSONS_JN
--------------------------------------------------------
CREATE TABLE "HR"."PERSONS_JN"
( "PJN_ID" NUMBER,
"PJN_PER_NAME" VARCHAR2(20 BYTE),
"PJN_PER_BIRTHDAY" DATE,
"PJN_PER_ID" NUMBER,
"PJN_PER_GEN_NAME" VARCHAR2(20 BYTE),
"PJN_VALID_FROM" DATE,
"PJN_VALID_TO" DATE,
"PJN_ACTION" VARCHAR2(20 BYTE),
"PJN_EDUCATIONS" "HR"."EDUCATION" ,
"PJN_TRG_TABLE" VARCHAR2(20 BYTE)
)
NESTED TABLE "PJN_EDUCATIONS" STORE AS "PJN_NT_EDUCATIONS"
CREATE UNIQUE INDEX "HR"."SYS_C0017903" ON "HR"."PERSONS_JN" ("SYS_NC0000900010$")
ALTER TABLE "HR"."PERSONS_JN" ADD UNIQUE ("PJN_EDUCATIONS");
--------------------------------------------------------
-- DDL for Package PCK_PERSON
--------------------------------------------------------
CREATE OR REPLACE PACKAGE "HR"."PCK_PERSON" AS
/* TODO enter package declarations (types, exceptions, methods etc) here */
procedure next;
procedure refresh_cache;
procedure get_cache_data (
per_name in varchar2
);
function get_data (v_per_name varchar2)
return persons%rowtype
result_cache;
procedure add_education (
P_PER_NAME IN VARCHAR2
, P_NEW_EDUCATION IN VARCHAR2
);
procedure add_education (
P_PER_ID IN number,
P_NEW_EDUCATION IN VARCHAR2
);
procedure print (v_per_name varchar2);
procedure write_jn(
p_action in varchar2,
p_table in varchar2,
p_per_rec in persons%rowtype default null,
p_per_id in number,
p_edu_name in varchar2 default null
);
END PCK_PERSON;
/
--------------------------------------------------------
-- DDL for Package Body PCK_PERSON
--------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY "HR"."PCK_PERSON" AS
cursor c_list is (select * from persons);
type person_table_type is table of persons%rowtype
index by varchar2(20);
v_per person_table_type;
v_last_refresh date := sysdate;
procedure get_cache_data (
per_name in varchar2) as
begin
if v_last_refresh + to_dsinterval('0 00:05:00') < sysdate then
refresh_cache;
end if;
dbms_output.put_line(v_per(per_name).per_birthday);
end;
function get_data (v_per_name varchar2)
return persons%rowtype
result_cache relies_on (persons) as
v_per_rec persons%rowtype;
begin
select * into v_per_rec from persons
where per_name = v_per_name;
return v_per_rec;
end;
procedure print (v_per_name varchar2) as
v_per_rec persons%rowtype;
begin
v_per_rec := get_data(v_per_name);
dbms_output.put_line(v_per_rec.per_birthday);
end;
procedure next as
v_person persons%rowtype;
begin
fetch c_list into v_person;
if c_list%notfound then
close c_list;
open c_list;
fetch c_list into v_person;
end if;
dbms_output.put_line(v_person.per_name);
end;
procedure add_education (
P_PER_ID IN number,
P_NEW_EDUCATION IN VARCHAR2
) as
v_edu_id number;
v_per_id number;
e_integrity_violated exception;
pragma exception_init
(e_integrity_violated, -2291);
BEGIN
/* begin
select per_id into v_per_id from persons
where per_id = p_per_id;
exception when no_data_found then
raise_application_error(-20028, 'no person found');
end;
*/
select edu_id into v_edu_id from educations
where edu_name = p_new_education;
insert into person_x_education (
exp_id,
exp_per_id,
exp_edu_id
) values (
wifi_seq.nextval,
p_per_id,
v_edu_id
);
exception
when no_data_found then
raise_application_error(-20015, 'no such education');
when e_integrity_violated then
raise pck_declarations.e_no_person_found;
when dup_val_on_index then
raise_application_error(-20031, 'education already assigned');
END add_education;
procedure add_education (
P_PER_NAME IN VARCHAR2
, P_NEW_EDUCATION IN VARCHAR2
) AS
v_per_id number;
BEGIN
select per_id into v_per_id
from persons
where per_name = p_per_name;
add_education(v_per_id, P_NEW_EDUCATION);
exception when no_data_found then
raise pck_declarations.e_no_person_found;
END add_education;
procedure refresh_cache as
begin
v_per.delete;
for r in (select * from persons) loop
v_per(r.per_name) := r;
end loop;
v_last_refresh := sysdate;
end;
procedure write_jn(
p_action in varchar2,
p_table in varchar2,
p_per_rec in persons%rowtype default null,
p_per_id in number,
p_edu_name in varchar2 default null
)
as
v_date date := sysdate;
v_educations education := education();
v_del_edu education := education();
cursor c_per_edu(cp_per_id in number) is
select edu_name from educations join person_x_education
on edu_id = exp_edu_id
where exp_per_id = cp_per_id;
begin
if p_table != 'person_x_education' then
open c_per_edu(p_per_id);
fetch c_per_edu bulk collect into v_educations;
close c_per_edu;
end if;
if p_edu_name is not null then
select pjn_educations into v_educations from persons_jn
where pjn_per_id = p_per_id
and pjn_id =
(select max(pjn_id) from persons_jn where pjn_per_id = p_per_id);
if p_action = 'I' then
v_educations.extend;
v_educations(v_educations.last) := p_edu_name;
elsif p_action = 'D' then
v_del_edu.extend;
v_del_edu(1) := p_edu_name;
v_educations := v_educations multiset except v_del_edu;
end if;
end if;
if p_action in ('D', 'U') then
update persons_jn set pjn_valid_to = v_date
where pjn_per_id = p_per_id and
pjn_valid_to is null;
/*if SQL%rowcount != 1 then
raise_application_error(-20666, 'something very wrong');
end if;*/
end if;
insert into persons_jn (
PJN_ID,
PJN_PER_NAME,
PJN_PER_BIRTHDAY,
PJN_PER_ID,
PJN_PER_GEN_NAME,
PJN_VALID_FROM,
PJN_VALID_TO,
PJN_ACTION,
PJN_EDUCATIONS,
PJN_TRG_TABLE
) values (
wifi_seq.nextval,
p_per_rec.per_name,
p_per_rec.per_birthday,
p_per_id,
(select gen_name from gender where gen_id = p_per_rec.per_gen_id),
v_date,
null,
p_action,
v_educations,
p_table
);
end;
begin
open c_list;
refresh_cache;
END PCK_PERSON;
/