虽然最近有点焦头烂额的感觉,但是看到这种有意思的活动,还是忍不住参加。
题目要求参考:http://www.itpub.net/thread-1419945-1-1.html
直接贴出过程代码:
SQL> create or replace package easter as
2
procedure showAllEasterDay;
3
procedure showMaxOccurenceEasterDay;
4
procedure showLeapEasterDay;
5
procedure showFoolEasterDay;
6
end;
7
/
程序包已创建。
SQL> create or replace
package body easter as
2
subtype z is char;
3
n int;
4
a int;
5
q int := 0;
6
m int;
7
r int;
8
k varchar(30);
9
b z(5) := 'mm-dd';
10
v KU$_VCNT := KU$_VCNT();
11
d z(48);
12
e z(18);
13
g z(35);
14
procedure p(s in z) as
15
begin
16
dbms_output.put_line(s);
17
end;
18
19
procedure showAllEasterDay as
20
begin
21
p('YEAR DAY');
22
for i in 2011..2099 loop
23
p(i || ' ' || v(i));
24
end loop;
25
end;
26
27
procedure showMaxOccurenceEasterDay as
28
s z(173) := 'select listagg(x, ''/'') within group (order by x), max(c)
from (select x, count(*) c,
rank
() over(order by count(*) desc) r from table(:v) where like ''0';
29
t z(38) := '%'' group by
) where r = 1';
30
begin
31
execute immediate replace('select * from (' || s || '_' || t || '), ('
|| s || '3' || t || '), (' || s || '4' || t
|| ')', ' ', ' column_value ')
32
into d, a, e, n, g, m using v, v, v;
33
p('MAXOCC MO_CNT
MAXOCC_3 MO3_CNT MAXOCC_4 MO4_CNT');
34
p(d || a || ' ' || e || n || ' ' || g || m);
35
end;
36
37
procedure showLeapEasterDay as
38
begin
39
p('ABSENT_START ABSENT_END');
40
select min(d), max(d) into e, g from (select to_char(to_date('03-21', b)
+ level, b) d
41
from dual connect by level < to_date('04-26',b) - to_date('03-21',
b))
42
where d not in (select * from table(v));
43
p(e || g);
44
end;
45
46
procedure showFoolEasterDay as
47
begin
48
p('YEAR TOTAL');
49
p(replace(k, '\', q || '
50
'));
51
end;
52
53
begin
54
v.extend(2099);
55
v.delete(1, 2010);
56
for i in 2011..2099 loop
57
n := i-1900;
58
a := mod(n,19);
59
m := mod(11*a+4-trunc((7*a+1)/19),29);
60
r := 25-m-mod(n+trunc(n/4)+31-m,7);
61
v(i) := to_char(to_date('03-31', b) + r, b);
62
if r = 1 then q := q + 1; k := k || i || ' \'; end if;
63
end loop;
64
end;
65
/
程序包体已创建。
SQL> SET
SERVEROUT ON SIZE 100000
SQL> EXEC easter.showFoolEasterDay
YEAR TOTAL
2018 3
2029 3
2040 3
PL/SQL 过程已成功完成。
SQL> EXEC
easter.showLeapEasterDay
ABSENT_START ABSENT_END
03-22 03-24
PL/SQL 过程已成功完成。
SQL> EXEC
easter.showMaxOccurenceEasterDay
MAXOCC MO_CNT MAXOCC_3 MO3_CNT MAXOCC_4 MO4_CNT
03-30/03-31/04-05/04-10/04-15/04-20/04-21 4 03-30/03-31 4 04-05/04-10/04-15/04-20/04-21 4
PL/SQL 过程已成功完成。
SQL> EXEC
easter.showAllEasterDay
YEAR DAY
2011 04-24
2012 04-08
2013 03-31
2014 04-20
2015 04-05
2016 03-27
2017 04-16
2018 04-01
2019 04-21
2020 04-12
2021 04-04
2022 04-17
2023 04-09
2024 03-31
2025 04-20
2026 04-05
2027 03-28
2028 04-16
2029 04-01
2030 04-21
2031 04-13
2032 03-28
2033 04-17
2034 04-09
2035 03-25
2036 04-13
2037 04-05
2038 04-25
2039 04-10
2040 04-01
2041 04-21
2042 04-06
2043 03-29
2044 04-17
2045 04-09
2046 03-25
2047 04-14
2048 04-05
2049 04-18
2050 04-10
2051 04-02
2052 04-21
2053 04-06
2054 03-29
2055 04-18
2056 04-02
2057 04-22
2058 04-14
2059 03-30
2060 04-18
2061 04-10
2062 03-26
2063 04-15
2064 04-06
2065 03-29
2066 04-11
2067 04-03
2068 04-22
2069 04-14
2070 03-30
2071 04-19
2072 04-10
2073 03-26
2074 04-15
2075 04-07
2076 04-19
2077 04-11
2078 04-03
2079 04-23
2080 04-07
2081 03-30
2082 04-19
2083 04-04
2084 03-26
2085 04-15
2086 03-31
2087 04-20
2088 04-11
2089 04-03
2090 04-16
2091 04-08
2092 03-30
2093 04-12
2094 04-04
2095 04-24
2096 04-15
2097 03-31
2098 04-20
2099 04-12
PL/SQL 过程已成功完成。
整个包没有太难懂的地方,就不做太多解释了。这里用了一个取巧的办法,大约节省了10多个字节。在利用TABLE()查询嵌套表时,只有用COLUMN_VALUE来指定列的名称,而这个长度太浪费了,于是在动态SQL中用空格代替,最后再用REPLACE替换回来。到最后没有时间做进一步的调整了,其实如果利用这种方式,将动态SQL中一些经常出现的较长的字符串用空格或回车来替换,可以进一步减少代码量,不过这种方式属于钻评判的漏洞,如果不用空格,用一些特殊字符替换也是可以的,不过代码长度会多一点。
SQL> SELECT
SUM(LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(TEXT,'
'),CHR(9)),CHR(10)),CHR(13)))) BYTES
2 FROM USER_SOURCE
3 WHERE NAME = 'EASTER';
BYTES
----------
1388