ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 复活节最短源代码比赛

复活节最短源代码比赛

原创 Linux操作系统 作者:cow977 时间:2011-04-25 08:45:07 0 删除 编辑
create or replace package easter is
  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;
/
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-693627/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    699
  • 访问量
    754868