Saturday, February 25, 2012

Dates and Loops question

I have a table called Months_Days and I want to fill it with the months 1 thru 12 in the months column, and fill in the corresponding number of days in the days column. i only want to use 1 loop and 1 Insert Into statement. Here's what i have so far. i can get the months inserted, but it inserts 31 for the number of days for each month. can anybody see what i'm doing wrong?
thanks in advance!

Drop Table Month_Days;
Create Table Month_Days(
Month Number(2),
Days Number(2));

Declare
LoopM Binary_Integer;
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
LoopM:= 0;
LoopD:= 0;

Loop
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
End_Date:= Last_Day(Begin_Date);
LoopM:=LoopM+1;
LoopD:=End_Date-Begin_Date+1;
IF LoopM=13 Then
Exit;
End IF;
End_Date:= Add_Months(Begin_Date, 1);
Insert Into Month_Days Values (LoopM, LoopD);
End Loop;
End;Yes: every time you go round the loop you reset Begin_Date to 01-Jan-2008, so you always set LoopD to the number of days in January.

Your code is way over-complicated, and doesn't make use of basic PL/SQL constructs like the FOR loop, e.g.

FOR LoopM IN 1..12 LOOP
...
END LOOP;

Here is a working version of your code:

Declare
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
FOR LoopM IN 1..12
Loop
End_Date:= Last_Day(Begin_Date);
LoopD:=End_Date-Begin_Date+1;
Insert Into Month_Days Values (LoopM, LoopD);
Begin_Date:= Add_Months(Begin_Date, 1);
End Loop;
End;
/

No comments:

Post a Comment