68. (MYSQL/SQL) 프로시저 (PROCEDURE) - 관리자 조회 , 등록 , 수정 , 삭제 실시

---

[개발 환경 설정]

​

개발 툴 : Heidi

개발 언어 : Mysql

 

---

[소스 코드]

/**************************************************************/
-- [DELIMITER : 프로시저 앞,뒤의 위치하여 안에 있는 부분은  한번에 실행]
DELIMITER $$
/**************************************************************/
DROP PROCEDURE IF EXISTS PRO_ALL_ADMIN; -- [이미 생성된 프로시저가 있으면 DROP]
CREATE PROCEDURE PRO_ALL_ADMIN -- [프로시저 생성]
(
    IN I_TYPE    VARCHAR(100) CHARACTER SET UTF8,    -- [인풋 : 필수 : 1=조회 / 2=등록 / 3=수정 / 4=삭제]
    IN I_ID      VARCHAR(100) CHARACTER SET UTF8,    -- [인풋 : 로그인 학번 : 20131575]
    IN I_PWD     VARCHAR(300) CHARACTER SET UTF8,    -- [인풋 : 로그인 비번 : lotecs@kr]
    IN I_NAME    VARCHAR(200) CHARACTER SET UTF8,    -- [인풋 : 이름 : 로텍스]
    IN I_DEPT    VARCHAR(200) CHARACTER SET UTF8,    -- [인풋 : 부서 : 전산팀]
    IN I_IP      VARCHAR(200) CHARACTER SET UTF8,    -- [인풋 : IP : 192.168.0.15]
    IN I_ISSU    VARCHAR(100) CHARACTER SET UTF8,    -- [인풋 : 관리자 권한 : 전체=1111 / 일부=1001]
    IN I_PSCO    VARCHAR(200) CHARACTER SET UTF8,    -- [인풋 : 관리자 부서 코드 : A0123KDFOI-1]
    IN I_MSG     VARCHAR(2000) CHARACTER SET UTF8,   -- [인풋 : 비고 메시지 : ]

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


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


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


[설 명] : 관리자 조회, 등록, 수정, 삭제 수행 프로시저





[프로시저 호출] : [전체 조회]

CALL PRO_ALL_ADMIN(
  '1', -- [필수 : I_TYPE]
  '',
  '',
  '',
  '',
  '',
  '',
  '',
  '',
  @O_MSG
);
SELECT @O_MSG;





[프로시저 호출] : [개별 조회]

CALL PRO_ALL_ADMIN(
  '1',       -- [필수 : I_TYPE]
  'admin',  -- [필수 : I_ID]
  '',
  '관리자',   -- [필수 : I_NAME]
  '관리팀',   -- [필수 : I_DEPT]
  '',
  '',
  '',
  '',
  @O_MSG
);
SELECT @O_MSG;





[프로시저 호출] : [등록]

CALL PRO_ALL_ADMIN(
  '2',         -- [필수 : I_TYPE]
  '20131575',  -- [필수 : I_ID]
  '',
  '김관리',     -- [필수 : I_NAME]
  '전산팀',     -- [필수 : I_DEPT]
  '',
  '1111',      -- [필수 : I_ISSU]
  '',
  '',
  @O_MSG
);
SELECT @O_MSG;





[프로시저 호출] : [수정]

CALL PRO_ALL_ADMIN(
  '3',       -- [필수 : I_TYPE]
  'admin',  -- [필수 : I_ID]
  'admin@kr',
  '관리자',   -- [필수 : I_NAME]
  '관리팀',   -- [필수 : I_DEPT]
  '192.168.0.15',
  '1010',    -- [필수 : I_ISSU]
  'AB-9492',
  '',
  @O_MSG
);
SELECT @O_MSG;





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

CALL PRO_ALL_ADMIN(
  '4',       -- [필수 : I_TYPE]
  'admin',  -- [필수 : I_ID]
  '',
  '관리자',   -- [필수 : I_NAME]
  '관리팀',   -- [필수 : I_DEPT]
  '',
  '',
  '',
  '',
  @O_MSG
);
SELECT @O_MSG;





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





[프로시저 삭제] :

DROP PROCEDURE PRO_ALL_ADMIN;





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

SHOW PROCEDURE STATUS;

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


/***************************************************************
[로직 정리 : 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; -- [UMCS_ADMIN 테이블에서 등록, 수정, 삭제에서 다중 조회 체크 실시]





    -- [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_ADMIN
        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 : Type Data Is Null : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

       -- [리턴 테이블 반환]
       SELECT *
            FROM UMCS_ADMIN
       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), ']');


        -- [리턴 테이블 반환 : UMCS_ADMIN 테이블에 저장된 전체 데이터 조회 실시]
        SELECT *
            FROM UMCS_ADMIN
        WHERE TRIM(ADMIN_ID) LIKE CONCAT('%', CAST(I_ID AS CHAR), '%')
          AND TRIM(ADMIN_NAME) LIKE CONCAT('%', CAST(I_NAME AS CHAR), '%')
          AND TRIM(ADMIN_DEPART) LIKE CONCAT('%', CAST(I_DEPT AS CHAR), '%')
        ORDER BY TRIM(ADMIN_DEPART) DESC;


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

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

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

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


        -- [등록에 필요한 필수 인풋 값 널 체크 실시]
        IF I_TYPE IS NULL OR TRIM(I_TYPE) = '' OR LENGTH(I_TYPE) = 0 -- [타입]
            OR I_ID IS NULL OR TRIM(I_ID) = '' OR LENGTH(I_ID) = 0 -- [로그인 아이디]
            OR I_NAME IS NULL OR TRIM(I_NAME) = '' OR LENGTH(I_NAME) = 0 -- [이름]
            OR I_DEPT IS NULL OR TRIM(I_DEPT) = '' OR LENGTH(I_DEPT) = 0 -- [부서]
            OR I_ISSU IS NULL OR TRIM(I_ISSU) = '' OR LENGTH(I_ISSU) = 0 -- [관리자 권한]
            THEN

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

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

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

        END IF;


        -- [같은 로그인 아이디, 이름, 부서, 권한이 이미 등록되어 있는지 확인 실시]
        SET M_DEBUG = '3-2-1'; -- [디버깅 단계]

        SET M_REG_COUNT = (
            SELECT COUNT(*)
                FROM UMCS_ADMIN
            WHERE TRIM(ADMIN_ID) = TRIM(I_ID) -- [로그인 아이디]
              AND TRIM(ADMIN_DEPART) = TRIM(I_DEPT) -- [부서]
              AND TRIM(ADMIN_NAME) = TRIM(I_NAME) -- [이름]
              AND TRIM(ADMIN_ISSU) = TRIM(I_ISSU) -- [권한]
        );
        IF M_REG_COUNT > 0 -- [조회된 개수가 있는 경우]
            THEN

            -- [리턴 데이터 삽입]
            SET O_MSG = CONCAT('N : 이미 등록된 관리자가 있습니다. 수정 및 삭제를 수행해주세요. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

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

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

        END IF;


        -- [실제로 UMCS_ADMIN 테이블에 등록 수행 실시]
        SET M_DEBUG = '3-2-2'; -- [디버깅 단계]

        START TRANSACTION;
        INSERT INTO UMCS_ADMIN
            (ADMIN_ID, ADMIN_PWD, ADMIN_NAME, ADMIN_DEPART, ADMIN_IP, ADMIN_UPDT, ADMIN_ISSU, ADMIN_PSCO, ADMIN_MSG)
        VALUES (I_ID, I_PWD, I_NAME, I_DEPT, I_IP, CAST(M_DATE AS CHAR), I_ISSU, I_PSCO, I_MSG);
        COMMIT;


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 관리자 등록을 완료했습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


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


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

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

    ELSEIF TRIM(I_TYPE) = '3' THEN  -- [수정]

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


        -- [수정에 필요한 필수 인풋 값 널 체크 실시]
        IF I_TYPE IS NULL OR TRIM(I_TYPE) = '' OR LENGTH(I_TYPE) = 0 -- [타입]
            OR I_ID IS NULL OR TRIM(I_ID) = '' OR LENGTH(I_ID) = 0 -- [로그인 아이디]
            OR I_NAME IS NULL OR TRIM(I_NAME) = '' OR LENGTH(I_NAME) = 0 -- [이름]
            OR I_DEPT IS NULL OR TRIM(I_DEPT) = '' OR LENGTH(I_DEPT) = 0 -- [부서]
            OR I_ISSU IS NULL OR TRIM(I_ISSU) = '' OR LENGTH(I_ISSU) = 0 -- [권한]
            THEN

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

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

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

        END IF;


        -- [관리자 테이블에 등록되어 있는지 확인 실시]
        SET M_DEBUG = '3-3-1'; -- [디버깅 단계]

        SET M_REG_COUNT = (
            SELECT COUNT(*)
                FROM UMCS_ADMIN
            WHERE TRIM(ADMIN_ID) = TRIM(I_ID) -- [로그인 아이디]
              AND TRIM(ADMIN_DEPART) = TRIM(I_DEPT) -- [부서]
              AND TRIM(ADMIN_NAME) = TRIM(I_NAME) -- [이름]
        );
        IF M_REG_COUNT = 0 -- [조회된 개수가 없는 경우]
            THEN

            -- [리턴 데이터 삽입]
            SET O_MSG = CONCAT('N : 수정할 수 있는 조건에 부합하는 값이 없습니다. (ID, DEPT, NAME) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

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

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

        END IF;


        -- [실제로 UMCS_ADMIN 테이블에 등록된 데이터 수정 실시]
        SET M_DEBUG = '3-3-2'; -- [디버깅 단계]

        START TRANSACTION;
        UPDATE UMCS_ADMIN
        SET ADMIN_ID = I_ID, ADMIN_PWD = I_PWD, ADMIN_NAME = I_NAME, ADMIN_DEPART = I_DEPT, ADMIN_IP = I_IP, ADMIN_UPDT = CAST(M_DATE AS CHAR), ADMIN_ISSU = I_ISSU, ADMIN_PSCO = I_PSCO, ADMIN_MSG = I_MSG
        WHERE TRIM(ADMIN_ID) = TRIM(I_ID)
           AND TRIM(ADMIN_NAME) = TRIM(I_NAME)
           AND TRIM(ADMIN_DEPART) = TRIM(I_DEPT);
        COMMIT;


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 관리자 수정을 완료했습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


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


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

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

    ELSEIF TRIM(I_TYPE) = '4' THEN  -- [삭제]

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


        -- [삭제에 필요한 필수 인풋 값 널 체크 실시]
        IF I_TYPE IS NULL OR TRIM(I_TYPE) = '' OR LENGTH(I_TYPE) = 0 -- [타입]
            OR I_ID IS NULL OR TRIM(I_ID) = '' OR LENGTH(I_ID) = 0 -- [로그인 아이디]
            OR I_NAME IS NULL OR TRIM(I_NAME) = '' OR LENGTH(I_NAME) = 0 -- [이름]
            OR I_DEPT IS NULL OR TRIM(I_DEPT) = '' OR LENGTH(I_DEPT) = 0 -- [부서]
            THEN

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

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

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

        END IF;


        -- [관리자 테이블에 등록되어 있는지 확인 실시]
        SET M_DEBUG = '3-4-1'; -- [디버깅 단계]

        SET M_REG_COUNT = (
            SELECT COUNT(*)
                FROM UMCS_ADMIN
            WHERE TRIM(ADMIN_ID) = TRIM(I_ID) -- [로그인 아이디]
              AND TRIM(ADMIN_NAME) = TRIM(I_NAME) -- [이름]
              AND TRIM(ADMIN_DEPART) = TRIM(I_DEPT) -- [부서]
        );
        IF M_REG_COUNT = 0 -- [조회된 개수가 없는 경우]
            THEN

            -- [리턴 데이터 삽입]
            SET O_MSG = CONCAT('N : 삭제할 수 있는 조건에 부합하는 값이 없습니다. (ID, NAME, DEPT) : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');

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

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

        END IF;


        -- [실제로 UMCS_ADMIN 테이블에 등록된 데이터 삭제 실시]
        SET M_DEBUG = '3-4-2'; -- [디버깅 단계]

        START TRANSACTION;
        DELETE FROM UMCS_ADMIN
        WHERE TRIM(ADMIN_ID) = TRIM(I_ID)
           AND TRIM(ADMIN_NAME) = TRIM(I_NAME)
           AND TRIM(ADMIN_DEPART) = TRIM(I_DEPT);
        COMMIT;


        -- [리턴 데이터 삽입]
        SET O_MSG = CONCAT('Y : 관리자 삭제를 완료했습니다. : M_DEBUG : [', CAST(M_DEBUG AS CHAR), ']');


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


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

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

    ELSE

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

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


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


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

    END IF;


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

 

---

[결과 출력]

​

---