72. (MYSQL/SQL) 프로시저 (PROCEDURE) - 모바일 분실 및 도난 조회, 등록, 해제 수행 실시

---

[개발 환경 설정]

​

개발 툴 : Heidi

개발 언어 : Mysql

 

---

[소스 코드]

​

/**************************************************************/
-- [DELIMITER : 프로시저 앞,뒤의 위치하여 안에 있는 부분은  한번에 실행]
DELIMITER $$
/**************************************************************/
DROP PROCEDURE IF EXISTS PRO_ALL_MLOST; -- [이미 생성된 프로시저가 있으면 DROP]
CREATE PROCEDURE PRO_ALL_MLOST -- [프로시저 생성]
(
    IN I_TYPE    VARCHAR(50) CHARACTER SET UTF8,      -- [인풋 : 공통 : 필수 : 1=조회/2=등록/3=해제]
    IN I_REG    VARCHAR(50) CHARACTER SET UTF8,       -- [인풋 : 등록 : 1=분실/2=도난]
    IN I_IDNO    VARCHAR(100) CHARACTER SET UTF8,     -- [인풋 : 사용자 학번]
    IN I_NAME    VARCHAR(200) CHARACTER SET UTF8,     -- [인풋 : 사용자 학번]
    IN I_IDDI    VARCHAR(50) CHARACTER SET UTF8,      -- [인풋 : 사용자 신분]
    IN I_PLAT    VARCHAR(100) CHARACTER SET UTF8,     -- [인풋 : 디바이스 플랫폼]
    IN I_MOCO    VARCHAR(300) CHARACTER SET UTF8,     -- [인풋 : 디바이스 고유값]

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

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


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


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


[설 명] : 사용자 디바이스 분실 및 도난 조회, 등록, 해제 수행 프로시저





[프로시저 호출] : [조회] : LIKE 조건

CALL PRO_ALL_MLOST(
  '1', -- [I_TYPE]
  '',
  '', -- [I_IDNO]
  '', -- [I_NAME]
  '',
  '', -- [I_PLAT]
  '',
  @O_MSG
);
SELECT @O_MSG;





[프로시저 호출] : [등록] : [분실 신고]

CALL PRO_ALL_MLOST(
  '2', -- [I_TYPE]
  '1', -- [I_REG]
  '9999999990', -- [I_IDNO]
  '',
  '1', -- [I_IDDI]
  'ios', -- [I_PAT]
  'A5209A77-B7CC-4FAE-60E8E46E4F834422', -- [I_MOCO]
  @O_MSG
);
SELECT @O_MSG;





[프로시저 호출] : [해제]

CALL PRO_ALL_MLOST(
  '3', -- [I_TYPE]
  '',
  '9999999990', -- [I_IDNO]
  '',
  '1', -- [I_IDDI]
  'ios', -- [I_PAT]
  'A5209A77-B7CC-4FAE-60E8E46E4F834422', -- [I_MOCO]
  @O_MSG
);
SELECT @O_MSG;





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





[프로시저 삭제] :

DROP PROCEDURE PRO_ALL_MLOST;





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

SHOW PROCEDURE STATUS;


[연관 프로시저 목록]
PRO_INS_MOCO (로그인 수행) : 분실, 도난 여부 검증

PRO_SEL_MOCO (단말기 등록 조회) : 분실, 도난 여부 결과 리턴

PRO_DEL_MOCO (단말기 초기화) : 분실, 도난 초기화
***************************************************************/


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

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

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

[3] : 인풋 타입에 맞게 조회, 등록, 해제 분기 처리 실시
***************************************************************/


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

    DECLARE M_REG_COUNT INTEGER DEFAULT 0; -- [학적에 사용자가 등록되어있는지 체크 실시]
    DECLARE M_REG_MOCO VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [사용자에게 디바이스가 등록되어있는지 확인 실시]
    DECLARE M_REG_PKEY 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), ']');

        -- [리턴 테이블 반환]
        SELECT *
            FROM UMCS_MAST
        LIMIT 0;
    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 THEN

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

        -- [리턴 테이블 반환]
        SELECT *
            FROM UMCS_MAST
        LIMIT 0;

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

    END IF;





    -- [3] : 인풋 타입에 맞게 조회, 등록, 해제 분기 처리 실시
    IF TRIM(I_TYPE) = '1' THEN -- [조회]

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

        -- [리턴 메시지 삽입]
        SET O_MSG = CONCAT('Y : 분실 및 도난 조회를 완료했습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

        -- [분실 및 도난으로 등록된 사용자 조회 수행 실시]
        SELECT *
            FROM UMCS_MAST
        WHERE TRIM(MAST_PKEY) != '' -- [분실 및 도난 신고된 것이 널이 아닌 것]
          AND MAST_PKEY IS NOT NULL
          AND LENGTH(MAST_PKEY) > 0

          AND TRIM(MAST_IDNO) LIKE CONCAT('%', CAST(I_IDNO AS CHAR), '%') -- [학번]
          AND TRIM(MAST_NAME) LIKE CONCAT('%', CAST(I_NAME AS CHAR), '%') -- [이름]
          AND TRIM(MAST_IDDI) LIKE CONCAT('%', CAST(I_IDDI AS CHAR), '%') -- [신분]
          AND TRIM(MAST_PLAT) LIKE CONCAT('%', CAST(I_PLAT AS CHAR), '%') -- [플랫폼]
        ORDER BY MAST_KEYDT DESC;

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

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

    ELSEIF TRIM(I_TYPE) = '2' THEN -- [등록]

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

        -- [등록에 필요한 인풋 데이터 체크 수행 실시]
        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_IDNO IS NULL OR TRIM(I_IDNO) = '' OR LENGTH(I_IDNO) = 0
           OR I_IDDI IS NULL OR TRIM(I_IDDI) = '' OR LENGTH(I_IDDI) = 0
           OR I_PLAT IS NULL OR TRIM(I_PLAT) = '' OR LENGTH(I_PLAT) = 0
           OR I_MOCO IS NULL OR TRIM(I_MOCO) = '' OR LENGTH(I_MOCO) = 0
           THEN

            -- [리턴 데이터 삽입]
            SET O_MSG = CONCAT('N : 등록에 필요한 데이터를 다시 확인해주세요. (INPUT DATA) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

            -- [리턴 테이블 반환]
            SELECT *
                FROM UMCS_MAST
            LIMIT 0;

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

        END IF;



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

        -- [학적에 사용자가 등록되어있는지 확인 실시]
        SET M_REG_COUNT = (
            SELECT COUNT(*)
                FROM UMCS_MAST
            WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
              AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
        );
        IF M_REG_COUNT != 1 THEN

           -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 학적에 등록된 사용자 정보를 다시 확인해주세요. (UserCount / ', CAST(M_REG_COUNT AS CHAR) ,') : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [정상적으로 사용자에게 단말기가 등록이 되어있는지 확인 실시]
        SET M_REG_MOCO = (
            SELECT MAST_MOCO
                FROM UMCS_MAST
            WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
              AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
        );
        IF M_REG_MOCO IS NULL OR TRIM(M_REG_MOCO) = '' OR LENGTH(M_REG_MOCO) = 0 THEN

           -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 사용자에게 디바이스가 등록되어있지 않습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [인풋으로 들어온 디바이스 고유값과 실제 사용자에게 등록된 디바이스 고유값이 같은지 확인]
        IF TRIM(M_REG_MOCO) != TRIM(I_MOCO) THEN

           -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 사용자에게 등록된 단말기 값을 다시 확인해주세요. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [이미 사용자에게 분실 및 도난 신고가 등록되어있는지 확인 실시]
        SET M_REG_PKEY = (
            SELECT CAST(MAST_PKEY AS CHAR)
                FROM UMCS_MAST
            WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
              AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
        );
        IF TRIM(CAST(M_REG_PKEY AS CHAR)) != '' OR LENGTH(CAST(M_REG_PKEY AS CHAR)) > 0 THEN

            -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 이미 분실 및 도난 신고가 등록되어있습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [분실 및 도난 신고 등록 수행 실시]
        START TRANSACTION;
        UPDATE UMCS_MAST
        SET MAST_PKEY = CAST(I_REG AS CHAR), MAST_KEYDT = CAST(M_DATE AS CHAR)
        WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
           AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
           AND TRIM(MAST_PLAT) = TRIM(I_PLAT)
           AND TRIM(MAST_MOCO) = TRIM(I_MOCO);
        COMMIT;

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 분실 및 도난 신고가 완료되었습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

        -- [리턴 테이블 반환]
        SELECT *
            FROM UMCS_MAST
        LIMIT 0;

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

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

    ELSEIF TRIM(I_TYPE) = '3' THEN -- [해제]

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

        -- [해제에 필요한 인풋 데이터 체크 수행 실시]
        IF I_TYPE IS NULL OR TRIM(I_TYPE) = '' OR LENGTH(I_TYPE) = 0
           OR I_IDNO IS NULL OR TRIM(I_IDNO) = '' OR LENGTH(I_IDNO) = 0
           OR I_IDDI IS NULL OR TRIM(I_IDDI) = '' OR LENGTH(I_IDDI) = 0
           OR I_PLAT IS NULL OR TRIM(I_PLAT) = '' OR LENGTH(I_PLAT) = 0
           OR I_MOCO IS NULL OR TRIM(I_MOCO) = '' OR LENGTH(I_MOCO) = 0
           THEN

            -- [리턴 데이터 삽입]
            SET O_MSG = CONCAT('N : 해제에 필요한 데이터를 다시 확인해주세요. (INPUT DATA) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

            -- [리턴 테이블 반환]
            SELECT *
                FROM UMCS_MAST
            LIMIT 0;

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

        END IF;



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

        -- [학적에 사용자가 등록되어있는지 확인 실시]
        SET M_REG_COUNT = (
            SELECT COUNT(*)
                FROM UMCS_MAST
            WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
              AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
        );
        IF M_REG_COUNT != 1 THEN

           -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 학적에 등록된 사용자 정보를 다시 확인해주세요. (UserCount / ', CAST(M_REG_COUNT AS CHAR) ,') : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [정상적으로 사용자에게 분실 및 도난 신고가 등록 되어있는지 확인 실시]
        SET M_REG_PKEY = (
            SELECT CAST(MAST_PKEY AS CHAR)
                FROM UMCS_MAST
            WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
              AND TRIM(MAST_IDDI) = TRIM(I_IDDI)
        );
        IF TRIM(CAST(M_REG_PKEY AS CHAR)) = '' OR LENGTH(CAST(M_REG_PKEY AS CHAR)) = 0 THEN

            -- [리턴 데이터 삽입]
           SET O_MSG = CONCAT('N : 분실 및 도난 신고가 등록되어있지 않습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

           -- [리턴 테이블 반환]
           SELECT *
                FROM UMCS_MAST
           LIMIT 0;

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



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

        -- [분실, 도난 신고 초기화 및 디바이스 초기화 수행 실시]
        START TRANSACTION;
        UPDATE UMCS_MAST
        SET MAST_PKEY = '', MAST_KEYDT = '', MAST_MOCO = '', MAST_MODT = '', MAST_PLAT = ''
        WHERE TRIM(MAST_IDNO) = TRIM(I_IDNO)
           AND TRIM(MAST_IDDI) = TRIM(I_IDDI);
        COMMIT;

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 분실, 도난 신고 해제 및 디바이스가 초기화 되었습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

        -- [리턴 테이블 반환]
        SELECT *
            FROM UMCS_MAST
        LIMIT 0;

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

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

    ELSE

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

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

        -- [리턴 테이블 반환]
        SELECT *
            FROM UMCS_MAST
        LIMIT 0;

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

    END IF;


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

 

---

[결과 출력]

​

---