itpub 復活節最短sql（未完成程式碼）

Nalternative發表於2011-04-25
SQL
CREATE OR REPLACE PACKAGE EASTER
IS
TYPE T IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
S T;
D T;
E T;
N NUMBER;
A NUMBER;
B NUMBER;
Q NUMBER;
M NUMBER;
W NUMBER;
P NUMBER;
C NUMBER;
PROCEDURE SHOWALLEASTERDAY;
PROCEDURE SHOWMAXOCCURENCEEASTERDAY;
PROCEDURE SHOWLEAPEASTERDAY;
PROCEDURE SHOWFOOLEASTERDAY;
PROCEDURE O(R T);
END;
/
CREATE OR REPLACE PACKAGE  BODY EASTER
IS
PROCEDURE SHOWALLEASTERDAY
IS
BEGIN
    O(S);
END;
PROCEDURE SHOWMAXOCCURENCEEASTERDAY
IS
BEGIN
--SELECT COUNT(*) INTO C FROM TABLE(S);
--DBMS_OUTPUT.PUT_LINE(C);
NULL;
END;
PROCEDURE SHOWLEAPEASTERDAY
IS
BEGIN
SELECT TO_CHAR(LEVEL+TO_DATE('0321','MMDD'),'MM-DD') BULK COLLECT INTO D
FROM DUAL
CONNECT BY LEVEL+TO_DATE('0321','MMDD')<=TO_DATE('0425','MMDD')
AND LEVEL+TO_DATE('0321','MMDD')>=TO_DATE('0322','MMDD');
E:=D MULTISET EXCEPT S;
O(E);
END;
PROCEDURE SHOWFOOLEASTERDAY
IS
J NUMBER;
BEGIN
    FOR I IN 2011..2099 LOOP
        IF S(I)='4-01' THEN
           D(I):=S(I);
        END IF;
    END LOOP;
   
    J:=D.FIRST;
   
    FOR I IN 1..D.COUNT LOOP
          
           D(J):=D.COUNT;
           J:=D.NEXT(J);
    END LOOP;
    O(D);
 
END;
PROCEDURE O(R T)
IS
J NUMBER;
BEGIN
    J:=R.FIRST;
   
    FOR I IN 1..R.COUNT LOOP
           DBMS_OUTPUT.PUT(J||'  ');
           DBMS_OUTPUT.PUT_LINE(R(J));
          
           J:=R.NEXT(J);
          
    END LOOP;
   
END;
BEGIN
    FOR I IN 2011..2099 LOOP
        N:=I-1900;
        A:=MOD(N,19);
        B:=TRUNC((A*7+1)/19);
        Q:=TRUNC(N/4);
        M:=MOD(11*A+4-B,29);
        W:=MOD(N+Q+31-M,7);
        P:=25-M-W;
        S(I):=CASE WHEN P>0 THEN '4-'||LPAD(P,2,0) WHEN P=0 THEN '3-31' ELSE '3-'||LPAD(-P,2,0) END;
    END LOOP;
END;
 
程式碼中問題：巢狀表型別，系統提供的有v KU$_VCNT := KU$_VCNT();（yang程式碼中）
2、巢狀表還有delete方法啊
3、日期函式，自己拼的有點繁瑣了
4、巢狀錶轉換成結果集查詢學習下。
5、自己程式碼中使用了索引表，往下做不動了哦

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-693674/，如需轉載，請註明出處，否則將追究法律責任。

相關文章