CREATE OR REPLACE PROCEDURE populate_time_table (v_start_date IN date, v_end_date IN date)
as
/*
DESCRIPTION:
This procedure will populate the TIME table completely,
based on entering a start and end date for the table data.
REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 26/06/2002 DRC 1. Created this procedure.
PARAMETERS:
NOTES:
*/
v_day number(2);
v_month number(2);
v_year number(4);
v_wk_yr_no number(2);
v_date date;
v_loop_no number(7);
BEGIN
— Deduct 2 dates to get number of days for loop
SELECT TRUNC (v_end_date – v_start_date)
INTO v_loop_no
FROM dual;
v_date := v_start_date;
— Loop to the number of days + 1 to make it inclusive
FOR i in 1 .. (v_loop_no + 1)
LOOP
v_wk_yr_no := to_number(to_char(v_date,’IW’));
v_day := to_number(to_char(v_date,’DD’));
v_month := to_number(to_char(v_Date,’MM’));
v_year := to_number(to_char(v_date,’YYYY’));
INSERT INTO times values(time_id_seq.nextval, v_day, v_month, v_year, v_wk_yr_no, v_date);
v_date := v_date + 1;
END LOOP;
END;
/