复活节最短编程之神秘代码版

newkid · 发表于 2011-5-6 22:25

感谢猪猪提醒，把空格和TAB对调之后发贴就没问题了。
此外去掉了abcd四个辅值改为用字符串常量传参，学习nyfor的e=e方法代替NOT NULL判断，增加了2011,2099作为动态PLSQL绑定变量，现在是525 bytes了。

DECLARE
  lv_sql CLOB;
  a VARCHAR2(2000);
  b VARCHAR2(2000);
  c VARCHAR2(2000);
  d VARCHAR2(2000);

  FUNCTION encode (p IN VARCHAR2) RETURN VARCHAR2
  AS
   lv_ret VARCHAR(32767);
   lv_asc NUMBER;
   s VARCHAR2(2000) := UPPER(REPLACE(p,CHR(10),' '));
  BEGIN
   FOR i IN 1..LENGTH(p) LOOP
      lv_asc := ASCII(SUBSTR(s,i,1));
      lv_ret := lv_ret||LPAD(CHR(9),TRUNC(lv_asc/10)-1,CHR(9))||' '||LPAD(CHR(9),MOD(lv_asc,10),CHR(9))||' ';
   END LOOP;
   RETURN lv_ret;
  END encode;
BEGIN
  a := q'{
BEGIN
DBMS_OUTPUT.PUT_LINE('YEAR    DAY');
FOR I IN (
SELECT (N+1900)||TO_CHAR(TO_DATE((N+1900)||'04','YYYYMM')+24-M-MOD(N+TRUNC(N/4)+31-M,7),' MM-DD') S
   FROM (SELECT MOD(11*MOD(N,19)+4-TRUNC((7*MOD(N,19)+1)/19),29) M,N
         FROM (SELECT Y1-1901+LEVEL N FROM (SELECT :Y1 Y1,:Y2 Y2 FROM DUAL) CONNECT BY LEVEL<Y2-Y1+2)
         )
ORDER BY 1
) LOOP
   DBMS_OUTPUT.PUT_LINE(I.S);
END LOOP;
END;}';

  b := q'{
DECLARE
S STRING(1000);
S3 STRING(1000);
S4 STRING(1000);
C NUMBER;
C3 NUMBER;
C4 NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('MAXOCC MO_CNT       MAXOCC_3 MO3_CNT             MAXOCC_4       MO4_CNT');
FOR I IN (
         WITH E AS (
         SELECT S
               ,RANK() OVER(ORDER BY CNT DESC) R
               ,RANK() OVER(ORDER BY CASE WHEN S LIKE'03%' THEN CNT ELSE -1 END DESC) R3
               ,RANK() OVER(ORDER BY CASE WHEN S LIKE'04%' THEN CNT ELSE -1 END DESC) R4
               ,CNT
               ,CASE WHEN S LIKE'03%' THEN CNT ELSE -1 END
            FROM ( SELECT S,COUNT(*) CNT
                     FROM (SELECT TO_CHAR(TO_DATE((N+1900)||'04','YYYYMM')+24-M-MOD(N+TRUNC(N/4)+31-M,7),'MM-DD') S
                              FROM (SELECT MOD(11*MOD(N,19)+4-TRUNC((7*MOD(N,19)+1)/19),29) M,N
                                    FROM (SELECT Y1-1901+LEVEL N FROM (SELECT :Y1 Y1,:Y2 Y2 FROM DUAL) CONNECT BY LEVEL<Y2-Y1+2)
                                    )
                           )
                  GROUP BY S
                  )
         )
         SELECT * FROM E WHERE 1 IN (R,R3,R4) ORDER BY 1
) LOOP
   S:=S||CASE WHEN I.R=1 THEN ','||I.S END;
   S3:=S3||CASE WHEN I.R3=1 THEN ','||I.S END;
   S4:=S4||CASE WHEN I.R4=1 THEN ','||I.S END;
   C := CASE WHEN I.R=1 THEN I.CNT ELSE C END;
   C3:= CASE WHEN I.R3=1 THEN I.CNT ELSE C3 END;
   C4:= CASE WHEN I.R4=1 THEN I.CNT ELSE C4 END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUBSTR(S,2)||' '||C||' '||SUBSTR(S3,2)||' '||C3||' '||SUBSTR(S4,2)||' '||C4);
