to_char
, wie das folgende Beispiel zeigt.Zunächst wird der Objekt-Typ erstellt:
create type time_dim_t as object ( v_day date, v_day_name varchar2(30), v_day_of_week integer, v_day_of_month integer, v_day_of_year integer, v_week integer, v_week_start date, v_week_end date, v_iso_week integer, v_iso_week_start date, v_iso_week_end date, v_month integer, v_month_name varchar2(30), v_month_start date, v_month_end date, v_month_days integer, v_quarter integer, v_quarter_name varchar(2), v_quarter_start date, v_quarter_end date, v_quarter_days integer, v_year integer, v_year_start date, v_year_end date, v_year_days integer, v_is_leap_year varchar2(1) ); /Hinzu kommt der Table-Typ:
create type time_dim_tab as table of time_dim_t; /Damit sind alle Vorbereitungen getroffen, um die Table-Function zu erstellen:
create or replace function time_dim ( p_year_start_in in integer, p_year_end_in in integer ) return time_dim_tab pipelined as v_day date; v_day_name varchar2(30); v_day_of_week integer; v_day_of_month integer; v_day_of_year integer; v_week integer; v_week_start date; v_week_end date; v_iso_week integer; v_iso_week_start date; v_iso_week_end date; v_month integer; v_month_name varchar2(30); v_month_start date; v_month_end date; v_month_days integer; v_quarter integer; v_quarter_name varchar(2); v_quarter_start date; v_quarter_end date; v_quarter_days integer; v_year integer; v_year_start date; v_year_end date; v_year_days integer; v_is_leap_year varchar2(1); begin begin for y in p_year_start_in .. p_year_end_in loop v_year := y; v_year_start := to_date('01.01.' || v_year, 'DD.MM.YYYY'); v_year_end := to_date('31.12.' || v_year, 'DD.MM.YYYY'); v_year_days := v_year_end - v_year_start + 1; if (v_year_days = 366) then v_is_leap_year := 'Y'; else v_is_leap_year := 'N'; end if; for q in 1 .. 4 loop v_quarter := q; v_quarter_name := 'Q' || v_quarter; case v_quarter_name when 'Q1' then v_quarter_start := to_date('01.01.' || v_year, 'DD.MM.YYYY'); v_quarter_end := to_date('31.03.' || v_year, 'DD.MM.YYYY'); when 'Q2' then v_quarter_start := to_date('01.04.' || v_year, 'DD.MM.YYYY'); v_quarter_end := to_date('30.06.' || v_year, 'DD.MM.YYYY'); when 'Q3' then v_quarter_start := to_date('01.07.' || v_year, 'DD.MM.YYYY'); v_quarter_end := to_date('30.09.' || v_year, 'DD.MM.YYYY'); when 'Q4' then v_quarter_start := to_date('01.10.' || v_year, 'DD.MM.YYYY'); v_quarter_end := to_date('31.12.' || v_year, 'DD.MM.YYYY'); end case; v_quarter_days := v_quarter_end - v_quarter_start + 1; for m in to_number(to_char(v_quarter_start, 'MM')) .. to_number(to_char(v_quarter_end, 'MM')) loop v_month := m; v_month_start := to_date('01.' || v_month || '.' || v_year, 'DD.MM.YYYY'); v_month_end := last_day(v_month_start); v_month_days := v_month_end - v_month_start + 1; v_month_name := to_char(v_month_start, 'MONTH'); for d in 1 .. v_month_days loop v_day := to_date(d || '.' || v_month || '.' || v_year, 'DD.MM.YYYY'); v_day_of_week := to_number(to_char(v_day, 'D')); v_day_of_month := to_number(to_char(v_day, 'DD')); v_day_of_year := to_number(to_char(v_day, 'DDD')); v_day_name := to_char(v_day, 'DAY'); v_week := to_number(to_char(v_day, 'WW')); v_week_start := trunc(v_day, 'WW'); v_week_end := v_week_start + interval '6' day; v_iso_week := to_number(to_char(v_day, 'IW')); v_iso_week_start := trunc(v_day, 'IW'); v_iso_week_end := v_iso_week_start + interval '6' day; pipe row ( time_dim_t ( v_day, v_day_name, v_day_of_week, v_day_of_month, v_day_of_year, v_week, v_week_start, v_week_end, v_iso_week, v_iso_week_start, v_iso_week_end, v_month, v_month_name, v_month_start, v_month_end, v_month_days, v_quarter, v_quarter_name, v_quarter_start, v_quarter_end, v_quarter_days, v_year, v_year_start, v_year_end, v_year_days, v_is_leap_year ) ); end loop; end loop; end loop; end loop; end; end;Ein Aufruf der Table-Function für die Jahre 2011 und 2012 sieht dann wie folgt aus:
select * from table(time_dim(2011, 2012));Auf der Grundlage dieser Table-Function kann dann eine Zeitdimension erstellt werden; sei es als Star- oder Snowflake-Schema, was durch entsprechende Projektionen erreicht werden kann.
Eine Ergänzung wäre die Angabe von Feiertagen, welche oftmals im iCal-Format bereitstehen. Vielleicht widme ich ein weiteres Posting diesem Thema, um zu zeigen, wie man dieses Format in SQL bereitstellen kann.
Keine Kommentare:
Kommentar veröffentlichen