Re: Inserting Records Into a table

From: Ed Prochak <edprochak_at_gmail.com>
Date: 7 Sep 2006 13:20:18 -0700
Message-ID: <1157660418.704290.80130@h48g2000cwc.googlegroups.com>

B. Williams wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1157647831.249454_at_bubbleator.drizzle.com...
> > B. Williams wrote:
> >> I have created a table with two columns and one of the columns is the
> >> month and the other is the date. I want to use a pl/sql program to insert
> >> the months and dates into the table using a loop instead of using a bunch
> >> of insert statements.Will someone assist me with this? If I can get some
> >> help with January, I can figure the rest of the months out.
> >>
> >> Thanks
> >
> > Why two columns to do one column's work?
> >
> > SELECT SYSDATE, TO_CHAR(SYSDATE, 'MON'), TO_CHAR(SYSDATE, 'MM'),
> > TO_CHAR(SYSDATE, 'MONTH')
> > FROM dual;
> >
> > And using PL/SQL to do this is even more backwards.
> >
> > But assuming this is just a self-education exercise:
> >
> > CREATE TABLE t (
> > datecol DATE);
> >
> > BEGIN
> > FOR i IN 1..10 LOOP
> > INSERT INTO t
> > (datecol)
> > VALUES(SYSDATE+i);
> > END LOOP;
> > COMMIT;
> > END;
> > /
> >
> > SELECT * FROM t;
> > --
> > Daniel Morgan
> > University of Washington
> > Puget Sound Oracle Users Group

>

> I have this book on using PL/SQL and it is one of the exercises on working
> with records. It ask to create the two columns with the month being a
> varhcar2 and the date being an integer. 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. I'll post that below. What I need is something like
> this

>


> YEAR_MONTH        YEAR_DAY

> January                        1

> January                        2

> ...

> January                        30

> January                        31

>
>

> This is the code I wrote to just list the months. I haven't added the insert
> portion yet because I am a little confused.

>

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

>

> 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');

>

> for i in 1..12 loop

>

> dbms_output.put_line(varray_name(i));

>

> end loop;
> end;
> /

You are on the right path. Exactly what confuses you? All you need at this point is another loop to scan thru the days, inserting each pair (month,date). I'll refrain from further hints until your next post. So try the next step and see what you get. Come back if you need more help, or if you solve it. (It is nice when the discuaaion loop can be closed.)

 Ed Received on Thu Sep 07 2006 - 15:20:18 CDT