'mariadb 달력 테이블 만들기'에 해당되는 글 1건

  1. 2018.06.19 mariadb 달력 테이블 만들기
02.Oracle/DataBase2018. 6. 19. 10:33
반응형

call SP_B_GPS_REG_DATES( 

        '20170101',

        '20171231'

    );


--------------------------------------


CREATE OR REPLACE PROCEDURE `wbdb`.`GenerateRangeDates`(IN S_INDATE DATE, IN E_INDATE DATE)

BEGIN


    -- CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE);

DELETE FROM B_GPS_REG_DATES_TMP;


    loopDate: LOOP

        INSERT INTO B_GPS_REG_DATES_TMP

        (

        YY, MM, DD, QUARTER, INDATE

        )

        VALUES 

        (

        -- SUBSTR(S_INDATE,1,4), SUBSTR(S_INDATE,6,2), SUBSTR(S_INDATE,9,2), QUARTER(S_INDATE), S_INDATE

SUBSTR(S_INDATE,1,4), SUBSTR(S_INDATE,6,2), SUBSTR(S_INDATE,9,2), QUARTER(S_INDATE), CONCAT(SUBSTR(S_INDATE,1,4), SUBSTR(S_INDATE,6,2), SUBSTR(S_INDATE,9,2))

        )

        ; 

        SET S_INDATE = DATE_ADD(S_INDATE, INTERVAL 1 DAY);


        IF S_INDATE <= E_INDATE 

            THEN ITERATE loopDate;

            ELSE LEAVE loopDate;

        END IF;

    END LOOP loopDate;


    -- SELECT day FROM dates;

    -- DROP TEMPORARY TABLE IF EXISTS dates;


END

Posted by 1010