Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Inserting Records Into a table

Re: Inserting Records Into a table

From: B. Williams <willdrama_at_hotmail.com>
Date: Thu, 7 Sep 2006 19:38:34 -0400
Message-ID: <642Mg.10118$JR5.6449@dukeread11>

"B. Williams" <willdrama_at_hotmail.com> wrote in message news:yV0Mg.10111$JR5.8533_at_dukeread11...
>
> "Ed Prochak" <edprochak_at_gmail.com> wrote in message
> news:1157660418.704290.80130_at_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
>>
> Ed,
> I can get the loop to isert the months into the table using the following
> code.
>
> FOR i IN 1..12 LOOP
> INSERT INTO yeardates
> (year_month)
> VALUES(varray_name(i));
> END LOOP;
>
> I can also get the loop to insert the date using a similiar loop
>
> FOR j IN 1..31 LOOP
> INSERT INTO yeardates
> (year_day)
> VALUES(varray_date(j));
> My problem is that I can't figure out how to get my output to display the
> (month day) format and even more confusing is that I can't figure out
> how to have January display 31 dates while having February displays only
> 28 and so on.
>
> I need to make a loop for each month that will display that month along
> with the days of that month.
>
> Can you assist me with that.
>
>
>
> This is the code I have so far.

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..12 LOOP
     INSERT INTO yeardates
     (year_month)
     VALUES(varray_name(i));


FOR j IN 1..31 LOOP
     INSERT INTO yeardates
     (year_day)
     VALUES(varray_date(j));
dbms_output.put_line( varray_name(i) || '     ' ||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 Thu Sep 07 2006 - 18:38:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US