Re: Insert Records into a table

<fitzjarrell_at_cox.net> wrote in message
news:1157726418.433970.9620_at_b28g2000cwb.googlegroups.com...
>
> B. Williams wrote:
>> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
>> news:hs01g21b8f36ue3qphvnthimnpe0rkqp3n_at_4ax.com...
>> > On Thu, 7 Sep 2006 13:40:19 -0400, "B. Williams"
>> > <willdrama_at_hotmail.com> wrote:
>> >
>> >>
>> >>"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
>> >>news:1157648680.372714.103310_at_p79g2000cwp.googlegroups.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
>> >>>
>> >>> You do not need pl/sql or a bunch of SQL statements.
>> >>>
>> >>> Take a look at the following query and see if you cannot figure out a
>> >>> way to convert it into an update statement.
>> >>>
>> >>> 1 select to_char(to_date('01-JAN-06') + a.increase,'MONTH')
>> >>> 2 ,to_char(to_date('01-JAN-06') + a.increase,'DD')
>> >>> 3 from (select rownum as increase from all_objects
>> >>> 4* where rownum < 366) A
>> >>>
>> >>> DECEMBER 28
>> >>> DECEMBER 29
>> >>> DECEMBER 30
>> >>> DECEMBER 31
>> >>> JANUARY 01
>> >>>
>> >>> 365 Rows selected
>> >>>
>> >>> See the SQL manual for the complete list of format options for the
>> >>> to_char and to_date functions plus look up add_months, last_day, and
>> >>> trunc.
>> >>>
>> >>> HTH -- Mark D Powell --
>> >>>
>> >>I wish it were that simple, but like all books, whenever you start a
>> >>new
>> >>chapter, the requirement is to reinvent the wheel. I have to use PL/SQL
>> >>and
>> >>the loops are also a requirement.
>> >>
>> > I would recommend throwing this 'book' in the fire. In that case it is
>> > at least of *some* use.
>> >
>> > --
>> > Sybrand Bakker, Senior Oracle DBA
>>
>> Would you mind looking at my code and helping me fine tune it. I have met
>> most of the requirements. I am stuck on creating multiple loops so that I
>> can control the number of days per month. This is the code I have now,
>> but
>> it assume that every month is 31 days.
>>
>> 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;
>> /
>
> Why do you think it requires two INSERT statements to populate a single
> row? Your code creates 377 rows, one for the month and n for the date
> as this partial listing proves:
>
> YEAR_MONTH YEAR_DAY
> ---------- ----------
> June
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
> YEAR_MONTH YEAR_DAY
> ---------- ----------
> 11
> 12
> 13
> 14
> 15
> 16
> 17
> 18
> 19
> 20
> 21
>
> YEAR_MONTH YEAR_DAY
> ---------- ----------
> 22
> 23
> 24
> 25
> 26
> 27
> 28
> 29
> 30
>
> The code shown below is what you should have ended up with given the
> example I posted earlier:
>
> 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);
> vCounter number:=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';
>
>
> 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'));
>
> for cdate in 1..vLastDay loop
> vCounter := vCounter + 1;
> insert into yeardates (year_month, year_day) values (varray_name(i),
> cdate);
> end loop;
>
>
> end loop;
> commit;
> dbms_output.put_line('A total of '||vCounter||' records were
> inserted.');
> end;
> /
>
> This does as you've asked, and uses one INSERT to populate a row (the
> proper way to INSERT data). What you have written would require an
> INSERT then an UPDATE to populate the data correctly, and even then it
> wouldn't work properly as your 'key' would be the month name and you'd
> end up with a months worth of records with the last date for that month
> as the UPDATE would affect every record for the given month name.
>
> Take the examples given and learn from them, as obviously what you
> think you know is wrong given the code you've tried to write.
>
>
> David Fitzjarrell
>

David,
Thanks for pointing out my mistakes. Your code is well written and concise, but this task called for seperate loops for every month so I just modified my code a little and now the table is populated correctly. I just picked up this book as a challenge and it is challenging. This is the code I wrote. I know it's long and inefficient, but I am at the beginning of the book and certain things havent been introduced yet.

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 j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(1), varray_date(j));
dbms_output.put_line( rpad(varray_name(1),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..28 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(2), varray_date(j));
dbms_output.put_line( rpad(varray_name(2),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(3), varray_date(j));
dbms_output.put_line( rpad(varray_name(3),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..30 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(4), varray_date(j));
dbms_output.put_line( rpad(varray_name(4),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(5), varray_date(j));
dbms_output.put_line( rpad(varray_name(5),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..30 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(6), varray_date(j));
dbms_output.put_line( rpad(varray_name(6),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(7), varray_date(j));
dbms_output.put_line( rpad(varray_name(7),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(8), varray_date(j));
dbms_output.put_line( rpad(varray_name(8),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..30 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(9), varray_date(j));
dbms_output.put_line( rpad(varray_name(9),10) || ' ' ||varray_date(j)); v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(10), varray_date(j)); dbms_output.put_line( rpad(varray_name(10),10) || ' ' ||varray_date(j));
v_counter := v_counter + 1;
END LOOP; FOR j IN 1..30 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(11), varray_date(j)); dbms_output.put_line( rpad(varray_name(11),10) || ' ' ||varray_date(j));
v_counter := v_counter + 1;
END LOOP; FOR j IN 1..31 LOOP
INSERT INTO yeardates
(year_month, year_day)
VALUES(varray_name(12), varray_date(j)); dbms_output.put_line( rpad(varray_name(12),10) || ' ' ||varray_date(j));
v_counter := v_counter + 1;
END LOOP; dbms_output.put_line('Total number of ' || v_counter ||' records were inserted.');

END;
/ Received on Fri Sep 08 2006 - 11:39:38 CDT