| 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:1157647743.465901.6420_at_e3g2000cwe.googlegroups.com...
> > Comments embedded.
> > 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.
> >
> > Why?
> >
> >> 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.
> >
> > Again I ask why? What purpose does this serve?
> >
> >>Will someone assist me with this? If I can get some help
> >> with January, I can figure the rest of the months out.
> >>
> >
> > Oh, I'm fairly certain someone will try, but I can't understand why
> > these columns are created, or why you feel the need to store date
> > values as strings. To get you started I'd be looking up information on
> > the to_char() function as it will allow you to isolate date parts as
> > strings. If your issue is generating dates from January 1 on then I
> > would investigate analytic functions such as CUBE. Or read this thread
> > for some ideas:
> >
> > http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/ab8b78b80f6371cf/61217f3306654aa5?lnk=gst&q=generate+past+dates&rnum=1#61217f3306654aa5
> >
> >> Thanks
> >
> >
> > David Fitzjarrell
>
> 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
>
> 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;
> /
Does this learning exercise want the day of the MONTH or the day of the YEAR? Your column names lead me to believe that YEAR_DAY should be a value between 1 and 366 inclusive, not values listing the days for each month (1-31, 1-28 (29), 1-31, etc.). The solution depends upon how you answer that question. If you want the days stored for each month I'd look into the LAST_DAY() function. Coupled with the to_number() and to_char() functions you should be able to generate an ending 'date' for each month of the year and use that to control a second loop to generate the assocated dates; an example is shown below:
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; -- loop control
vFirstDay varchar2(20); -- generated date string for last_day()
function
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 Year_Day');
for i in 1..12 loop
vFirstDay := varray_name(i)||' 1 '||to_char(sysdate, 'YYYY'); -- generate a date string for each month
vLastDay := to_number(to_char(last_day(to_date(vFirstDay, 'Month DD YYYY')), 'DD')); -- compute last day of 'current' month
for cdate in 1..vLastDay loop -- generate dates from first to last for the given month
dbms_output.put_line(rpad(varray_name(i),10)||' '||cdate); end loop;
end loop;
end;
/
Executing this code will produce the following output:
Year_Month Year_Day January 1 January 2 January 3 January 4 January 5 January 6 January 7 January 8 January 9 January 10 January 11 January 12 January 13 January 14 January 15 January 16 January 17 January 18 January 19 January 20 January 21 January 22 January 23 January 24 January 25 January 26 January 27 January 28 January 29 January 30 January 31 February 1 February 2 February 3 February 4 February 5 February 6 February 7 February 8 February 9 February 10 February 11 February 12 February 13 February 14 February 15 February 16 February 17 February 18 February 19 February 20 February 21 February 22 February 23 February 24 February 25 February 26 February 27 February 28 March 1 March 2 March 3 March 4 March 5 March 6 March 7 March 8 March 9 .
If this is what you want you're finished. If not, then you have two loops to write and you'll conditionally choose one or the other depending upon whether February 29 exists:
vFirstDay := varray_name(2)||' 1 '||to_char(sysdate, 'YYYY'); --
first day for Februrary
FebLastDay := to_number(to_char(last_day(to_date(vFirstDay, 'Month DD
YYYY')), 'DD')); -- compute last day
if FebLastDay = 29 then
for d_o_y in 1..366 loop
...
end loop;
else
for d_o_y in 1..365 loop
...
end loop;
end if;
Again, it depends upon what values you actually want returned.
David Fitzjarrell Received on Thu Sep 07 2006 - 13:43:23 CDT
 |
 |