Oracle 주차 관리(테이블, 프로시저)

<< Table >>

 

DROP TABLE TB_YEAROFWEEK_MST CASCADE CONSTRAINTS;

CREATE TABLE TB_YEAROFWEEK_MST (

       WEEK_YEAR            VARCHAR2(4 BYTE) NOT NULL,

       WEEK_MONTH           VARCHAR2(2) NOT NULL,

       WEEK_DAY             VARCHAR2(8 BYTE) NOT NULL,

       WEEK_NM              VARCHAR2(20 BYTE) NULL,

       WEEK_Y_NUM           INTEGER NULL,

       WEEK_M_NUM           INTEGER NULL,

       WEEK_FROM_DATE       VARCHAR2(8 BYTE) NULL,

       WEEK_TO_DATE         VARCHAR2(8 BYTE) NULL

);

CREATE UNIQUE INDEX PK_TB_YEAROFWEEK_MST ON TB_YEAROFWEEK_MST

(

       WEEK_YEAR                      ASC,

       WEEK_MONTH                     ASC,

       WEEK_DAY                       ASC

);

ALTER TABLE TB_YEAROFWEEK_MST

       ADD  ( PRIMARY KEY (WEEK_YEAR, WEEK_MONTH, WEEK_DAY) ) ;

COMMENT ON TABLE TB_YEAROFWEEK_MST IS '주차마스터';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_YEAR is '주차년도';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_MONTH is '주차월';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_DAY is '일자';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_NM is '요일';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_Y_NUM is '년주차';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_M_NUM is '월주차';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_FROM_DATE is '주차구간 시작일자';

COMMENT ON COLUMN TB_YEAROFWEEK_MST.WEEK_TO_DATE is '주차구간 마지막일자';

 

 

<< Procedure >>

 

CREATE OR REPLACE PROCEDURE PR_YEAROFWEEK (pYear IN VARCHAR2)

IS 

    BEGIN

        INSERT INTO TB_YEAROFWEEK_MST(WEEK_YEAR, WEEK_MONTH, WEEK_DAY, WEEK_NM, WEEK_Y_NUM, WEEK_M_NUM, WEEK_FROM_DATE, WEEK_TO_DATE)

        SELECT  A.YYYY AS WEEK_YEAR, 

                TO_CHAR(TO_DATE(YMD,'RRRRMMDD'), 'MM') AS WEEK_MONTH, 

                YMD AS WEEK_DAY, 

                CASE WHEN A.WEEK = '1' THEN '일요일'

                     WHEN A.WEEK = '2' THEN '월요일'

                     WHEN A.WEEK = '3' THEN '화요일'

                     WHEN A.WEEK = '4' THEN '수요일'

                     WHEN A.WEEK = '5' THEN '목요일'

                     WHEN A.WEEK = '6' THEN '금요일'

                     WHEN A.WEEK = '7' THEN '토요일'

                END WEEK_NM,

                A.YW AS WEEK_Y_NUM,

                A.MW AS WEEK_M_NUM,

                TO_CHAR(GREATEST(TO_DATE(A.YYYY||'01','RRRRMM'), TRUNC(TO_DATE(A.YYYY||'01','RRRRMM'),'d')+A.YW*7-7),'RRRRMMDD') WEEK_FROM_DATE,

                TO_CHAR(LEAST(TO_DATE(A.YYYY||'1231','RRRRMMDD'), TRUNC(TO_DATE(A.YYYY||'01','RRRRMM'),'d')+A.YW*7-1),'RRRRMMDD') WEEK_TO_DATE

        FROM (SELECT SUBSTR(YMD, 1,4) YYYY, 

                     YMD,

                     TO_NUMBER((TRUNC(TO_DATE(YMD),'d') - TRUNC(TRUNC(TO_DATE(YMD),'RRRR'),'d')) / 7 + 1) YW,

                     CEIL((TO_NUMBER(SUBSTRB(YMD, -2, 2)) + 7 - TO_NUMBER(TO_CHAR(TO_DATE(YMD,'RRRRMMDD'),'d')))/7) MW,

                     TO_CHAR(TO_DATE(YMD, 'RRRRMMDD'), 'd') WEEK

              FROM (SELECT TO_CHAR(TO_DATE(pYear||'0101','RRRRMMDD') + ROWNUM - 1,'RRRRMMDD') AS YMD

                    FROM DUAL

                    CONNECT BY LEVEL <= TO_CHAR(TO_DATE(pYear||'1231','RRRRMMDD'),'ddd')

                    )

             ) A;

    COMMIT;

    EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK; 

        RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));

END;

/

출처 : http://blog.naver.com/PostView.nhn?blogId=2zerox&logNo=90082893243&categoryNo=11&parentCategoryNo=0&viewDate=&currentPage=2&postListTopCurrentPage=1&userTopListOpen=true&userTopListCount=15&userTopListManageOpen=false&userTopListCurrentPage=2