虽然最近有点焦头烂额的感觉,但是看到这种有意思的活动,还是忍不住参加。
题目要求参考: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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-693271/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-693271/