用多種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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 當月、本週、當年SQL查詢OracleSQL
- java實現一個月的日曆列印Java
- java一個月日曆Java
- java 獲取上月、一個月最後一天、週一、週日、本月日期Java
- [python實用程式碼片段]python獲取當前時間的前一天，前一週，前一個月Python
- 大神爆料：iPhone 7 9月12日當週釋出iPhone
- S根據年月實現獲得當月天數
- java 實現根據年月得到這個月的日曆Java
- 微軟一週年更新RTM版將8月2日推送：6個方面革新微軟
- Qtum量子鏈週報（4月1日-4月7日）QT
- Qtum量子鏈週報（3月18日-3月24日）QT
- Qtum量子鏈週報（3月25日-3月31日）QT
- Qtum量子鏈週報（3月4日-3月10日）QT
- Qtum量子鏈週報（11月19日-11月25日）QT
- Qtum量子鏈週報（1月14日-1月20日）QT
- PHP日期加減月數，天數，週數，小時，分，秒等等PHP
- 8月5日-8月11日：建工建材檢測資訊一週速覽
- Spark SQL日期相距天數,月數SparkSQL
- java 日期加減天數、月數、年數的計算方式Java
- SQLServer查詢最近一天，三天，一週，一月，一季度方法SQLServer
- 9k9k：2017年2月27日-3月5日一週網頁遊戲資料包告網頁遊戲
- 9k9k：2017年3月27日-4月2日一週網頁遊戲資料包告網頁遊戲
- 9k9k：2017年7月31日-8月6日一週網頁遊戲資料包告網頁遊戲
- 9k9k：2014年5月26日-6月1日一週網頁遊戲開服資料網頁遊戲
- 根據傳入的年，月引數，返回當月的最大天數，平潤都可計算
- 用java實現日曆demo。Java
- 大資料投融資週報（3月3日——3月9日，共17起）大資料
- 大資料投融資週報（3月10日——3月16日，共17起）大資料
- 9k9k：2016年12月26日-2017年1月2日一週網頁遊戲資料包告網頁遊戲
- Oracle用SQL列印日曆OracleSQL
- 第六週週二
- php 時間戳 總結 今日，昨日，上週，本週，最近三個月，上季，本季，去年，最近七天，今年，最近三十天PHP時間戳
- 月的天數
- 計算任意兩個日期之間的天數，週數。《python大學實用教程》例子Python
- 整理了SQL Server中是實現日曆的幾個方法SQLServer
- 遊戲⼴告買量週報（3月第三週）遊戲
- Mysql 獲取當月和上個月第一天和最後一天的解決方案MySql
- 研究發現女性月經週期會跟月亮週期短暫同步