oracle html文档,生成年度日历(Oracle SQLPLUS版)的html文档(zt)

生成年度日历(Oracle SQLPLUS版)的html文档(zt)

/************************************************************

生成年度日历(Oracle SQLPLUS版)的html文档

Ref: http://www.itpub.net/469543.html

*************************************************************/

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 linesize 1000

set term off verify off feedback off pagesize 999

SET TRIMSPOOL ON

set markup html on entmap ON spool on preformat off

spool e:calendar&year..html

set heading off

select lpad(' ',30)||&year||'年度日历(Oracle SQLPLUS版)' title from dual

/

set heading on

--set markup html on entmap on 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