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

I need a program that will insert all the MONTHS.(ie JANUARY..), and all the DAYS of each month,(ie 1,2,3...) for a total of 1 full year. It must also include a Counter that will be used to display a message stating the total records inserted,(ie "365 records inserted").

Look forward to hearing from you all!! :D

CREATE TABLE my_dates
(my_date DATE, my_month VARCHAR2(10), my_day VARCHAR2(10), cntr INTEGER);

INSERT INTO my_dates (my_date, my_month, my_day, cntr)
SELECT
TRUNC(sysdate,'YYYY')+ROWNUM -1,
TO_CHAR(TRUNC(sysdate,'YYYY')+ROWNUM -1,'MONTH'),
TO_CHAR(TRUNC(sysdate,'YYYY')+ROWNUM -1,'DD'),
ROWNUM
FROM all_objects
WHERE TRUNC(sysdate,'YYYY')+ROWNUM -1 < ADD_MONTHS(TRUNC(sysdate,'YYYY'),12);

Argh, I didn't notice you want to do inserts in a PL/SQL loop (although I don't see why you should, when a simple SQL will do). Anyway, you can convert the above select into a cursor and perform inserts in a loop.

Jmodic!

Any chance you can show me what that would look like in a PL/SQL format that inserts all the months of the year with their respective number of days ?


Thank you for your responses!

Given my previous definition of table MY_DATES, you can use the following procedure:

SQL> CREATE OR REPLACE PROCEDURE insert_dates (p_year IN INTEGER)
2 AS
3 v_cntr INTEGER := 0;
4 v_start_date DATE := TRUNC(TO_DATE(p_year, 'YYYY'), 'YYYY');
5 v_end_date DATE := TRUNC(TO_DATE(p_year+1, 'YYYY'), 'YYYY');
6 r_dates my_dates%ROWTYPE;
7 BEGIN
8 LOOP
9 r_dates.my_date := v_start_date + v_cntr;
10 EXIT WHEN r_dates.my_date >= v_end_date;
11 v_cntr := v_cntr + 1;
12 r_dates.my_month := TO_CHAR(r_dates.my_date,'MONTH');
13 r_dates.my_day := TO_CHAR(r_dates.my_date,'DD');
14 r_dates.cntr := v_cntr;
15 INSERT INTO my_dates (my_date, my_month, my_day, cntr)
16 VALUES (r_dates.my_date, r_dates.my_month, r_dates.my_day, r_dates.cntr);
17 END LOOP;
18 DBMS_OUTPUT.PUT_LINE(v_cntr || ' rows inserted.');
19 END;
20 /

Procedure created.

SQL> set serveroutput on
SQL> exec insert_dates(2000);
366 rows inserted.

PL/SQL procedure successfully completed.

SQL> exec insert_dates(2001)
365 rows inserted.

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Jmodic!

Thank you very much for your assistance!

Being a new student, I would like to understand better
exactly how this method works.

I also wonder how this same procedure would be done using the format that I am more familiar with, (ie DECLARE, CURSOR, BEGIN, .....).

The program that is actually requested of me is expected to be written using a separate loop for each month, which I can currently do for 1 month, but it's when I try to include all 12 loops in one nicely constructed program where I get hung up.

Granted this would be a much more time-consuming manner in which to accomplish what you have so concisely presented to me, I think for the sake of learning it would be really cool to do it the "hard way" as well.

Anyway, I really appreciate all your help. I'm gonna go try to figure out how to do using 12 separate loops within one program now.

If you get a chance, post how it could be done that way, but dont go out of your way as I feel you have already gone beyond the call for me.

Thanks Again!!!

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:

Code:

---

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;
/

---