Oracle输出06年年历

自动版的，默认输出06年的日历
[php]
column next_year new_v next_year
set termout off
select to_char(add_months(sysdate,12),'yyyy') next_year from dual;
set termout on
accept year char prompt '请输入日历年份，默认为明年：' default &next_year;
set timing off
set autotrace off

set linesize 1000
set term off verify off feedback off pagesize 999
SET TRIMSPOOL ON

spool calendar&year..html
set heading off
select lpad(' ',30)||' Calendar     of     '||&year||'    Year   ' title from dual
/
set heading on

set markup html on entmap off spool on preformat off
alter session set nls_language=american;

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
/

SPOOL OFF

set markup html off entmap off preformat on
set term on verify on feedback on pagesize 14
set linesize 80
........................
[/php]

主要改动如下：
1、前面加上年份自动设置功能
2、增加一个表格标题
3、去除多余的空格，大大减小html文件大小

直接存为sql文件，然后在sqlplus中调用即可

ps：jackywood，你的代码我全部存为脚本后，再执行，输入年份后告诉我说
INPUT A YEAR:
SQL> 2344
SP2-0226: Invalid line number

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

转载于:http://blog.itpub.net/26690043/viewspace-716192/