INSERT MONTHS & DAY #'S IN PL/SQL LOOP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Question

    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!!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Question

    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!

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Talking

    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!!!

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    /
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width