Re: Insert Records into a table

From: <fitzjarrell_at_cox.net>
Date: 8 Sep 2006 07:40:18 -0700
Message-ID: <1157726418.433970.9620@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 Received on Fri Sep 08 2006 - 09:40:18 CDT