转自
http://space.itpub.net/12932950/viewspace-626842
用SQL语句实现万年历
先上一个例子：
| 年 | 月 | 星期日 | 星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六 | 本月最后一日 |
| 2010 | 06 |
|
| 1 | 2 | 3 | 4 | 5 | 30 |
| 2010 | 06 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 30 |
| 2010 | 06 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 30 |
| 2010 | 06 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 30 |
| 2010 | 06 | 27 | 28 | 29 | 30 |
|
|
| 30 |
思路
首先构建年和月，每个年月对应至少6行（用于显示1-31号的日期，每行7天，所以最少需要ceil((31+6)/7)=6），然后按照每周7天分别列出7个字段。由于7个字段会横向递增，所以需要通过(rownum - 1)×7来实现跨行顺序递增。但是又需要跨月，和跨年，所以需要借助分析函数row_number（row_number按照年和月进行分组，后面简称为rn）。
以上只是初步思路，进一步发现由于每月的月初并不是在周一（呵呵，是的，后来才发现），所以不能直接从周一就开始迭代。于是想了个办法，将每月第一天转为星期几，一旦rn大于等于星期几减1（实际通过to_char获取的星期几会大一天）便开始显示日期。
以上方式解决了开始日期的问题，但是又出现了结束日期的问题。由于没有对结束日期进行约束，所以迭代一直会到每行结束为止，这样就出现了大于最大日期的情况（出现32、33这样的日期）。于是又用与处理开始日期同样的方式解决结束日期的问题，不过首先得取到该月的最后一日，才能控制结束日期的正确性。
以下是几个不同版本的实现方式。
下面这个脚本无需任何初始化便可以执行，而且只需修改第一个虚拟表的开始日期即可。而且这个脚本还有一个特点就是能够对当天的日期进行着重显示（用【】引起来）：
with initday as (select date '1980-2-28' bsday, 6 lx from dual),
strcday as (select to_char(add_months(bsday, ceil(rownum/lx) - 1), 'yyyy') year,
to_char(add_months(bsday, ceil(rownum/lx) - 1), 'mm') month,
to_number(to_char(trunc(add_months(bsday, ceil(rownum/lx) - 1), 'mm'), 'd')) kv,
to_number(to_char(last_day(add_months(bsday, ceil(rownum/lx) - 1)), 'dd')) mxdays
from initday
connect by rownum <= 10000)
select year年, month月,
case when rn >= kv - 1 and rn - kv + 2 <= mxdays then
case when year||month||lpad(rn - kv + 2, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 2) || '】' else to_char(rn - kv + 2) end end星期日,
case when rn >= kv - 2 and rn - kv + 3 <= mxdays then
case when year||month||lpad(rn - kv + 3, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 3) || '】' else to_char(rn - kv + 3) end end星期一,
case when rn >= kv - 3 and rn - kv + 4 <= mxdays then
case when year||month||lpad(rn - kv + 4, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 4) || '】' else to_char(rn - kv + 4) end end星期二,
case when rn >= kv - 4 and rn - kv + 5 <= mxdays then
case when year||month||lpad(rn - kv + 5, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 5) || '】' else to_char(rn - kv + 5) end end星期三,
case when rn >= kv - 5 and rn - kv + 6 <= mxdays then
case when year||month||lpad(rn - kv + 6, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 6) || '】' else to_char(rn - kv + 6) end end星期四,
case when rn >= kv - 6 and rn - kv + 7 <= mxdays then
case when year||month||lpad(rn - kv + 7, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 7) || '】' else to_char(rn - kv + 7) end end星期五,
case when rn >= kv - 7 and rn - kv + 8 <= mxdays then
case when year||month||lpad(rn - kv + 8, 2, '0') = to_char(sysdate, 'yyyymmdd')
then '【' || (rn - kv + 8) || '】' else to_char(rn - kv + 8) end end星期六,
mxdays最大天数
from (select a.*, (row_number()over(partition by a.year, a.month order by rownum) - 1)*7 rn
from strcday a) b
where rn - kv + 2 <= mxdays --过滤空行
增加函数mark版本，需要增加mark函数，使SQL脚本的结构看起来相对简单些：
with initday as (select date '2010-2-1' bsday, 6 lx from dual),
strcday as (select to_char(add_months(bsday, ceil(rownum/lx) - 1), 'yyyy') year,
to_char(add_months(bsday, ceil(rownum/lx) - 1), 'mm') month,
to_number(to_char(trunc(add_months(bsday, ceil(rownum/lx) - 1), 'mm'), 'd')) kv,
to_number(to_char(last_day(add_months(bsday, ceil(rownum/lx) - 1)), 'dd')) mxdays
from initday
connect by rownum <= 10000)
select year年, month月,
case when rn >= kv - 1 and rn - kv + 2 <= mxdays then
mark(year, month, rn - kv + 2, to_char(sysdate, 'yyyymmdd')) end星期日,
case when rn >= kv - 2 and rn - kv + 3 <= mxdays then
mark(year, month, rn - kv + 3, to_char(sysdate, 'yyyymmdd')) end星期一,
case when rn >= kv - 3 and rn - kv + 4 <= mxdays then
mark(year, month, rn - kv + 4, to_char(sysdate, 'yyyymmdd')) end星期二,
case when rn >= kv - 4 and rn - kv + 5 <= mxdays then
mark(year, month, rn - kv + 5, to_char(sysdate, 'yyyymmdd')) end星期三,
case when rn >= kv - 5 and rn - kv + 6 <= mxdays then
mark(year, month, rn - kv + 6, to_char(sysdate, 'yyyymmdd')) end星期四,
case when rn >= kv - 6 and rn - kv + 7 <= mxdays then
mark(year, month, rn - kv + 7, to_char(sysdate, 'yyyymmdd')) end星期五,
case when rn >= kv - 7 and rn - kv + 8 <= mxdays then
mark(year, month, rn - kv + 8, to_char(sysdate, 'yyyymmdd')) end星期六,
mxdays最大天数
from (select a.*, (row_number()over(partition by a.year, a.month order by rownum) - 1)*7 rn
from strcday a) b
where rn - kv + 2 <= mxdays --过滤空行
简化版本（无当日标记）
with initday as (select date '1980-2-28' bsday, 6 lx from dual),
strcday as (select to_char(add_months(bsday, ceil(rownum/lx) - 1), 'yyyy') year,
to_char(add_months(bsday, ceil(rownum/lx) - 1), 'mm') month,
to_number(to_char(trunc(add_months(bsday, ceil(rownum/lx) - 1), 'mm'), 'd')) kv,
to_number(to_char(last_day(add_months(bsday, ceil(rownum/lx) - 1)), 'dd')) mxdays
from initday
connect by rownum <= 10000)
select year年, month月,
case when rn >= kv - 1 and rn - kv + 2 <= mxdays then rn - kv + 2 end星期日,
case when rn >= kv - 2 and rn - kv + 3 <= mxdays then rn - kv + 3 end星期一,
case when rn >= kv - 3 and rn - kv + 4 <= mxdays then rn - kv + 4 end星期二,
case when rn >= kv - 4 and rn - kv + 5 <= mxdays then rn - kv + 5 end星期三,
case when rn >= kv - 5 and rn - kv + 6 <= mxdays then rn - kv + 6 end星期四,
case when rn >= kv - 6 and rn - kv + 7 <= mxdays then rn - kv + 7 end星期五,
case when rn >= kv - 7 and rn - kv + 8 <= mxdays then rn - kv + 8 end星期六,
mxdays最大天数
from (select a.*, (row_number()over(partition by a.year, a.month order by rownum) - 1)*7 rn
from strcday a) b
where rn - kv + 2 <= mxdays --过滤空行
函数版本（可以查看指定月份）
这个脚本可以指定任意一个月的日历进行显示，但是需要依赖不少对象（具体内容已在下面列出）：
SQL> select * from table(show_calendar(‘201005’));
| 年 | 月 | 星期日 | 星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六 | 本月最后一日 |
| 2010 | 05 |
|
|
|
|
|
| 1 | 31 |
| 2010 | 05 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 31 |
| 2010 | 05 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 31 |
| 2010 | 05 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 31 |
| 2010 | 05 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 31 |
| 2010 | 05 | 30 | 31 |
|
|
|
|
| 31 |
默认显示的是当前月的日历，下面是今天（2010-02-05）的执行结果，其中5号进行了着重标记，表示是今天：
SQL> select * from table(show_calendar);
| 年 | 月 | 星期日 | 星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六 | 本月最后一日 |
| 2010 | 06 |
|
| 1 | 2 | 3 | 4 | 5 | 30 |
| 2010 | 06 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 30 |
| 2010 | 06 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 30 |
| 2010 | 06 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 30 |
| 2010 | 06 | 27 | 28 | 29 | 30 |
|
|
| 30 |
下面创建该函数所需要以来的类型
首先增加一个对象类型：
createorreplacetypetyp_calendarasobject(
年varchar2(8),
月varchar2(8),
星期日varchar2(8),
星期一varchar2(8),
星期二varchar2(8),
星期三varchar2(8),
星期四varchar2(8),
星期五varchar2(8),
星期六varchar2(8),
本月最后一日varchar2(2),
memberfunctionformat(
curdaydate :=sysdate,
fmtlenpls_integer:=8
)returntyp_calendar
)
创建该类型的类型体：
createorreplacetypebodytyp_calendaras
memberfunctionformat(
curdaydate :=sysdate,
fmtlenpls_integer:=8
)returntyp_calendaras
v_return typ_calendar := typ_calendar('','','','','','','','','','');
v_dd varchar2(2) := to_char(curday,'dd');
functionfmt(
fmtstrvarchar2
)returnvarchar2as
begin
returnlpad(fmtstr, fmtlen,' ');
endfmt;
begin
v_return.年:=年;
v_return.月:=月;
v_return.星期日:= fmt(星期日);
v_return.星期一:= fmt(星期一);
v_return.星期二:= fmt(星期二);
v_return.星期三:= fmt(星期三);
v_return.星期四:= fmt(星期四);
v_return.星期五:= fmt(星期五);
v_return.星期六:= fmt(星期六);
v_return.本月最后一日:=本月最后一日;
if(年|| lpad(月,2,'0') = to_char(curday,'yyyymm'))then
casev_dd
when星期日then
v_return.星期日:= fmt('【'||星期日||'】');
when星期一then
v_return.星期一:= fmt('【'||星期一||'】');
when星期二then
v_return.星期二:= fmt('【'||星期二||'】');
when星期三then
v_return.星期三:= fmt('【'||星期三||'】');
when星期四then
v_return.星期四:= fmt('【'||星期四||'】');
when星期五then
v_return.星期五:= fmt('【'||星期五||'】');
when星期六then
v_return.星期六:= fmt('【'||星期六||'】');
elsenull;
endcase;
endif;
returnv_return;
endformat;
end;
这个成员函数用于格式化输出的数据，加着重标识以及输出格式。
创建该对象类型的数组类型：
createorreplacetypetbl_calendarastableoftyp_calendar
创建显示日历的函数show_calendar，默认日期为当前月：
createorreplacefunctionshow_calendar(
v_yermonthvarchar2 := to_char(sysdate,'yyyymm'))
returntbl_calendaras
v_cal tbl_calendar;
v_segpls_integer:=6;
v_lenpls_integer:=8;
v_yervarchar2(4) := substr(v_yermonth,1,4);
v_monvarchar2(2) := lpad(substr(v_yermonth,5,2),2,'0');
v_inidate:= to_date(v_yermonth ||'01','yyyymmdd');
begin
selecttyp_calendar(v_yer, v_mon,
casewhenrn >= wkn -1andrn - wkn +2<= mxdays
then rn - wkn +2end,
casewhenrn >= wkn -2andrn - wkn +3<= mxdays
then rn - wkn +3end,
casewhenrn >= wkn -3andrn - wkn +4<= mxdays
then rn - wkn +4end,
casewhenrn >= wkn -4andrn - wkn +5<= mxdays
then rn - wkn +5end,
casewhenrn >= wkn -5andrn - wkn +6<= mxdays
then rn - wkn +6end,
casewhenrn >= wkn -6andrn - wkn +7<= mxdays
then rn - wkn +7end,
casewhenrn >= wkn -7andrn - wkn +8<= mxdays
then rn - wkn + v_lenend,
mxdays).format()
bulkcollectintov_cal
from(select(rownum -1)*7rn,
to_number(to_char(trunc(v_ini,'mm'),'d')) wkn,
to_number(to_char(last_day(v_ini),'dd')) mxdays
fromdual
connectbyrownum <= v_seg) b
wherern - wkn +2<= mxdays;--过滤空行
returnv_cal;
endshow_calendar;
需要注意的是，在函数中调用外部定义的对象类型的时候，对使用的数组变量v_cal进行bulk collect into，要将所有字段用obj_calender对象类型进行包装，然后再bulk collect into。否则会报：
--ora-00947
--PL/SQL: SQL Statement ignored
--PL/SQL: ORA-00947: not enough values
这样的错误。这个地方与本地定义的类型有很明显的不同，本地定义的类型只要列出字段，直接bulk collect into就能正常实现数据载入。这个地方需要注意。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7865774/viewspace-664417/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7865774/viewspace-664417/
2087
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
