MySQL 입력용 프로시저 - 트랜젝션, 시퀀스, 롤백 사용

MySQL 프로시저

START TRANSACTION;

1. 서론

MySQL 프로시저를 사용하여 간단한 구조의 테이블에 INSERT를 해보고 TRANSACTION을 이용해 입력에 실패한 경우 입력을 ROLLBACK하고 오류에 대한 내용까지 저장해보도록 하겠습니다.

해당 글에서는 INSERT를 사용하지만 SELECT나 UPDATE, DELETE에 응용하셔서 사용하셔도 좋습니다.

추후 프로시저에 대한 내용도 자세하게 다룰 예정이지만 기초적인 구조를 알아가는데에 좋을 것 같습니다.

 

1.1. 사용환경

사용할 DB는 마리아DB입니다.

PK를 생성할 때 직접 pk를 생성하는 방식을 사용하기 때문에 내부적으로 코드의 중첩을 피하기 위해 시퀀스를 사용하는데 아직 MySQL은 시퀀스를 공식적으로 지원하지 않기 때문에 마리아 DB를 사용하였습니다.

 

1.2. 참고

내부적으로 pk를 생성할때 auto increment를 사용하지 않고 사용자 지정한 프로시저를 이용해서 직접 pk를 생성하도록 하겠습니다.

실제 업무를 진행하면서 MySQL과 마리아DB의 pk는 auto increment에 최적화되어있지만 로그용 테이블에 등에서 pk를 생성할 때 YYYMMDDHHIISS0001 ~ YYYMMDDHHIISS9999 등으로 년월일을 포함한 pk를 생성할 경우 날짜 범위를 이용한 검색에 용의할때가 있습니다.

혹은 시스템에 따라 별도 코드를 두는 경우도 있고 제일 중요한것은 대량의 데이터를 INSERT할 경우, auto_increment 에서 중복 에러가날 경우가 있기 때문에 자동 생성이 아닌 별도의 값을 가지는것이 추후 데이터 입력이나 관리에 유용합니다.

또한 제가 날짜를 저장할때는 대부분 YYYYMMDDHHIISS를 이용하여 별도로 입력을 합니다.

일반적으로 MySQL에서 제공하는 Date함수를 사용할 수 있지만 YYYY-MM-DD HH:II:SS 등의 형식은 일자 검색을 할 때 생각 이상으로 복잡한 상황을 처하는 경우가 발생하여 습관적으로 사용하게 된 방법이기에 참고바랍니다.

2. 테이블 DDL

2.1. 회원 테이블

