INSERT MONTHS & DAY #'S IN PL/SQL LOOP

[jmodic]

Actually it wouldn't be wise to use any cursor (DECLARE, OPEN, LOOP, FETCH, CLOSE) for this task, but sure, it can be done that way too.

If you wan't to do it with the separate loop for each month, then I would suggest the following. Use one FOR loop with 12 iterations, one for each month. This will be an outer loop and in each iteration it will start a separate inner FOR loop that will do as many itterations as there is days in that month.

As number of months in each year is fixed, you can safely hard code that fact in the outer loop. However the inner loop will have different number of iterations each time, depending on which month (and because of leap years also depending on the year) it is processing. So it wouldn't be wise to hard code that in the program, you'd better use some builtin date functions to determine the number of days in each particular month. LAST_DATE() function would be a good candidate to use for this.

Here is an example:

CREATE OR REPLACE PROCEDURE insert_dates (p_year IN INTEGER)
AS
  v_cntr  INTEGER := 0;  -- total counter
  v_numdays INTEGER;     -- days within each month
  v_first_in_month DATE; -- first date in month
  v_month VARCHAR2(10);
BEGIN
  FOR i IN 1..12 LOOP --outer loop for each month
    v_first_in_month := TO_DATE(TO_CHAR(p_year)||TO_CHAR(i), 'YYYYMM');
    v_month := TO_CHAR(v_first_in_month, 'MONTH'); 
    v_numdays := TO_NUMBER(TO_CHAR(LAST_DAY(v_first_in_month),'DD'));
    FOR j IN 1..v_numdays LOOP --inner loop for days within a month
      v_cntr := v_cntr+1;
      INSERT INTO my_dates (my_date, my_month, my_day, cntr)
        VALUES (v_first_in_month + (j-1), v_month, j, v_cntr);
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_cntr || ' rows inserted.');
END;
/