END;}';

  c := q'{
BEGIN
DBMS_OUTPUT.PUT_LINE('ABSENT_START    ABSENT_END');
FOR l IN (
SELECT MIN(d)||'    '||MAX(d) d
   FROM (SELECT TO_CHAR(DATE '1-3-21'+LEVEL,'MM-DD') d FROM DUAL CONNECT BY LEVEL<36
         MINUS
         SELECT TO_CHAR(TO_DATE((N+1900)||'04','YYYYMM')+24-M-MOD(N+TRUNC(N/4)+31-M,7),'MM-DD') S
         FROM (SELECT MOD(11*MOD(N,19)+4-TRUNC((7*MOD(N,19)+1)/19),29) M,N
                  FROM (SELECT Y1-1901+LEVEL N FROM (SELECT :Y1 Y1,:Y2 Y2 FROM DUAL) CONNECT BY LEVEL<Y2-Y1+2)
               )
         ORDER BY 1
         )
GROUP BY TO_DATE(d,'MM-DD')-ROWNUM
) LOOP
   DBMS_OUTPUT.PUT_LINE(l.d);
END LOOP;
END;
}';

  d := q'{
BEGIN
DBMS_OUTPUT.PUT_LINE('YEAR       TOTAL');
FOR l IN (
   SELECT SUBSTR(s,1,4)||'       '||COUNT(*) OVER() s
      FROM (
            SELECT (N+1900)||TO_CHAR(TO_DATE((N+1900)||'04','YYYYMM')+24-M-MOD(N+TRUNC(N/4)+31-M,7),' MM-DD') S
               FROM (SELECT MOD(11*MOD(N,19)+4-TRUNC((7*MOD(N,19)+1)/19),29) M,N
                     FROM (SELECT Y1-1901+LEVEL N FROM (SELECT :Y1 Y1,:Y2 Y2 FROM DUAL) CONNECT BY LEVEL<Y2-Y1+2)
                     )
            )
      WHERE SUBSTR(s,9) ='04-01'
) LOOP
   DBMS_OUTPUT.PUT_LINE(l.s);
END LOOP;
END;}';

  lv_sql := '
CREATE OR REPLACE PACKAGE BODY easter
AS
SUBTYPE t IS STRING(32000);

PROCEDURE p(s t)
AS
   v t;
   e t := s;
   i t;
   j t;
BEGIN
   WHILE e=e LOOP
      i := INSTR(e,'' '');
      j := INSTR(e,'' '',i+1);
      v := v||CHR(i*10+j-i-1);
      e := SUBSTR(e,j+1);
   END LOOP;
   EXECUTE IMMEDIATE v USING 2011,2099;
END;

PROCEDURE showAllEasterDay
AS
BEGIN
  p('''||encode(a)||''');
END;

PROCEDURE showMaxOccurenceEasterDay
AS
BEGIN
  p('''||encode(b)||''');
END;

PROCEDURE showLeapEasterDay
AS
BEGIN
  p('''||encode(c)||''');
END;

PROCEDURE showFoolEasterDay
AS
BEGIN
  p('''||encode(d)||''');
END;
END;
';
  EXECUTE IMMEDIATE lv_sql;
END;
/

测试：
EXEC easter.showAllEasterDay;
EXEC easter.showMaxOccurenceEasterDay;
EXEC easter.showLeapEasterDay;
EXEC easter.showFoolEasterDay;

SELECT SUM(LENGTH(REPLACE(REPLACE(REPLACE(TEXT,' '),CHR(9)),CHR(10)))) bytes FROM USER_SOURCE WHERE NAME='EASTER';

   BYTES
----------
   525