CREATE TABLE `tbl_user` (
  `userCode` varchar(30) NOT NULL COMMENT '회원 코드',
  `userName` varchar(100) NOT NULL COMMENT '회원 명',
  `userGender` varchar(2) NOT NULL COMMENT '회원 성별 M:남자, W:여자',
  `insertDate` varchar(30) NOT NULL COMMENT '등록일자 YYYYMMDDHHIISS',
  PRIMARY KEY (`userCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

입력용으로 사용할 회원 테이블입니다.

pk로 지정된 userCode를 보시면 직접 생성한 pk를 사용할 것이기때문에 varchar 데이터 형식으로 지정된 것을 확인하실 수 있습니다.

 

2.2. 오류 로그용 테이블

CREATE TABLE `tbl_errorlog` (
  `errorCode` varchar(30) NOT NULL COMMENT '에러 코드',
  `errorPoint` varchar(100) NOT NULL COMMENT '오류 위치',
  `errorNumber` varchar(100) NOT NULL COMMENT '오류 번호',
  `errorState` varchar(100) NOT NULL COMMENT '오류 상태',
  `errorText` varchar(200) NOT NULL COMMENT '오류 메시지',
  PRIMARY KEY (`errorCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

프로시저 INSERT과정에서 문제가 발생할 경우 발생한 오류 내역을 저장할 오류 로그용 테이블입니다.

 

3. 시퀀스

pk 생성에 사용할 시퀀스 입니다.

3.1. 사용자 코드 생성용 시퀀스

CREATE OR REPLACE SEQUENCE `sq_usercode` start with 1 minvalue 1 maxvalue 9999 increment by 1 cache 1000 cycle ENGINE=InnoDB

 

 

3.2. 에러 코드 생성용 시퀀스

CREATE OR REPLACE SEQUENCE `sq_errorcode` start with 1 minvalue 1 maxvalue 9999 increment by 1 cache 1000 cycle ENGINE=InnoDB

 

 

4. 프로시저

CREATE DEFINER=`root`@`localhost` PROCEDURE `testdb`.`proc_insert_user`(
    IN `_userName` VARCHAR(30),			-- 입력값 회원 명
    IN `_userGender` VARCHAR(2),		-- 입력값 회원 성별
    OUT `_userCode` VARCHAR(20),		-- 생성한 userCode 값 반환
    OUT `_result` INT				-- 프로시저 성공여부 0:실패, 1:성공
)
    COMMENT '회원 등록'
BEGIN
    DECLARE _errorCode VARCHAR(30);		-- tbl_errorlog 테이블 오류 코드
    DECLARE _errState VARCHAR(255);		-- MariaDB 오류 상태
    DECLARE _errCode INT;			-- MariaDB 오류 코드
    DECLARE _errMsg VARCHAR(255);		-- MariaDB 오류 메시지

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- TRANSACTION 실패할 경우 오류 사항 수집
        GET DIAGNOSTICS CONDITION 1
        	_errState = RETURNED_SQLSTATE,
            _errCode = MYSQL_ERRNO,
            _errMsg = MESSAGE_TEXT;

        ROLLBACK;
       
    	SET _errorCode = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i'), LPAD(CONVERT(nextval(sq_errorCode), CHAR), 4, '0'));
   
        INSERT INTO tbl_errorlog (errorCode, errorPoint, errorNumber, errorState, errorText)
        VALUES (_errorCode, 'proc_insert_user', _errCode, _errState, _errMsg);
        SET _result = 0;
    END;

   -- TRANSACTION 시작
    START TRANSACTION;
	
    -- userCode pk 생성 YYYYMMDDHHII0001 ~ YYYYMMDDHHII9999
    SET _userCode = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i'), LPAD(CONVERT(nextval(sq_userCode), CHAR), 4, '0'));

    -- tbl_user 테이블 입력
    INSERT INTO tbl_user 
    (userCode, userName, userGender, insertDate) 
    VALUES 
    (_userCode, _userName, _userGender, DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'));

    COMMIT;
   
   -- TRANSACTION 이 정상 완료될 경우, 결과값 반환
    SET _result = 1;

END

 

보시면 구조적으로 뒤에서 설명하지만 오류에 대한 내역을 저장하는 부분이 상단에 존재하고 그에 대한 ROLLBACK 또 한 위쪽에 존재하기에 프로시저가 아래로 진행을 하다 예외상황이 발생했을 경우 위로 다시 올라가는 구조를 취하고있습니다.

보기에는 불편할 수 있지만 실제 프로시저를 사용할때 하단 쿼리 부분이 늘어나고 줄어들 수 있기 때문에 고정적으로 사용하는 폼인 예외사항에 대해서는 위쪽으로 올린것입니다.

사용에 따라 제일 하단으로 옮기셔도 좋습니다.

 

전체 프로시저 구문은 다음과 같고 각 구문에 대한 설명은 다음과 같습니다.

 

4.1. 프로시저 설명

4.1.1. CRATE

CREATE DEFINER=`root`@`localhost` PROCEDURE `testdb`.`proc_insert_user`

 

DEFINER : 프로시저에 대한 실행 권한 컨텍스트로 root 사용자의 localhost 접근을 뜻합니다.

PROCEDURE `testdb`.`proc_insert_user` : 프로시저가 testdb DB에 proc_insert_user 라는 명칭으로 등록되었음을 뜻합니다.

 

4.1.2. 입/출 변수 선언 IN, OUT, INOUT

(
    IN `_userName` VARCHAR(30),			-- 입력값 회원 명
    IN `_userGender` VARCHAR(2),		-- 입력값 회원 성별
    OUT `_userCode` VARCHAR(20),		-- 생성한 userCode 값 반환
    OUT `_result` INT				-- 프로시저 성공여부 0:실패, 1:성공
)

 

프로시저로 들어오는 변수와 나가는 변수에 대한 정리입니다.

IN 은 프로시저로 들어오는 변수를 뜻합니다. 콤마 단위로 쪼개어 들어오는 입력값이며 각 입력값에 대한 데이터형식을 정의합니다.

OUT은 프로시저에서 나가는 변수를 뜻합니다. 마찬가지로 나가는 값에 대한 데이터형식을 정의합니다.

별도로 사용하지는 않았지만 INOUT 이라는 선언도 존재합니다.

해당 구문은 들어오는 값을 그대로 내보낼때 사용하는 선언인데 변수에 대한 재활용을 통해 종류는 줄일수 있지만 사용에 있어서 햇갈릴 경우가 있기 때문에 제외했습니다.

 

4.1.3. 실행문

BEGIN
```
END;

 

BEGIN ~ END 구문은 실행문으로 프로시저 내부에서 기능을 구현할 때 해당 구문안에서 작성합니다.

 

4.1.4. 내부 변수 선언

DECLARE _errorCode VARCHAR(30);		-- tbl_errorlog 테이블 오류 코드
DECLARE _errState VARCHAR(255);		-- MariaDB 오류 상태
DECLARE _errCode INT;			-- MariaDB 오류 코드
DECLARE _errMsg VARCHAR(255);		-- MariaDB 오류 메시지

 

DECLARE는 선언문 입니다.

여기에서는 프로시저 내부에서 사용할 변수들을 선언하기 위해 사용되었습니다.

 

4.1.5. SQLEXCEPTION 예외처리

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
```
END;

 

여기서 사용하는 DECLARE는 SQLEXCEPTION 즉 SQL 예외 사항이 발생할 경우 하단의 BEGIN ~ END; 문을 실행하고 프로시저를 종료시킵니다.

따라서 프로시저 내부에서 오류가 발생할 경우 해당 구문이 실행 된 후 종료가 되게됩니다.

해당 핸들러에 대한 자세한 내용은 하단 공식 홈페이지에서 확인하실 수 있습니다.

 

https://dev.mysql.com/doc/refman/8.4/en/declare-handler.html

MySQL :: MySQL 8.4 Reference Manual :: 15.6.7.2 DECLARE ... HANDLER Statement

 

4.1.6. SQLEXCEPTION 메시지 반환

-- TRANSACTION 실패할 경우 오류 사항 수집
GET DIAGNOSTICS CONDITION 1
	_errState = RETURNED_SQLSTATE,
	_errCode = MYSQL_ERRNO,
	_errMsg = MESSAGE_TEXT;

 

GET DIAGNOSTICS 구문을 이용해 발생한 오류 사항에 대한 정보를 가져옵니다.

해당 내용에 대한 자세한 설명은 해당 공식 홈페이지에서 확인하실 수 있습니다.

 

https://dev.mysql.com/doc/refman/8.4/en/get-diagnostics.html

MySQL :: MySQL 8.4 Reference Manual :: 15.6.7.3 GET DIAGNOSTICS Statement

 

4.1.7. ROLLBACK

ROLLBACK;

해당 구문은 START TRANSACTION; 에 대한 실행문을 취소하는 구문인데 위에서 설명했다시피 예외상황이 발생했을 경우 실행하는 고정 구문이 위쪽에 존재하기 때문에 그에 따라 ROLLBACK 부분도 상단으로 올라가있습니다.

 

 

4.1.8. PK 생성

SET _errorCode = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i'), LPAD(CONVERT(nextval(sq_errorCode), CHAR), 4, '0'));

 

해당 구문은 상단에 정의한 _errorCode 변수에 날짜 형식과 앞을 0으로 채운 4자리 시퀀스 코드를 합성해서 pk를 생성하는 구문입니다.

따라서 YYYYMMDDHHII0001 ~ YYYYMMDDHHII9999 의 값을 가집니다.

실제 값은 2024081215290001 와 값습니다.

 

4.1.9. CONCAT 함수

-- VAL1VAL2
CONCAT('VAL1', 'VAL2');

CONCAT 함수는 함수 내부의 문자열을 합쳐주는 기능을 합니다.

다음과 같이 VAL1, VAL2 값이 들어있다면 VAL1VAL2 라는 값을 반환합니다.

 

4.1.10. DATE_FORMAT 함수

DATE_FORMAT(NOW(), '%Y%m%d%H%i')

 

DATE_FORMAT 함수는 날짜를 생성할때 사용하는 함수입니다.

해당 함수 앞쪽 변수인 NOW()는 현재 DB 시스템 날짜를 사용한다는것이고 두번째 변수인 '%Y%m%d%H%i'는 202408121518로 년월일시분 의 날짜 포멧을 뜻합니다.

 

4.1.11. LPAD 함수

LPAD(CONVERT(nextval(sq_errorCode), CHAR), 4, '0'))

 

LPAD는 왼쪽부터 문자열을 채울때 사용하는 함수입니다.

첫번째 변수에 입력값

두번째 변수에 몇자리까지 채울것인지

세번째 변수에 무엇으로 채울것인지에 대한 값입니다.

따라서

LPAD('53', CHAR), 4, '0'))		-- =0053
LPAD('DBM', CHAR), 4, '0'))		-- =0DBM
LPAD('DBMS', CHAR), 4, '0'))		-- =DBMS

 

