一个显示对应日期的日历的sql函数^_^.

出于无聊写了这个函数, 供大家赏玩.:-)

上次贴这个数据的时候, 忘记将对应的数据类型贴出来, 这里将他补齐.

create type DATABAK.date_obj as object (
    month varchar2(20),
    sun char(2),
    mon char(2),
    tue char(2),
    wed char(2),
    thu char(2),
    fri char(2),
    sat char(2)
);

create type DATABAK.date_obj_tab as table of date_obj;


function DATABAK.get_date_str(in_date in date default sysdate)
return date_obj_tab
pipelined
is
date_o date_obj := date_obj(to_char(in_date,'Month'),1,1,1,1,1,1,1);
begin
for rs in (
select
sum(case when date_str = 'sun' then date_d end) sun,
sum(case when date_str = 'mon' then date_d end) mon,
sum(case when date_str = 'tue' then date_d end) tue,
sum(case when date_str = 'wed' then date_d end) wed,
sum(case when date_str = 'thu' then date_d end) thu,
sum(case when date_str = 'fri' then date_d end) fri,
sum(case when date_str = 'sat' then date_d end) sat
from (
select to_number(to_char(date_d,'dd')) date_d,to_char(date_d,'dy') date_str,
to_number(to_char(date_d,'ddd')) - to_number(to_char(date_d,'d')) date_w
from (
select trunc(date_d)+rownum - 1 date_d
from dba_objects a,(
select trunc(in_date,'mm') date_d from dual
) b
where rownum <= to_number(to_char(last_day(date_d),'dd'))
)
)
group by date_w
) loop
date_o.sun := rs.sun;
date_o.mon := rs.mon;
date_o.tue := rs.tue;
date_o.wed := rs.wed;
date_o.thu := rs.thu;
date_o.fri := rs.fri;
date_o.sat := rs.sat;
pipe row (date_o);
end loop;
return;
end;

function DATABAK.get_date_str(in_date in date default sysdate)
return date_obj_tab
pipelined
is
date_o date_obj := date_obj(to_char(in_date,'Month'),1,1,1,1,1,1,1);
begin
for rs in (
select
sum(case when date_str = 'sun' then date_d end) sun,
sum(case when date_str = 'mon' then date_d end) mon,
sum(case when date_str = 'tue' then date_d end) tue,
sum(case when date_str = 'wed' then date_d end) wed,
sum(case when date_str = 'thu' then date_d end) thu,
sum(case when date_str = 'fri' then date_d end) fri,
sum(case when date_str = 'sat' then date_d end) sat
from (
select to_number(to_char(date_d,'dd')) date_d,to_char(date_d,'dy') date_str,
to_number(to_char(date_d,'ddd')) - to_number(to_char(date_d,'d')) date_w
from (
select trunc(date_d)+rownum - 1 date_d
from dba_objects a,(
select trunc(in_date,'mm') date_d from dual
) b
where rownum <= to_number(to_char(last_day(date_d),'dd'))
)
)
group by date_w
) loop
date_o.sun := rs.sun;
date_o.mon := rs.mon;
date_o.tue := rs.tue;
date_o.wed := rs.wed;
date_o.thu := rs.thu;
date_o.fri := rs.fri;
date_o.sat := rs.sat;
pipe row (date_o);
end loop;
return;
end;


实例:

SQL> select * from table(get_date_str());

MONTH SU MO TU WE TH FR SA
-------------------- -- -- -- -- -- -- --
April 1 2
April 3 4 5 6 7 8 9
April 10 11 12 13 14 15 16
April 17 18 19 20 21 22 23
April 24 25 26 27 28 29 30

SQL> select * from table(get_date_str(sysdate-10));

MONTH SU MO TU WE TH FR SA
-------------------- -- -- -- -- -- -- --
March 1 2 3 4 5
March 6 7 8 9 10 11 12
March 13 14 15 16 17 18 19
March 20 21 22 23 24 25 26
March 27 28 29 30 31

SQL>
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-795294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-795294/