Data warehouse ----Time dimension
-----Create table
CREATE TABLE DIM_DATE
(
DATE_ID INTEGER,
YEAR INTEGER,
MONTH INTEGER,
QUARTER INTEGER,
WEEK INTEGER,
DAY INTEGER,
CREATE_DATE DATE
)
-----Create a procedure to fill the data
CREATE OR REPLACE PROCEDURE fill_dim_date (start_date IN char,end_date IN char)
AS
v_counter number := 0;
v_max number := 0;
BEGIN
EXECUTE IMMEDIATE 'truncate table dim_date';
v_max :=
TO_NUMBER(TO_DATE (end_date, 'yyyy-mm-dd')
- TO_DATE (start_date, 'yyyy-mm-dd'));
LOOP
INSERT INTO dim_date (
DATE_ID,
YEAR,
MONTH,
DAY,
WEEK,
CREATE_DATE,
QUARTER
)
VALUES (
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'yyyymmdd')),
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'yyyy')),
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'mm')),
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'dd')),
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'w')),
SYSDATE,
to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'Q'))
);
EXIT WHEN v_counter >= v_max;
v_counter := v_counter + 1;
END LOOP;
COMMIT;
END fill_dim_date;
/
