用多種SQL方式實現一個萬年曆（含年、月、週日...週六、當月天數字段）

regonly1發表於2010-02-04
SQL
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 用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

 

下面建立該函式所需要以來的型別

首先增加一個物件型別：

create or replace type typ_calendar as object(

    年 varchar2(8),

    月 varchar2(8),

    星期日 varchar2(8),

    星期一 varchar2(8),

    星期二 varchar2(8),

    星期三 varchar2(8),

    星期四 varchar2(8),

    星期五 varchar2(8),

    星期六 varchar2(8),

    本月最後一日 varchar2(2),

   

    member function format(

        curday date        := sysdate,

        fmtlen pls_integer := 8

    )return typ_calendar

)

建立該型別的型別體：

create or replace type body typ_calendar as

    member function format(

        curday date        := sysdate,

        fmtlen pls_integer := 8

    ) return typ_calendar as

        v_return   typ_calendar := typ_calendar('','','','','','','','','','');

        v_dd       varchar2(2)  := to_char(curday, 'dd');

       

        function fmt(

            fmtstr varchar2

        )return varchar2 as

        begin

            return lpad(fmtstr, fmtlen, ' ');

        end fmt;

    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

            case v_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('【' || 星期六 || '】');

            else null;

            end case;

        end if;

       

        return v_return;

    end format;

end;

這個成員函式用於格式化輸出的資料，加著重標識以及輸出格式。

 

建立該物件型別的陣列型別：

create or replace type tbl_calendar as table of typ_calendar

 

建立顯示日曆的函式show_calendar，預設日期為當前月：

create or replace function show_calendar(

    v_yermonth varchar2  := to_char(sysdate, 'yyyymm'))

return tbl_calendar as

    v_cal tbl_calendar;

    v_seg pls_integer := 6;

    v_len pls_integer := 8;

    v_yer varchar2(4) := substr(v_yermonth, 1, 4);

    v_mon varchar2(2) := lpad(substr(v_yermonth, 5, 2), 2, '0');

    v_ini date := to_date(v_yermonth || '01', 'yyyymmdd');

begin

    select typ_calendar(v_yer, v_mon,

           case when rn >= wkn - 1 and rn - wkn + 2 <= mxdays

           then  rn - wkn + 2 end,

           case when rn >= wkn - 2 and rn - wkn + 3 <= mxdays

           then  rn - wkn + 3 end,

           case when rn >= wkn - 3 and rn - wkn + 4 <= mxdays

           then  rn - wkn + 4 end,

           case when rn >= wkn - 4 and rn - wkn + 5 <= mxdays

           then  rn - wkn + 5 end,

           case when rn >= wkn - 5 and rn - wkn + 6 <= mxdays

           then  rn - wkn + 6 end,

           case when rn >= wkn - 6 and rn - wkn + 7 <= mxdays

           then  rn - wkn + 7 end,

           case when rn >= wkn - 7 and rn - wkn + 8 <= mxdays

           then  rn - wkn + v_len end,

           mxdays).format()

      bulk collect into v_cal

      from (select (rownum - 1)*7 rn,

                   to_number(to_char(trunc(v_ini, 'mm'), 'd')) wkn,

                   to_number(to_char(last_day(v_ini), 'dd')) mxdays

              from dual

            connect by rownum <= v_seg) b

     where rn - wkn + 2 <= mxdays; --過濾空行

    return v_cal;

end show_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/12932950/viewspace-626842/，如需轉載，請註明出處，否則將追究法律責任。

相關文章