Re: Insert Records into a table

From: B. Williams <willdrama_at_hotmail.com>
Date: Thu, 7 Sep 2006 19:37:17 -0400
Message-ID: <V22Mg.10117$JR5.8602@dukeread11>

"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;
/ Received on Thu Sep 07 2006 - 18:37:17 CDT