69. (MYSQL/SQL) 프로시저 (PROCEDURE) - 아이폰 및 안드로이드 등록된 플랫폼 현황 확인

---

[개발 환경 설정]

​

개발 툴 : Heidi

개발 언어 : Mysql

 

---

[소스 코드]

​

/**************************************************************/
-- [DELIMITER : 프로시저 앞,뒤의 위치하여 안에 있는 부분은  한번에 실행]
DELIMITER $$
/**************************************************************/
DROP PROCEDURE IF EXISTS PRO_SEL_MPLAT; -- [이미 생성된 프로시저가 있으면 DROP]
CREATE PROCEDURE PRO_SEL_MPLAT -- [프로시저 생성]
(
    IN I_TYPE    VARCHAR(100) CHARACTER SET UTF8,      -- [인풋 : 필수 : 1=기간별 전체 조회 건수 / 2=기간별 일자 구분 조회 건수 표시]
    IN I_REG     VARCHAR(100) CHARACTER SET UTF8,      -- [인풋 : 필수 : 등록 시작 기간 : 20220613]
    IN I_END     VARCHAR(50) CHARACTER SET UTF8,       -- [인풋 : 필수 : 조회 종료 기간 : 20220620]

    OUT O_MSG    VARCHAR(1000) CHARACTER SET UTF8      -- [리턴 : 메시지]
)

/**************************************************************/


/**************************************************************/
PROC_BODY : BEGIN
/**************************************************************/


/***************************************************************
[프로시저 설명]


[설 명] : 안드로이드, 아이폰 등록된 사용자 정보 조회 실시


[프로시저 호출] : [기간별 전체 조회 건수] :

CALL PRO_SEL_MPLAT('1', '20220613', '20220620', @O_MSG);
SELECT @O_MSG;


[프로시저 호출] : [기간별 일자 구분 조회 건수 표시] :

CALL PRO_SEL_MPLAT('2', '20220613', '20220620', @O_MSG);
SELECT @O_MSG;


[프로시저 리턴] : MSG + 테이블


[프로시저 삭제] :

DROP PROCEDURE PRO_SEL_MPLAT;


[프로시저 생성 목록 확인] :

SHOW PROCEDURE STATUS;

***************************************************************/


