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;
/
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