SQL的魅力是无限的。这里给大家展示一下使用SQL得到日历,同时,用SQL*Plus的break命令格式化显示效果。
sec@ora10g> break on month skip page 1
sec@ora10g> col month for a20 justify center
sec@ora10g> col Su for a4
sec@ora10g> col Mo for a4
sec@ora10g> col Tu for a4
sec@ora10g> col We for a4
sec@ora10g> col Th for a4
sec@ora10g> col Fr for a4
sec@ora10g> col Sa for a4
sec@ora10g> sec@ora10g> select LPAD (Month, 20 - (20 - LENGTH (month)) / 2) month,
2 "Su",
3 "Mo",
4 "Tu",
5 "We",
6 "Th",
7 "Fr",
8 "Sa"
9 from (select TO_CHAR (dt, 'fmMonthfm YYYY') month,
10 case
11 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Dec%'
12 and TO_CHAR (dt + 1, 'iw') = '01'
13 then
14 '53'
15 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Jan%'
16 and TO_CHAR (dt + 1, 'iw') = '53'
17 then
18 '.5'
19 else
20 TO_CHAR (dt + 1, 'iw')
21 end
22 week,
23 MAX(DECODE (TO_CHAR (dt, 'd'),
24 '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
25 "Su",
26 MAX(DECODE (TO_CHAR (dt, 'd'),
27 '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
28 "Mo",
29 MAX(DECODE (TO_CHAR (dt, 'd'),
30 '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
31 "Tu",
32 MAX(DECODE (TO_CHAR (dt, 'd'),
33 '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
34 "We",
35 MAX(DECODE (TO_CHAR (dt, 'd'),
36 '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
37 "Th",
38 MAX(DECODE (TO_CHAR (dt, 'd'),
39 '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
40 "Fr",
41 MAX(DECODE (TO_CHAR (dt, 'd'),
42 '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
43 "Sa"
44 from (select TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
45 from all_objects
46 where ROWNUM <=
47 ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
48 - TRUNC (SYSDATE, 'y'))
49 group by TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
50 order by TO_CHAR (dt + 1, 'iw'))
51 order by TO_DATE (month, 'Month YYYY'), TO_NUMBER (week)
52 /
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
January 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
February 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
March 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
April 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
May 2009 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
June 2009 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
July 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
August 2009 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
September 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
October 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
November 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
December 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
61 rows selected.
尽在SQL & SQL*Plus不言中。
关注一下“break on month skip page 1”和“col month for a20 justify center”SQP*Plus格式化输出的效果。
-- The End --
sec@ora10g> break on month skip page 1
sec@ora10g> col month for a20 justify center
sec@ora10g> col Su for a4
sec@ora10g> col Mo for a4
sec@ora10g> col Tu for a4
sec@ora10g> col We for a4
sec@ora10g> col Th for a4
sec@ora10g> col Fr for a4
sec@ora10g> col Sa for a4
sec@ora10g> sec@ora10g> select LPAD (Month, 20 - (20 - LENGTH (month)) / 2) month,
2 "Su",
3 "Mo",
4 "Tu",
5 "We",
6 "Th",
7 "Fr",
8 "Sa"
9 from (select TO_CHAR (dt, 'fmMonthfm YYYY') month,
10 case
11 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Dec%'
12 and TO_CHAR (dt + 1, 'iw') = '01'
13 then
14 '53'
15 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Jan%'
16 and TO_CHAR (dt + 1, 'iw') = '53'
17 then
18 '.5'
19 else
20 TO_CHAR (dt + 1, 'iw')
21 end
22 week,
23 MAX(DECODE (TO_CHAR (dt, 'd'),
24 '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
25 "Su",
26 MAX(DECODE (TO_CHAR (dt, 'd'),
27 '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
28 "Mo",
29 MAX(DECODE (TO_CHAR (dt, 'd'),
30 '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
31 "Tu",
32 MAX(DECODE (TO_CHAR (dt, 'd'),
33 '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
34 "We",
35 MAX(DECODE (TO_CHAR (dt, 'd'),
36 '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
37 "Th",
38 MAX(DECODE (TO_CHAR (dt, 'd'),
39 '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
40 "Fr",
41 MAX(DECODE (TO_CHAR (dt, 'd'),
42 '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
43 "Sa"
44 from (select TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
45 from all_objects
46 where ROWNUM <=
47 ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
48 - TRUNC (SYSDATE, 'y'))
49 group by TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
50 order by TO_CHAR (dt + 1, 'iw'))
51 order by TO_DATE (month, 'Month YYYY'), TO_NUMBER (week)
52 /
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
January 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
February 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
March 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
April 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
May 2009 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
June 2009 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
July 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
August 2009 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
September 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
October 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
November 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
December 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
61 rows selected.
尽在SQL & SQL*Plus不言中。
关注一下“break on month skip page 1”和“col month for a20 justify center”SQP*Plus格式化输出的效果。
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-618320/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-618320/