다음과 같이 첫번째 변수의 값이 두번째 변수의 값보다 작을 경우 좌측에 세번째 변수의 값을 더해줍니다.

 

4.1.12. CONVERT 함수

CONVERT(nextval(sq_errorCode), CHAR)

 

CONVERT 함수는 첫번째 변수 값을 두번째 변수의 데이터 형식에 맞춰서 데이터의 형식을 변경시킵니다.

단순히 데이터 타입만을 변경하는 것만이 아닌 인코딩 방식에 대한 변경도 가능하지만 여기에서는 데이터 형식을 변경할때만 사용합니다.

 

CONVERT(12, CHAR)		-- = '12'
CONVERT('123', INTEGER)		-- = 123

 

다음과 같이 데이터 형식을 변경할 수 있고 자세한 내용은 하단의 공식홈페이지를 참조합니다.

 

https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html#function_convert

MySQL :: MySQL 8.4 Reference Manual :: 14.10 Cast Functions and Operators

 

4.1.13. NETXTVAL 함수

nextval(sq_errorCode)

 

해당 함수는 시퀀스에서 값을 호출할 때 사용합니다.

상단에 에러코드 생성용 시퀀스를 DB에 등록하셨다면 사용이 가능합니다.

 

 

4.1.14. tbl_errorlog 입력