/***************************************************************
[로직 정리 : M_DEBUG]

[1] : 현재 날짜 및 시간 데이터 변수에 지정 실시

[2] : 인풋 데이터 널 체크 수행 실시

[3] : [종료 날짜가 시작 날짜 이전 인지 에러 사항 체크 실시]

[4] : UMCS_MAST 테이블에서 등록된 디바이스 구분 조회 실시
***************************************************************/


    -- [전역 변수 선언]
    DECLARE M_DEBUG VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [디버깅 단계]
    DECLARE M_DATE VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [현재 날짜 및 시간]





    -- [SQL 수행 EXCEPTION 처리]
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- [롤백 처리 실시]
        ROLLBACK;

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('N : EXCEPTION : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

    END;





    -- [1] : 현재 날짜 및 시간 데이터 변수에 지정 실시
    SET M_DEBUG = '1'; -- [디버깅 단계]

    SET M_DATE = (
        SELECT CAST(DATE_FORMAT(sysdate(), '%Y%m%d%H%i%s') AS CHAR)
    );





    -- [2] : 인풋 데이터 널 체크 수행 실시
    SET M_DEBUG = '2'; -- [디버깅 단계]

    IF I_TYPE IS NULL OR TRIM(I_TYPE) = '' OR LENGTH(I_TYPE) = 0
       OR I_REG IS NULL OR TRIM(I_REG) = '' OR LENGTH(I_REG) = 0
       OR I_END IS NULL OR TRIM(I_END) = '' OR LENGTH(I_END) = 0
       THEN

       -- [리턴 데이터 삽입]
       SET O_MSG = CONCAT('N : Input Data Is Null : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

       -- [리턴 테이블 반환 실시]
       SELECT '발급일자' AS REDT, -- [등록 일자]

              '' AS ASTD, -- [안드로이드 : 학생]
              '' AS APRO, -- [안드로이드 : 교수]
              '' AS AEMP, -- [안드로이드 : 직원]

              '' AS ISTD, -- [아이폰 : 학생]
              '' AS IPRO, -- [아이폰 : 교수]
              '' AS IEMP, -- [아이폰 : 직원]

              '' AS MSUM  -- [전체 카운트 개수]
       ;

       -- [프로시저 종료]
       LEAVE PROC_BODY;

    END IF;





    -- [3] : [종료 날짜가 시작 날짜 이전 인지 에러 사항 체크 실시]
    SET M_DEBUG = '3'; -- [디버깅 단계]

    IF CAST(I_REG AS INTEGER) > CAST(I_END AS INTEGER) THEN

       -- [리턴 데이터 삽입]
       SET O_MSG = CONCAT('N : 종료 일자가 시작 일자보다 더 커야합니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

       -- [리턴 테이블 반환 실시]
       SELECT '발급일자' AS REDT, -- [등록 일자]

              '' AS ASTD, -- [안드로이드 : 학생]
              '' AS APRO, -- [안드로이드 : 교수]
              '' AS AEMP, -- [안드로이드 : 직원]

              '' AS ISTD, -- [아이폰 : 학생]
              '' AS IPRO, -- [아이폰 : 교수]
              '' AS IEMP, -- [아이폰 : 직원]

              '' AS MSUM  -- [전체 카운트 개수]
       ;

       -- [프로시저 종료]
       LEAVE PROC_BODY;
    END IF;





    -- [4] : UMCS_MAST 테이블에서 등록된 기간별 전체 조회 및 일자별 구분 조회 분기 처리
    SET M_DEBUG = '4'; -- [디버깅 단계]

    IF TRIM(I_TYPE) = '1' THEN -- [기간별 전체 조회 실시]

        SET M_DEBUG = '4-1'; -- [디버깅 단계]


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 기간별 전체 데이터 조회 성공 : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [전체 데이터 조회 실시]
        SELECT '전체일자' AS REDT, -- [등록 일자]
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'android' AND G.MAST_IDDI = '1' -- [안드로이드 : 학생]
                    THEN 1
                    ELSE 0
                    END
               ) AS ASTD,
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'android' AND G.MAST_IDDI = '2' -- [안드로이드 : 교수]
                    THEN 1
                    ELSE 0
                    END
               ) AS APRO,
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'android' AND G.MAST_IDDI = '3' -- [안드로이드 : 직원]
                    THEN 1
                    ELSE 0
                    END
               ) AS AEMP,
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'ios' AND G.MAST_IDDI = '1' -- [아이폰 : 학생]
                    THEN 1
                    ELSE 0
                    END
               ) AS ISTD,
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'ios' AND G.MAST_IDDI = '2' -- [아이폰 : 교수]
                    THEN 1
                    ELSE 0
                    END
               ) AS IPRO,
               SUM(
                    CASE WHEN TRIM(G.MAST_PLAT) = 'ios' AND G.MAST_IDDI = '3' -- [아이폰 : 직원]
                    THEN 1
                    ELSE 0
                    END
               ) AS IEMP,
               COUNT(*) AS MSUM -- [전체 합계]
            FROM
            (
                SELECT CAST(DATE_FORMAT(CAST(MAST_MODT AS DATE ), '%Y-%m-%d') AS CHAR) AS REDT, -- [등록 일자]
                       MAST_IDNO, -- [학번]
                       MAST_IDDI, -- [신분]
                       MAST_PLAT -- [플랫폼]
                    FROM UMCS_MAST
                WHERE CAST(DATE_FORMAT(CAST(MAST_MODT AS DATE ), '%Y%m%d') AS INTEGER)  BETWEEN CAST(TRIM(I_REG) AS INTEGER) AND CAST(TRIM(I_END) AS INTEGER)
                    AND TRIM(MAST_MOCO) != ''
                    AND TRIM(MAST_PLAT) != ''

                    AND MAST_MOCO IS NOT NULL
                    AND MAST_PLAT IS NOT NULL

                GROUP BY REDT, MAST_IDNO, MAST_IDDI, MAST_PLAT -- [그룹 매핑 실시]
            ) G
        ORDER BY CAST(DATE_FORMAT(CAST(G.REDT AS DATE ), '%Y%m%d') AS INTEGER) DESC
        ;


        -- [프로시저 종료]
        LEAVE PROC_BODY;


    -- ============================================

    ELSEIF TRIM(I_TYPE) = '2' THEN -- [기간별 구분 조회 실시]

        SET M_DEBUG = '4-2'; -- [디버깅 단계]


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 기간별 일자 구분 조회 성공 : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [일자 구분 데이터 조회 실시]
        SELECT CAST(DATE_FORMAT(CAST(G.REDT AS DATE ), '%Y-%m-%d') AS CHAR) AS REDT, -- [등록 일자]
               (
                 SELECT COUNT(*) -- [안드로이드 : 학생]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '1'
                   AND TRIM(MAST_PLAT) = 'android'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS ASTD,
               (
                 SELECT COUNT(*) -- [안드로이드 : 교수]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '2'
                   AND TRIM(MAST_PLAT) = 'android'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS APRO,
               (
                 SELECT COUNT(*) -- [안드로이드 : 직원]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '3'
                   AND TRIM(MAST_PLAT) = 'android'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS AEMP,
               (
                 SELECT COUNT(*) -- [아이폰 : 학생]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '1'
                   AND TRIM(MAST_PLAT) = 'ios'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS ISTD,
               (
                 SELECT COUNT(*) -- [아이폰 : 교수]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '2'
                   AND TRIM(MAST_PLAT) = 'ios'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS IPRO,
               (
                 SELECT COUNT(*) -- [아이폰 : 직원]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_IDDI) = '3'
                   AND TRIM(MAST_PLAT) = 'ios'
                   AND TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS IEMP,
               (
                 SELECT COUNT(*) -- [안드로이드 + 아이폰 전체]
                    FROM UMCS_MAST
                 WHERE TRIM(MAST_MODT) LIKE CONCAT('', CAST(G.REDT AS CHAR), '%')
               ) AS MSUM
            FROM
            (
                SELECT CAST(DATE_FORMAT(CAST(MAST_MODT AS DATE ), '%Y%m%d') AS CHAR) AS REDT -- [등록 일자]
                    FROM UMCS_MAST
                WHERE CAST(DATE_FORMAT(CAST(MAST_MODT AS DATE ), '%Y%m%d') AS INTEGER)  BETWEEN CAST(TRIM(I_REG) AS INTEGER) AND CAST(TRIM(I_END) AS INTEGER)
                    AND TRIM(MAST_MOCO) != ''
                    AND TRIM(MAST_PLAT) != ''

                    AND MAST_MOCO IS NOT NULL
                    AND MAST_PLAT IS NOT NULL

                GROUP BY REDT -- [그룹 매핑 실시]
            ) G
        ORDER BY CAST(DATE_FORMAT(CAST(G.REDT AS DATE ), '%Y%m%d') AS INTEGER) DESC
        ;


        -- [프로시저 종료]
        LEAVE PROC_BODY;


    -- ============================================

    ELSE

        SET M_DEBUG = '4-3'; -- [디버깅 단계]

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('N : 정의 되지 않은 조회 타입입니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

        -- [리턴 테이블 반환 실시]
        SELECT '발급일자' AS REDT, -- [등록 일자]

              '' AS ASTD, -- [안드로이드 : 학생]
              '' AS APRO, -- [안드로이드 : 교수]
              '' AS AEMP, -- [안드로이드 : 직원]

              '' AS ISTD, -- [아이폰 : 학생]
              '' AS IPRO, -- [아이폰 : 교수]
              '' AS IEMP, -- [아이폰 : 직원]

              '' AS MSUM  -- [전체 카운트 개수]
        ;

        -- [프로시저 종료]
        LEAVE PROC_BODY;

    END IF;

/**************************************************************/
END$$;
DELIMITER ;
/**************************************************************/

---

 

[결과 출력]

​

 

---