Pages

Monday, November 24, 2014

Create a Calander with SQL

CREATE OR REPLACE Procedure Prc_Calander ( p_month in   varchar2 default to_char(sysdate,'MM'),
                                                                                 p_year    in   varchar2 default to_char(sysdate,'YYYY'),
                                                                                 p_Data   out varchar2
                                                                                ) IS
v_Line varchar2(2000);

Begin

For i in (select Nop, Line
from (
With
-- days: 1 line per week day
days as ( select level day from dual connect by level <= 7 ),
-- weeks: 1 line per possible week in a month
weeks as ( select level-1 week from dual connect by level <= 6 ),
-- mdays: each day of the month within each week
mdays as (
select week, weekday,
case
when day > to_char(last_day(to_date(p_month||'/'||p_year,'MM/YYYY')),'DD')
then ' '
when day <= 0 then ' '
else to_char(day,'99')
end monthday
from ( select week, day weekday,
7*week+day-to_char(to_date(p_month||'/'||p_year,'MM/YYYY'),'D')+1 day
from weeks, days
)
)
-- Display blank line
select 0 nop, null line from dual
union all
-- Display Month title
select 1 nop,
to_char(to_date(p_month||'/'||p_year,'MM/YYYY'),' FMMonth YYYY') line
from dual
union all
-- Display blank line
select 2 nop, null line from dual
union all
-- Display week day name
select 3 nop,
sys_connect_by_path(substr(to_char(trunc(sysdate,'D')+day-1,'Day'),
1,3),' ') line
from days
where day = 7
connect by prior day = day-1
start with day = 1
union all
-- Display each week
select 4+week nop, replace(sys_connect_by_path(monthday,'/'), '/', ' ') line
from mdays
where weekday = 7
connect by prior week = week and prior weekday = weekday-1
start with weekday = 1)
)
Loop

v_Line := v_Line||chr(10)||i.Line;

End Loop;

p_Data := v_Line;

Exception
When others then null;
End Prc_Calander;
/

No comments:

Post a Comment