INSERT INTO tbl_errorlog (errorCode, errorPoint, errorNumber, errorState, errorText)
        VALUES (_errorCode, 'proc_insert_user', _errCode, _errState, _errMsg);

 

에러 테이블에 방금 생성한 errorCode와 MariaDB의 SQL EXCEPTION 내역을 입력합니다.

 

4.1.15. 결과값 지정

SET _result = 0;

 

상단에 반환할 변수로 지정되어있는 _result가 0일 경우 오류가 발생했다고 정의 했으므로 4.1.5. SQLEXCEPTION 예외처리 항목에서 설명 했다시피 프로시저가 종료되므로 결과값을 입력합니다.

 

4.1.16. START TRANSACTION

START TRANSACTION;
```
COMMIT;

 

트랜잭션의 시작을 선언하는 구문입니다.

해당 START TRANSACTION; 부터 COMMIT; 부분 안의 내용에서 트랜잭션이 실행되며 예외상황이 발생할 경우 ROLLBACK을 이용하여 실행문 안의 INSERT, UPDATE, DELETE에 대한 실행을 취소할 수 있습니다.

 

그 아래의 내용은 위에 설명한 tbl_errorLog 테이블 입력에 대한 내용의 반복이기 때문에 생략하도록 하겠습니다.

 

5. 프로시저 실행

call proc_insert_user("홍길동", "M", @_userCode, @_result);
select @_userCode as userCode, @_result as result;

 

프로시저를 실행할때에는 프로시저 명 앞에 CALL 이라는 선언을 하고 사용합니다.

현재 생성한 프로시저는 두개의 값을 입력 받고 두개의 값을 반환하기 때문에 프로시저를 선언 한 후 두개 값을 입력하고 두개 값을 반환 받습니다.

반환 받은 값을 표출하기 위해 select 문을 사용했고 select 문안에서 @변수는 직접 표현할 수 없기 때문에 as의 별칭을 사용했습니다.

 

5.1. 실제 사용

5.1.1. 프로시저 정상 실행

프로시저 실행

DBeaver를 사용하여 프로시저를 호출하고 반환 값을 받아 표출한 내용입니다.

userCode로 생성한 pk값을 반환 받고 result에서 1을 확인한 후 tbl_user 테이블을 확인하면 해당 pk값을 가진 데이터가 생성된 것을 확인할 수 있습니다.

 

5.1.2. 잘못된 변수 입력 시

프로시저 오류1

SQL Error [1406] [22001]: (conn=8) Data too long for column '_userGender' at row 1

 

다음은 프로시저에서 IN으로 선언한 변수값과 일치하지 않는 데이터 길이의 변수를 입력할 때 반환되는 오류입니다.

두번째 변수인 _userGender 값을 varchar(2)로 설정했는데 글길이 3자리로 입력하여 반환된 메시지입니다.

 

5.1.3. 프로시저 내부 오류 발생 시

내부적으로 오류를 발생시키기 위해 일부 프로시저 변수를 수정하였습니다.

CREATE DEFINER=`root`@`localhost` PROCEDURE `testdb`.`proc_insert_user`(
    IN `_userName` VARCHAR(30),			-- 입력값 회원 명
    IN `_userGender` VARCHAR(3),		-- 입력값 회원 성별
    OUT `_userCode` VARCHAR(20),		-- 생성한 userCode 값 반환
    OUT `_result` INT				-- 프로시저 성공여부 0:실패, 1:성공
)

상단 입력 변수의 _userGender 에대해 데이터 길이를 3으로 변경하였습니다.

tbl_user 테이블의 userGender 필드의 데이터 길이는 2이므로 해당 값으로 INSERT 문이 실행된다면 오류를 반환하게 됩니다.

 

프로시저 오류 결과2

 

프로시저를 변경한 후 실행할 경우 userCode 값 자체는 실행되기 떄문에 값을 반환하지만 result 값을 보면 프로시저의 실패를 뜻하는 0을 반환합니다.

 

프로시저 오류 결과3

0을 반환한것을 확인하였으면 tbl_errorlog 테이블을 확인합니다.

의도한대로 userGender에 입력한 값이 너무 길다는

Data too long for column 'userGender' at row 1

 

오류를 확인하실 수 있습니다.

 

 

5. 결론

프로시저를 사용하는데에 있어 익숙해지는것은 힘들지만 사용방법만 숙지하면 프로그램단과 DB단을 분리하여 더 효율적인 개발이 가능합니다.

특히 근래에 MSA등에 대한 개발론에 대해 각 프로그램단이 쪼개지고 있으므로 DB에 대한 접근이 파편화 되면서 더더욱 필요성을 느끼고 있습니다.