62. (MYSQL/SQL) 프로시저 (PROCEDURE) - 사용자 로그인 수행 및 로그인 이력 관리 실시

---

[개발 환경 설정]

​

개발 툴 : Heidi

개발 언어 : Mysql

 

---

[소스 코드]

​

/**************************************************************/
-- [DELIMITER : 프로시저 앞,뒤의 위치하여 안에 있는 부분은  한번에 실행]
DELIMITER $$
/**************************************************************/
DROP PROCEDURE IF EXISTS PRO_INS_MOCO; -- [이미 생성된 프로시저가 있으면 DROP]
CREATE PROCEDURE PRO_INS_MOCO -- [프로시저 생성]
(
    IN I_ID    VARCHAR(500) CHARACTER SET UTF8,     -- [인풋 : 필수 : 사용자 학번 (바이패스) / 아이디]
    IN I_PASS  VARCHAR(500) CHARACTER SET UTF8,     -- [인풋 : 필수 : 사용자 비번]
    IN I_MOCO  VARCHAR(500) CHARACTER SET UTF8,     -- [인풋 : 필수 : 디바이스 고유값]
    IN I_PLAT  VARCHAR(50) CHARACTER SET UTF8,      -- [인풋 : 필수 : 디바이스 플랫폼 : android / ios]

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

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


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


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


[설 명] : 로그인 수행 및 사용자 정보 반환 프로시저


[프로시저 호출] :

CALL PRO_INS_MOCO('9999999990', 'admin@kr', 'A5209A77-B7CC-4FAE-60E8E46E4F83', 'ios', @O_MSG);
SELECT @O_MSG;


[프로시저 리턴] : MSG , TABLE


[프로시저 삭제] :

DROP PROCEDURE PRO_INS_MOCO;


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

SHOW PROCEDURE STATUS;

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


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

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

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

[3] : 인풋 값으로 들어온 사용자 비밀번호 바이패스 체크 실시

[4] : 인풋 값으로 들어온 사용자 로그인 정보에 맞게 로그인 함수 호출 실시

[5] : 로그인 응답 값 확인 및 정상적인 로그인 응답 값이 아닌 경우 프로시저 종료

[6] : UMCS_MAST 테이블에서 같은 학번 값이 다중인 경우 프로시저 종료

[7] : 사용자에게 현재 분실 및 도난 등록이 되어있는지 체크 수행 실시

[8] : 학번에 매핑된 디바이스 기기가 있는지 확인 실시

[9] : 디바이스 고유 값이 널이 아니고 인풋 값으로 들어온 디바이스 고유값과 다른 경우 프로시저 종료

[10] : 디바이스 고유 값이 널이 아니고 인풋 값으로 들어온 디바이스 고유값이 다른 학번에 매핑되었는지 확인

[11] : 디바이스 값이 널인 경우 UMCS_MAST 기기 값 INSERT 및 디바이스 고유 값이 일치하는 경우 정상 로그인 성공 메시지 리턴
***************************************************************/


    -- [전역 변수 선언]
    DECLARE M_DEBUG VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [디버깅 단계]
    DECLARE M_DATE VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [현재 날짜 및 시간]
    DECLARE M_IDNO VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [로그인 함수 호출 값 : 사용자 학번]

    DECLARE M_USER_COUNT INTEGER DEFAULT 0; -- [UMCS_MAST 테이블에서 같은 학번 값이 다중인 경우 체크]
    DECLARE M_REG_DEVICE VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [사용자에게 등록된 디바이스 기기 고유값]
    DECLARE M_REG_USER VARCHAR(100) CHARACTER SET UTF8 DEFAULT ''; -- [디바이스 고유 값 중복 저장되는 이슈 판단]
    DECLARE M_LOST_VALUE 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_ID IS NULL OR TRIM(I_ID) = '' OR LENGTH(I_ID) = 0
        OR I_PASS IS NULL OR TRIM(I_PASS) = '' OR LENGTH(I_PASS) = 0
        OR I_MOCO IS NULL OR TRIM(I_MOCO) = '' OR LENGTH(I_MOCO) = 0
        OR I_PLAT IS NULL OR TRIM(I_PLAT) = '' OR LENGTH(I_PLAT) = 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] : 인풋 값으로 들어온 사용자 비밀번호 바이패스 체크 실시
    SET M_DEBUG = '3'; -- [디버깅 단계]

    IF TRIM(I_PASS) = 'admin@kr' THEN

       -- [로그인 수행 값 : 학번]
       SET M_IDNO = I_ID;


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


       -- [로그인 이력 관리 테이블에 insert 수행 실시]
       START TRANSACTION;
       INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
       VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'Y', O_MSG, M_DATE);
       COMMIT;


       -- [리턴 테이블 반환]
       SELECT *
           FROM UMCS_MAST
       WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO);


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





    -- [4] : 인풋 값으로 들어온 사용자 로그인 정보에 맞게 로그인 함수 호출 실시
    SET M_DEBUG = '4'; -- [디버깅 단계]

    SET M_IDNO = I_ID; -- [로그인 수행 값 : 학번 : 테스트 하드 코딩]





    -- [5] : 로그인 응답 값 확인 및 정상적인 로그인 응답 값이 아닌 경우 프로시저 종료
    SET M_DEBUG = '5'; -- [디버깅 단계]

    IF M_IDNO IS NULL OR TRIM(M_IDNO) = '' OR LENGTH(M_IDNO) = 0 THEN

       -- [리턴 데이터 삽입]
       SET O_MSG = CONCAT('N : 로그인 정보가 올바르지 않습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


       -- [로그인 이력 관리 테이블에 insert 수행 실시]
       START TRANSACTION;
       INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
       VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'N', O_MSG, M_DATE);
       COMMIT;


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


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





    -- [6] : UMCS_MAST 테이블에서 같은 학번 값이 다중인 경우 프로시저 종료
    SET M_DEBUG = '6'; -- [디버깅 단계]

    SET M_USER_COUNT = (
        SELECT COUNT(*)
            FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO)
    );

    IF M_USER_COUNT != 1 THEN

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


       -- [로그인 이력 관리 테이블에 insert 수행 실시]
       START TRANSACTION;
       INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
       VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'N', O_MSG, M_DATE);
       COMMIT;


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


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





    -- [7] : 사용자에게 현재 분실 및 도난 등록이 되어있는지 체크 수행 실시
    SET M_DEBUG = '7'; -- [디버깅 단계]

    SET M_LOST_VALUE = (
        SELECT MAST_PKEY
            FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO)
    );

    IF M_LOST_VALUE IS NOT NULL
        AND TRIM(M_LOST_VALUE) != ''
        AND LENGTH(M_LOST_VALUE) > 0 THEN

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('N : ',CAST(M_IDNO AS CHAR),' 사용자에게 현재 분실 및 도난 신고가 등록되어있습니다. 디바이스 초기화 및 분실, 도난 해제 후 다시 로그인해 주세요. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [로그인 이력 관리 테이블에 insert 수행 실시]
        START TRANSACTION;
        INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
        VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'N', O_MSG, M_DATE);
        COMMIT;


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


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

    END IF;





    -- [8] : 학번에 매핑된 디바이스 기기가 있는지 확인 실시
    SET M_DEBUG = '8'; -- [디버깅 단계]

    -- [디바이스 고유값 추출 : DES3434FE-3232-FDFEDBEFE]
    SET M_REG_DEVICE = (
        SELECT TRIM(MAST_MOCO)
            FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO)
    );





    -- [9] : 디바이스 고유 값이 널이 아니고 인풋 값으로 들어온 디바이스 고유값과 다른 경우 프로시저 종료
    SET M_DEBUG = '9'; -- [디버깅 단계]

    IF M_REG_DEVICE IS NOT NULL
        AND TRIM(M_REG_DEVICE) != ''
        AND LENGTH(M_REG_DEVICE) > 0
        AND TRIM(M_REG_DEVICE) != TRIM(I_MOCO) THEN

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('N : 디바이스 값이 일치하지 않습니다. 기기가 변경된 경우 초기화 후 다시 로그인해 주세요. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [로그인 이력 관리 테이블에 insert 수행 실시]
        START TRANSACTION;
        INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
        VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'N', O_MSG, M_DATE);
        COMMIT;


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


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

    END IF;





    -- [10] : 디바이스 고유 값이 널이 아니고 인풋 값으로 들어온 디바이스 고유값이 다른 학번에 매핑되었는지 확인
    SET M_DEBUG = '10'; -- [디버깅 단계]

    SET M_REG_USER = (
        SELECT MAST_IDNO
            FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) != TRIM(M_IDNO)
            AND TRIM(MAST_MOCO) = TRIM(I_MOCO)
        LIMIT 1
    );

    IF M_REG_USER IS NOT NULL
        AND TRIM(M_REG_USER) != ''
        AND LENGTH(M_REG_USER) > 0 THEN

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('N : 이미 등록된 디바이스 값입니다. (RegUser / ', CAST(M_REG_USER AS CHAR) ,') : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

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

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

    END IF;




    -- [11] : 디바이스 값이 널인 경우 UMCS_MAST 기기 값 INSERT / 디바이스 고유 값이 일치하는 경우 정상 로그인 성공 메시지 리턴
    IF M_REG_DEVICE IS NULL OR TRIM(M_REG_DEVICE) = '' OR LENGTH(M_REG_DEVICE) = 0 THEN

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

        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 정상 로그인 성공 (최초 디바이스 등록) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [UMCS_MAST 테이블 디바이스 값 UPDATE 실시]
        START TRANSACTION;
        UPDATE UMCS_MAST SET MAST_MOCO = TRIM(I_MOCO), MAST_PLAT = TRIM(I_PLAT), MAST_MODT = TRIM(M_DATE), MAST_UPDT = TRIM(M_DATE)
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO);
        COMMIT;


        -- [로그인 이력 관리 테이블에 insert 수행 실시]
        START TRANSACTION;
        INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
        VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'Y', O_MSG, M_DATE);
        COMMIT;


        -- [리턴 테이블 반환]
        SELECT *
           FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO);


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


    ELSE

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


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 정상 로그인 성공 (디바이스 일치 확인) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


        -- [로그인 이력 관리 테이블에 insert 수행 실시]
        START TRANSACTION;
        INSERT INTO UMCS_MOCO (MOCO_IDNO, MOCO_ID, MOCO_PASS, MOCO_FLAT, MOCO_MOCO, MOCO_FLAG, MOCO_MSG, MOCO_UPDT)
        VALUES (M_IDNO, I_ID, I_PASS, I_PLAT, I_MOCO, 'Y', O_MSG, M_DATE);
        COMMIT;


        -- [리턴 테이블 반환]
        SELECT *
           FROM UMCS_MAST
        WHERE TRIM(MAST_IDNO) = TRIM(M_IDNO);


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

    END IF;

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

 

---

[결과 출력]

​

 

---