Oracle日历

这是在QQ群里，懒羊羊大师贴出来的一段比较经典的oracle代码，用来打印任意一个年份的日历。[@more@]

运行之前要先设置好环境,即定义好年份:
DEFINE YEAR=&YEAR
要查看其它年份,就再执行:
def year=&year1

然后再执行下面的脚本:
select case
when (new_yweek = min(new_yweek)
over(partition by mon order by new_yweek)) then
mon_name
else
null
end as month,
new_yweek as yweek,
row_number() over(partition by mon order by new_yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
to_char(dayofyear, 'ww') as yweek,
case
when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
(to_char(dayofyear, 'd') <
to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
else
to_char(dayofyear, 'ww')
end as new_yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <=
to_char(to_date(&year || '1231', 'yyyymmdd'),
'ddd')))
group by mon, mon_name, new_yweek

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

转载于:http://blog.itpub.net/266238/viewspace-915491/