<fitzjarrell_at_cox.net> wrote in message
news:1157654603.362856.127240_at_b28g2000cwb.googlegroups.com...
>
> 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
>David,
Thanks for you help. You were correct in your assumption that the days were
1-31 versus 1-365. I just need to insert these values into the table and add
the counter.
Received on Thu Sep 07 2006 - 15:08:45 CDT
