Re: Inserting Records Into a table

From: B. Williams <willdrama_at_hotmail.com>
Date: Fri, 8 Sep 2006 11:23:31 -0400
Message-ID: <1WfMg.10148$JR5.5536@dukeread11>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1157679949.390916.41560_at_i3g2000cwc.googlegroups.com...

> B. Williams wrote:

>> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
>> news:1157673085.523104.66250_at_m73g2000cwd.googlegroups.com...
>> > Are you maybe making it more difficult than necessary for the learning
>> > experience? Take a look at this statement, no looping required:
>> > INSERT INTO MY_TABLE
>> > SELECT
>> > TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'MONTH')
>> > YEAR_MONTH,
>> >
>> > TO_NUMBER(TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'DD'))
>> > YEAR_DAY
>> > FROM
>> > ALL_OBJECTS
>> > WHERE
>> > ROWNUM<=366
>> > AND TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1)<'01-JAN-2007';
>> >
>> > In the above, I am using the ALL_OBJECTS view to seed a counter, in
>> > this case from 1 to 366 (to account for years with leap year days) -
>> > you can use any table with at least 366 rows. By using January 1, 2006
>> > as the starting date, and adding to that (ROWNUM - 1) days, we obtain
>> > all of the dates for 2006. All that you need to do is to format the
>> > dates as required. Note the check at the end to make certain that we
>> > do not include dates in the next year.
>> >
>> > Charles Hooper
>> > PC Support Specialist
>> > K&M Machine-Fabricating, Inc.
>> >
>> Charles, I can do this in different ways as seen below.
>>
>> set serveroutput on size 1000000
>>
>> Declare
>> Type name_varray is varray(12) of varchar2(20);
>> varray_name name_varray :=
>> name_varray(null,null,null,null,null,null,null,null,null,null,null,null);
>>
>> vLastDay number;
>> vFirstDay varchar2(20);
>>
>> v_counter BINARY_INTEGER := 0;
>>
>> begin
>>
>> varray_name(1) := 'January';
>> varray_name(2) := 'February';
>> varray_name(3) := 'March';
>> varray_name(4) := 'April';
>> varray_name(5) := 'May';
>> varray_name(6) := 'June';
>> varray_name(7) := 'July';
>> varray_name(8) := 'August';
>> varray_name(9) := 'September';
>> varray_name(10) := 'October';
>> varray_name(11) := 'November';
>> varray_name(12) := 'December';
>>
>> dbms_output.put_line('Year_Month Year_Day');
>>
>> for i in 1..12 loop
>>
>> vFirstDay := varray_name(i)||' 1 '||to_char(sysdate, 'YYYY');
>>
>> vLastDay := to_number(to_char(last_day(to_date(vFirstDay, 'Month DD
>> YYYY')), 'DD')); -- compute last day of 'current' month
>>
>> for cdate in 1..vLastDay loop
>>
>> dbms_output.put_line(rpad(varray_name(i),10)||' '||cdate);
>> v_counter := v_counter + 1;
>> end loop;
>>
>> end loop;
>> dbms_output.put_line('Total number of ' || v_counter ||' records were
>> inserted.');
>> end;
>> /
>>
>> but, I really want to accomplish this task while meeting all of the
>> requirements.

>
> OK, looking over the requirements again:
> "The end result is supposed to list
> the two columns with the month under the month column and the date
> under the
> day column. It also requires using a seperate loop for every month and
> a
> counter to keep track of the total records inserted. I have a good
> understanding of counter so I don't need any assistance with that. I
> created
> a loop for the months, but it is rather long and it doesn't meet the
> requirement for the day."
>
> It still looks like you are still working too hard at the problem.  The
> solution in pseudo code (actually VB syntax):
>    Dim m As Integer
>    Dim d As Integer
>    Dim varStartMonth As Variant 'This is a date
>    Dim varMonth As Variant 'This is a date
>    Dim strMonth As String 'This is a VARCHAR2
>    Dim intDay As Integer
>    Dim intCounter As Integer
>
>    varStartMonth = CDate("01/01/2006") 'Set the first day of the year
>    intCounter = 0 'Zero the counter, not required in some languages
>
>    For m = 1 To 12 'Loop through the 12 months
>        varMonth = DateAdd("m", m - 1, varStartMonth) 'Record the
> numeric value of the first day of each month
>        strMonth = Format(DateAdd("m", m - 1, varStartMonth), "MMMM")
> 'Record the month name for each month
>        For d = 1 To 31 'Loop through up to 31 days in the month
>            If Format(DateAdd("d", d - 1, varMonth), "MMMM") = strMonth
> Then
>                'This date is still in the same month
>                'Output the strMonth (month name) and the value of d
> (day of the year)
>                Debug.Print strMonth, d
>                intCounter = intCounter + 1 'Increase the counter
>            End If
>        Next d
>    Next m
>
> Help in the translation:
> DateAdd("m", m - 1, varStartMonth)  ===  ADD_MONTHS(varStartMonth, m -
> 1)
> DateAdd("d", d - 1, varMonth)  === varMonth + (d -1)
> CDate("01/01/2006")  === TO_DATE('01/01/2006', 'MM/DD/YYYY')
>
> I will let you finish the translation.  If you complete the
> translation, and it works in a PL/SQL program, post the results.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Charles,
I think I just needed to sleep on this. I really wanted to do this using the instruction I was given. I'll post the instruction below along with the code I used to make it work. Thanks for all of the help.   1.. Create a database table named YEARDATES that has two columns: YEAR_MONTH, which will hold the names of the months of the year (such as 'JANUARY'), and YEAR_DAY; which will hold integers representing each day of the month. Then, write a PL/SQL program that inserts all of the records for every day of the year into the table, using a separate loop for each month. Also, create a counter that counts how many total records are inserted. The finished table should look like this:

YEAR_MONTH YEAR_DAY

JANUARY                  1

JANUARY                   2.

.

JANUARY                  30

JANUARY                  31

FEBRUARY                1

FEBRUARY                2

.

FEBRUARY                27

FEBRUARY                28

 Total number of 365 records were inserted.

set serveroutput on size 1000000
CREATE TABLE yeardates (year_month varchar2(20) ,year_day integer);

Declare
Type name_varray is varray(12) of varchar2(20); varray_name name_varray :=
name_varray(null,null,null,null,null,null,null,null,null,null,null,null);

Type date_varray is varray(31) of integer; varray_date date_varray :=
date_varray(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

v_counter BINARY_INTEGER := 0;

BEGIN

varray_name(1) := 'January';
varray_name(2) := 'February';
varray_name(3) := 'March';
varray_name(4) := 'April';
varray_name(5) := 'May  ';
varray_name(6) := 'June';
varray_name(7) := 'July';
varray_name(8) := 'August';
varray_name(9) := 'September';
varray_name(10) := 'October';
varray_name(11) := 'November';
varray_name(12) := 'December';

dbms_output.put_line( 'YEAR_MONTH' || '     ' ||'YEAY_DAY');

FOR i IN 1..1 LOOP
     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(1));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(1),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(2));
FOR j IN 1..28 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( varray_name(2) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(3));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(3),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(4));
FOR j IN 1..30 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(4),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(5));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(5),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(6));
FOR j IN 1..30 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(6),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(7));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(7),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(8));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(8),10) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(9));
FOR j IN 1..30 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( varray_name(9) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(10));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( rpad(varray_name(10),10) || '      ' 

||varray_date(j));
v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(11));
FOR j IN 1..30 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line(varray_name(11) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP; FOR i IN 1..1 LOOP

     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(12));
FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( varray_name(12) || '      ' ||varray_date(j));

v_counter := v_counter + 1;

   END LOOP;
   END LOOP;
dbms_output.put_line('Total number of ' || v_counter ||' records were inserted.');

END;
/ Received on Fri Sep 08 2006 - 10:23:31 CDT