PL/SQL anonymous block voor vullen tijd dimensie

Met deze code kan de tijdsdimensie in een datawarehouse gevuld worden.
Aanpasbaar zijn:
De doeltabelnaam: DIM_TIME
De sequence: T_KEY
Het beginjaar: v_year (2010)
Het eindjaar: v_thisyear

Voorbeeld van een doeltabel:

create table "DIM_TIME"(
T_KEY NUMBER NOT NULL,
DATE_VALUE DATE NULL,
MONTH NUMBER NULL,
QUARTER NUMBER NULL,
YEAR NUMBER NULL);

CREATE SEQUENCE T_KEY
NOCACHE
ORDER ;


DECLARE
v_thisyear NUMBER;
v_year NUMBER:=2010;
l_start_date date;
l_end_date date;
l_month number:=1;
l_quarter number;

BEGIN
EXECUTE IMMEDIATE 'SELECT TO_NUMBER(extract(year from sysdate)) AS thisyear from dual' INTO v_thisyear;
WHILE v_year <= v_thisyear LOOP
-- execute
l_start_date:=to_date('01-jan-'||to_char(v_year),'DD-MM-YYYY');
l_end_date:=last_day(l_start_date);

while l_month<=12 loop
if l_month!=1 then
l_start_date:=l_start_date;
l_end_date:=last_day(l_start_date);
end if;
if l_month in (1,2,3) then
l_quarter:=1;
elsif l_month in (4,5,6) then
l_quarter:=2;
elsif l_month in(7,8,9) then
l_quarter:=3;
else
l_quarter:=4;
end if;

while l_start_date<=l_end_date loop
insert into DIM_TIME values(T_KEY.nextval,l_start_date,l_month,l_quarter,v_year);
l_start_date:=l_start_date+1;
end loop;
l_month:=l_month+1;
end loop;
-- iterator
v_year:=v_year+1;
l_month:=1;
END LOOP; -- while current year < this year loop
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('FILL_DIM_TIME says: no_data_found');
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.