Mycode1

CREATE OR REPLACE PROCEDURE ERWP_LOAD_TIME IS

start_dt DATE;
current_dt DATE;
end_dt DATE;

n_days number;
n_hours number;

YEAR number;
QUARTER number;
MONTH number;
MONTH_NAME varchar(50);
WEEK number;
DAY_OF_WEEK number;
DAY_OF_MONTH number;
DAY_NAME varchar(50);
DATE_ID NUMBER;
hh varchar2(6) := '00';
mm varchar2(6) := '00';
this_date_string_v varchar2(64) := null;
this_date_v date := null;
time_format_mask varchar2(64) := 'YYYYMMDD HH24MISS';
hour_name_mask varchar2(64) := 'HH24';
BEGIN

-- set start at today - (2*366), end = today + 10*366
start_dt := to_date('2006-01-01', 'YYYY-MM-DD');
end_dt := to_date('2025-12-31', 'YYYY-MM-DD');

current_dt := TRUNC(start_dt);


n_days := end_dt - start_dt;

for m in 0 .. n_days
loop

YEAR := TO_CHAR(current_dt, 'yyyy');

QUARTER := TO_CHAR(current_dt, 'Q');

MONTH := TO_CHAR(current_dt, 'MM');

MONTH_NAME := TO_CHAR(current_dt, 'MON');

WEEK := TO_CHAR(current_dt, 'WW'); -- week of year

DAY_OF_WEEK := TO_CHAR(current_dt, 'D');

DAY_OF_MONTH := TO_CHAR(current_dt, 'DD');

DAY_NAME := TO_CHAR(current_dt, 'DAY');

DATE_ID := YEAR*10000+MONTH*100+DAY_OF_MONTH;

EXECUTE IMMEDIATE 'INSERT INTO ERW_DATE (ID, YEAR, QUARTER, MONTH, MONTH_NAME, WEEK, DAY_OF_WEEK, DAY_OF_MONTH, DAY_NAME, ORACLE_DATE) ' ||
' values (:DATE_ID, :YEAR, :QUARTER, :MONTH, :MONTH_NAME, :WEEK, :DAY_OF_WEEK, :DAY_OF_MONTH, :DAY_NAME, :ORACLE_DATE) '
USING DATE_ID, YEAR, QUARTER, MONTH, MONTH_NAME, WEEK, DAY_OF_WEEK, DAY_OF_MONTH, DAY_NAME, current_dt;

current_dt := TRUNC (current_dt + INTERVAL '1' DAY);
end loop;

for i in 0..23 loop
mm := '00';
for j in 0..59 loop
insert into ERW_TIME (id, TIME_OF_DAY, TIME_TYPE, MINUTE, HOUR, TIME_NAME)
values(to_number(hh||mm),to_number(hh||mm),'24HHMM',mm,hh,hh||':'||mm);

mm := to_char((to_number(mm)+1));
if (to_number(mm) < 10) then mm := '0'||mm; end if;
end loop; --end minute loop
commit;
hh := to_char(hh+1);
if (hh < 10) then hh := '0'||hh; end if;
dbms_output.put_line('hh = ' || hh);
end loop; --end hour loop
commit;

END;

/