
--------------------------------------------------------
--  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;

/