Differences

This shows you the differences between two versions of the page.

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

dokuwiki

User Tools

Site Tools

Differences

Page Tools