ITPub博客

首页 > Linux操作系统 > Linux操作系统 > itpub 复活节最短sql(未完成代码)

itpub 复活节最短sql(未完成代码)

原创 Linux操作系统 作者:Nalternative 时间:2011-04-25 15:54:06 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    178256