I'm currently learning PL/SQL and my task is to create a package which finds all German holidays and inserts them into one collection. My problem is that I have to insert them by looping over them and by extending the table. It's easy to extend the table manually but way too much work and my teacher won't allow the manual extends. I appreciate any solution or help.
CREATE OR REPLACE PACKAGE BODY pa_feiertage_mau IS
FUNCTION calc_holidays (in_year_c IN INTEGER)
RETURN DATE
IS
v_k INTEGER;
v_m INTEGER;
v_s INTEGER;
v_a INTEGER;
v_d INTEGER;
v_r INTEGER;
v_og INTEGER;
v_sz INTEGER;
v_oe INTEGER;
v_os INTEGER;
v_day INTEGER;
v_month INTEGER;
BEGIN
v_k := floor(in_year_c / 100);
v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25);
v_s := 2 - floor((3 * v_k + 3) / 4);
v_a := MOD(in_year_c, 19);
v_d := MOD((19 * v_a + v_m), 30);
v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11);
v_og := 21 + v_d - v_r;
v_sz := 7 - MOD((in_year_c + floor(in_year_c / 4) + v_s), 7);
v_oe := 7 - MOD(v_og - v_sz, 7);
v_os := v_og + v_oe;
IF (v_os <= 31) THEN
v_day := v_os;
v_month := 3;
ELSE
v_day := v_os - 31;
v_month := 4;
END IF;
RETURN TO_DATE(v_day || '.' || v_month || '.' || in_year_c, 'DD,MM,YYYY');
END calc_holidays;
FUNCTION get_holidays (in_year1 IN VARCHAR2)
RETURN Type_tab_feiertage
PIPELINED
IS
/*Static holidays */
v_year1 VARCHAR2(4) := in_year1;
v_neujahr VARCHAR2(10) := '01.01.';
v_3kings VARCHAR2(10) := '06.01.';
v_fraut VARCHAR2(10) := '08.03.';
v_arbeit VARCHAR2(10) := '01.05.';
v_himmel VARCHAR2(10) := '15.08.';
v_deu VARCHAR2(10) := '03.10.';
v_refo VARCHAR2(10) := '31.10.';
v_aller VARCHAR2(10) := '01.11.';
v_wh1 VARCHAR2(10) := '25.12.';
v_wh2 VARCHAR2(10) := '26.12.';
/*Changing holidays*/
d_ostern DATE := calc_holidays(TO_NUMBER(in_year1));
d_karf DATE := d_ostern - INTERVAL '2' DAY;
d_gruen DATE := d_ostern - INTERVAL '3' DAY;
d_osterm DATE := d_ostern + INTERVAL '1' DAY;
d_chimmel DATE := d_ostern + INTERVAL '39' DAY;
d_pfingsts DATE := d_ostern + INTERVAL '49' DAY;
d_pfingstm DATE := d_pfingsts + INTERVAL '1' DAY;
d_fronlei DATE := d_ostern + INTERVAL '60' DAY;
tab_feiertage type_tab_feiertage;
BEGIN
tab_feiertage := NEW type_tab_feiertage();
LOOP
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM := TO_DATE( v_neujahr || v_year1, 'DD.MM.YYYY');
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Neujahr';
END LOOP;
IF tab_feiertage.COUNT > 0 THEN
FOR i IN tab_feiertage.FIRST .. tab_feiertage.LAST
LOOP
PIPE ROW( tab_feiertage( i));
END LOOP;
--RETURN tab_feiertage;
END IF;
RETURN;
END get_holidays;
END pa_feiertage_mau;
To be honest it's hard to tell what your teacher is driving at. You have some fixed reference data keys (holiday names) and logic for deriving their reference values, and you need to put the keys and derived values in a collection. There is no magic way of doing this: each entry needs to be assigned manually.
Personally I would discard the local variables (v_%, d_%) and put that logic and the names in the collection populating code. The only variable I would keep is d_ostern, because you need that to anchor all the other moveable feasts. Note the change to how Pfingstm is derived.
d_ostern := calc_holidays(TO_NUMBER(in_year1));
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM := TO_DATE( '01.01.' || v_year1, 'DD.MM.YYYY');
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Neujahr';
...
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM := d_ostern;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Ostern';
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM := d_ostern - INTERVAL '2' DAY;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Karf';
...
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM := d_ostern + INTERVAL '50' DAY;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Pfingstm');
...
There is a way of populating the collection without using manual extension and explicit assignment, but it requires SQL. Here we calculate the value of Ostern using a WITH clause to call the calc_holidays() function, then write a massive UNION ALL query which selects the key-value pairs and stores them in the collection using BULK COLLECT:
select datum, feiertag
bulk collect into tab_feiertage
from (
/* calculate moveable feast */
with hol as (
select calc_holidays(TO_NUMBER(in_year1)) as ostern
from dual )
/* Static holidays */
select to_date('01.01.'||in_year1, 'DD.MM.YYYY') as datum, 'Neujahr' as feiertag from hol union all
select to_date('06.01.'||in_year1, 'DD.MM.YYYY') as datum, '3kings' as feiertag from hol union all
select to_date('08.03.'||in_year1, 'DD.MM.YYYY') as datum, 'Fraut' as feiertag from hol union all
select to_date('01.05.'||in_year1, 'DD.MM.YYYY') as datum, 'Arbeit' as feiertag from hol union all
select to_date('15.08.'||in_year1, 'DD.MM.YYYY') as datum, 'Himmel' as feiertag from hol union all
select to_date('03.10.'||in_year1, 'DD.MM.YYYY') as datum, 'Deu' as feiertag from hol union all
select to_date('31.10.'||in_year1, 'DD.MM.YYYY') as datum, 'Refo' as feiertag from hol union all
select to_date('01.11.'||in_year1, 'DD.MM.YYYY') as datum, 'Aller' as feiertag from hol union all
select to_date('25.12.'||in_year1, 'DD.MM.YYYY') as datum, 'Wh1' as feiertag from hol union all
select to_date('26.12.'||in_year1, 'DD.MM.YYYY') as datum, 'Wh2' as feiertag from hol union all
/* Changing holidays */
select ostern , 'Ostern' as feiertag from hol union all
select ostern - INTERVAL '2' DAY as datum , 'Karf' as feiertag from hol union all
select ostern - INTERVAL '3' DAY as datum , 'Gruen' as feiertag from hol union all
select ostern + INTERVAL '1' DAY as datum , 'Osterm' as feiertag from hol union all
select ostern + INTERVAL '39' DAY as datum , 'Chimmel' as feiertag from hol union all
select ostern + INTERVAL '49' DAY as datum , 'Pfingsts' as feiertag from hol union all
select ostern + INTERVAL '50' DAY as datum , 'Pfingstm' as feiertag from hol union all
select ostern + INTERVAL '60' DAY as datum , 'Fronlei' as feiertag from hol
);
This may not be what your teacher expects but it works. There is a demo on db<>fiddle here