| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert Records into a table
B. Williams wrote:
> <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.
>
> >
>
>
>
>> varray_name(11) := 'November';
> 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';
>
> dbms_output.put_line( 'YEAR_MONTH' || ' ' ||'YEAY_DAY');
> >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
You have an issue with February as you could have 29 days in that month, and your code ignores leap years.
David Fitzjarrell Received on Fri Sep 08 2006 - 12:12:36 CDT
 |
 |