create or replace package easter is
function f(y number) return varchar2;
procedure showAllEasterDay;
procedure showMaxOccurenceEasterDay;
procedure showLeapEasterDay;
procedure showFoolEasterDay;
end easter;
/
function f(y number) return varchar2;
procedure showAllEasterDay;
procedure showMaxOccurenceEasterDay;
procedure showLeapEasterDay;
procedure showFoolEasterDay;
end easter;
/
create or replace package body easter is
function f(y number) return varchar2 is
n number;
m number;
begin
n := y - 1900;
m := mod(11 * mod(n, 19) + 4 - floor((7 * mod(n, 19) + 1) / 19), 29);
return to_char(to_date('0425', 'mmdd') - m -
mod(n + floor(n / 4) + 31 - m, 7),
'mm-dd');
end;
procedure p(v varchar2) is
begin
dbms_output.put_line(v);
end;
procedure showAllEasterDay is
i number;
begin
p('YEAR DAY');
for i in 2011 .. 2099 loop
p(i || ' ' || f(i));
end loop;
end;
function q(u varchar2, v varchar2) return varchar2 is
r varchar2(200);
i number;
begin
r := '';
for x in (with q as (select d, count(*) c
from (select f(2010 + level) d
from dual
connect by level < 90)
where substr(d, 1, 2) in (u, v)
group by d)select *
from q
where c = (select max(c) from q)
order by 1) loop
i := x.c;
r := r || '/' || x.d;
end loop;
return substr(r, 2) || ' ' || i || ' ';
end;
procedure showMaxOccurenceEasterDay is
begin
p('MAXOCC MO_CNT MAXOCC_3 MO3_CNT MAXOCC_4 MO4_CNT');
p(q('03', '04') || q('03', '') || q('', '04'));
end;
procedure showLeapEasterDay is
begin
p('ABSENT_START ABSENT_END');
for x in (select min(d) s, max(d) e
from (select d, l - rownum g
from (select level l,
to_char(to_date('0321', 'mmdd') + level,
'mm-dd') d
from dual
connect by level < 36)
where d not in (select f(2010 + level)
from dual
connect by level < 90))
group by g
order by 1) loop
p(x.s || ' ' || x.e);
end loop;
end;
procedure showFoolEasterDay is
begin
p('YEAR TOTAL');
for x in (select 2010 + level y, count(*) over() d
from dual
where f(2010 + level) = '04-01'
connect by level < 90) loop
p(x.y || ' ' || x.d);
end loop;
end;
end easter;
/
function f(y number) return varchar2 is
n number;
m number;
begin
n := y - 1900;
m := mod(11 * mod(n, 19) + 4 - floor((7 * mod(n, 19) + 1) / 19), 29);
return to_char(to_date('0425', 'mmdd') - m -
mod(n + floor(n / 4) + 31 - m, 7),
'mm-dd');
end;
procedure p(v varchar2) is
begin
dbms_output.put_line(v);
end;
procedure showAllEasterDay is
i number;
begin
p('YEAR DAY');
for i in 2011 .. 2099 loop
p(i || ' ' || f(i));
end loop;
end;
function q(u varchar2, v varchar2) return varchar2 is
r varchar2(200);
i number;
begin
r := '';
for x in (with q as (select d, count(*) c
from (select f(2010 + level) d
from dual
connect by level < 90)
where substr(d, 1, 2) in (u, v)
group by d)select *
from q
where c = (select max(c) from q)
order by 1) loop
i := x.c;
r := r || '/' || x.d;
end loop;
return substr(r, 2) || ' ' || i || ' ';
end;
procedure showMaxOccurenceEasterDay is
begin
p('MAXOCC MO_CNT MAXOCC_3 MO3_CNT MAXOCC_4 MO4_CNT');
p(q('03', '04') || q('03', '') || q('', '04'));
end;
procedure showLeapEasterDay is
begin
p('ABSENT_START ABSENT_END');
for x in (select min(d) s, max(d) e
from (select d, l - rownum g
from (select level l,
to_char(to_date('0321', 'mmdd') + level,
'mm-dd') d
from dual
connect by level < 36)
where d not in (select f(2010 + level)
from dual
connect by level < 90))
group by g
order by 1) loop
p(x.s || ' ' || x.e);
end loop;
end;
procedure showFoolEasterDay is
begin
p('YEAR TOTAL');
for x in (select 2010 + level y, count(*) over() d
from dual
where f(2010 + level) = '04-01'
connect by level < 90) loop
p(x.y || ' ' || x.d);
end loop;
end;
end easter;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-693627/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/81227/viewspace-693627/