This shows you the differences between two versions of the page.
— |
kurs:denormalisieren_im_trigger_fuer_person_journal [2015/04/23 09:39] (current) mh created |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | -------------------------------------------------------- | ||
+ | -- 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"); | ||
+ | |||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | -------------------------------------------------------- | ||
+ | -- 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; | ||
+ | |||
+ | / | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | -------------------------------------------------------- | ||
+ | -- 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; | ||
+ | |||
+ | / | ||
+ | </code> |