ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL实现万年历

SQL实现万年历

原创 Linux操作系统 作者:hjgluguo 时间:2010-06-05 00:24:34 0 删除 编辑

转自

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_numberrow_number按照年和月进行分组,后面简称为rn)。

以上只是初步思路,进一步实验发现由于每月的月初并不是在周一(呵呵,是的,后来才发现),所以不能直接从周一就开始迭代。于是想了个办法,将每月第一天转为星期几,一旦rn大于等于星期几减1(实际通过to_char获取的星期几会大一天)便开始显示日期。

以上方式解决了开始日期的问题,但是又出现了结束日期的问题。由于没有对结束日期进行约束,所以迭代一直会到每行结束为止,这样就出现了大于最大日期的情况(出现3233这样的日期)。于是又用与处理开始日期同样的方式解决结束日期的问题,不过首先得取到该月的最后一日,才能控制结束日期的正确性。

以下是几个不同版本的实现方式。

下面这个脚本无需任何初始化便可以执行,而且只需修改第一个虚拟表的开始日期即可。而且这个脚本还有一个特点就是能够对当天的日期进行着重显示(用【】引起来):

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-07-11

  • 博文量
    97
  • 访问量
    182050