[MySQL / Stored Procedure] 에러 핸들링 시리즈
학생이 강의를 수강하는 모델
테스트 DB와 테이블을 아래와 같이 생성합니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | CREATE DATABASE exception_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE exception_test; /*------------------------------------------------------------- Create User Tables ------------------------------------------------------------- */ CREATE TABLE `student` ( `student_id` INT NOT NULL AUTO_INCREMENT COMMENT '학생 ID', `login_email` VARCHAR(128) NOT NULL COMMENT '로그인 이메일', PRIMARY KEY (`student_id`), UNIQUE INDEX `uix-student-login_email` (`login_email` ASC)) ENGINE = InnoDB COMMENT = '학생'; CREATE TABLE `lecture` ( `lecture_id` INT NOT NULL AUTO_INCREMENT COMMENT '강의 ID', PRIMARY KEY (`lecture_id`)) ENGINE = InnoDB COMMENT = '강의'; CREATE TABLE `attending` ( `student_id` INT NOT NULL COMMENT '학생 ID', `lecture_id` INT NOT NULL COMMENT '강의 ID', PRIMARY KEY (`student_id`, `lecture_id`), INDEX `nix-attending-lecture_id` (`lecture_id` ASC), CONSTRAINT `fk-student-attending` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`), CONSTRAINT `fk-lecture-attending` FOREIGN KEY (`lecture_id`) REFERENCES `lecture` (`lecture_id`)) ENGINE = InnoDB COMMENT = '수강'; /*------------------------------------------------------------- Create Log Tables ------------------------------------------------------------- */ CREATE TABLE `exception_log` ( `exception_log_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '예외 ID', `proc_name` varchar(100) NOT NULL COMMENT '프로시저 이름', `proc_step` tinyint(3) unsigned NOT NULL COMMENT '프로서저 안에서 예외가 발생한 단계', `exception_no` int(10) unsigned NOT NULL COMMENT '예외 ID', `call_stack` text COMMENT '프로시저 호출 파라미터', `proc_call_date` datetime NOT NULL COMMENT '프로시저 호출 일자', `log_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '로그 적재 일자', PRIMARY KEY (`exception_log_id`) ) ENGINE=InnoDB COMMENT='예외 로그'; CREATE TABLE `error_log` ( `error_log_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '에러 로그 ID', `proc_name` varchar(100) NOT NULL COMMENT '프로시저 이름', `proc_step` tinyint(3) unsigned NOT NULL COMMENT '프로서저 안에서 예외가 발생한 단계', `sql_state` varchar(5) NOT NULL COMMENT 'SQLSTATE', `error_no` int(11) NOT NULL COMMENT '에러 번호', `error_msg` text NOT NULL COMMENT '에러 메세지', `call_stack` text COMMENT '프로시저 호출 파라미터', `proc_call_date` datetime NOT NULL COMMENT '프로시저 호출 일자', `log_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '로그 적재 일자', PRIMARY KEY (`error_log_id`) ) ENGINE=InnoDB COMMENT='DB 런타임 에러 로그'; | cs |
잠깐만!!!
`error_log`와 `exception_log`를 따로 만들었는데, error와 exception은 다른가요?
먼저 제 주관적인 정의라는 점을 밝힙니다.
exception은 요청한 작업을 정상적으로 완료하지 못하는 상황을 말합니다.
단, 개발자의 예상 범위 안에서 발생한다는 조건이 붙습니다.
error는 DBMS가 SQL문을 처리하는 과정에서 시스템적으로 발생하는 SQLEXCEPTION 으로 개발자가 예상하지 못한 것입니다.
그렇다면 Constraint 위반에 의한 SQLEXCEPTION은 error 일까요?
개발자가 SQLEXCEPTION 이 발생한다고 예상했다면 exception 이고, 그렇지 못했다면 error 입니다.
그리고 이 글에서는 Constraint 위반을 exception 으로 처리하는 방법을 다룹니다.
아래와 같이 `student` 테이블과 `lecture` 테이블에 각각 1개의 row를 insert 합니다.
이제 1명의 학생과 1개의 강의가 등록 됩니다.
1 2 3 4 5 | INSERT student (login_email) VALUES ('apple@test.com'); INSERT lecture (lecture_id) VALUES (DEFAULT); | cs |
첫 번째 예제 : 학생 추가하기
목표 : 이미 등록된 로그인 이메일 주소이면 예외 처리한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | -- ----------------------------------------------------- -- PROCEDURE usp_add_student -- ----------------------------------------------------- USE exception_test; DELIMITER $$ DROP PROCEDURE IF EXISTS `usp_add_student`$$ CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_student`( IN pi_vch_login_email varchar(128) -- 로그인 이메일 , IN pi_dt5_now datetime(0) -- 현재 서버 시각 , OUT po_int_return int -- 리턴 값 ) DETERMINISTIC SQL SECURITY DEFINER CONTAINS SQL COMMENT ' author : doeyull.kim e-mail : purumae@gmail.com created date : 2018-06-21 description : 학생을 추가합니다. parameter : IN pi_vch_login_email varchar(128) -- 로그인 이메일 , IN pi_dt5_now datetime(0) -- 현재 서버 시각 , OUT po_int_return int -- 리턴 값 result set : student_id / int UNSIGNED / 학생 ID return value : 0 = 에러가 없습니다. -1 = 예상하지 않은 런 타임 오류가 발생하였습니다. 10001 = 이미 존재하는 "로그인 이메일" 입니다. usage : SET @pi_vch_login_email = ; SET @pi_dt5_now = ; CALL usp_add_student (@pi_vch_login_email, @pi_dt5_now, @po_int_return); SELECT @po_int_return; ' proc_body: BEGIN DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_add_student'; DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0; DECLARE v_txt_call_stack text; DECLARE v_bit_write_exception_log boolean DEFAULT FALSE; DECLARE v_vch_sql_state varchar(5); DECLARE v_int_error_no int; DECLARE v_txt_error_msg text; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END; DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 v_vch_sql_state = RETURNED_SQLSTATE , v_int_error_no = MYSQL_ERRNO , v_txt_error_msg = MESSAGE_TEXT; ROLLBACK; SET v_txt_call_stack = CONCAT('{"pi_vch_login_email":', IF(pi_vch_login_email IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_login_email, '"', '\\\"'), '"')) , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"')) , '}' ); -- 런 타임 에러 중 논리 에러로 분류할 항목을 필터링 IF v_int_error_no = 1062 AND v_txt_error_msg LIKE '%uix-student-login_email%' THEN SET po_int_return = 10001; SET v_bit_write_exception_log = FALSE; END IF; IF v_bit_write_exception_log THEN -- exception_log table에 기록하는 논리 에러인 경우 INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date) VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0)); ELSEIF po_int_return = 0 OR po_int_return IS NULL THEN -- exception으로 분류하지 않는 그 밖의 런 타임 에러인 경우 INSERT error_log (proc_name, proc_step, sql_state, error_no, error_msg, call_stack, proc_call_date, log_date) VALUES (v_vch_proc_name, v_iny_proc_step, v_vch_sql_state, v_int_error_no, v_txt_error_msg, v_txt_call_stack, pi_dt5_now, NOW(0)); SET po_int_return = -1; RESIGNAL; END IF; END; SET v_iny_proc_step = 1; INSERT student (login_email) VALUES (pi_vch_login_email); SET v_iny_proc_step = 2; SELECT LAST_INSERT_ID() AS student_id; SET po_int_return = 0; END proc_body$$ DELIMITER ; | cs |
86행 ~ 93행이 실제로 `student` 테이블에 insert 하고 채번한 값을 리턴하는, 이 Stored Procedure 의 실질적 body 입니다.
login_email 중복을 확인하기 위해 별도의 코드를 작성 하지 않았다는 점에 주목할 수 있습니다.
86 87 88 89 90 91 92 93 | SET v_iny_proc_step = 1; INSERT student (login_email) VALUES (pi_vch_login_email); SET v_iny_proc_step = 2; SELECT LAST_INSERT_ID() AS student_id; | cs |
만약 이미 등록된 로그인 이메일 주소라면 SQLEXCEPTION 이 발생하므로, 67행 ~ 70행의 코드로 캐치할 수 있습니다.
즉, 중복 키 에러인 1062 에러가 발생 & 위반한 Constraint의 이름인 `uix-student-login_email` 이 에러 메세지에 포함
67 68 69 70 | IF v_int_error_no = 1062 AND v_txt_error_msg LIKE '%uix-student-login_email%' THEN SET po_int_return = 10001; SET v_bit_write_exception_log = FALSE; END IF; | cs |
이 예외가 발생했을 때, 68행에서 output parameter 인 po_int_return 값에 이 예외에 대한 고유 ID (10001)를 넣어 줍니다.
그리고 10001번 예외에 대한 정의는 주석에 아래와 같이 적어 줍니다. (31행 ~ 34행)
31 32 33 34 | return value : 0 = 에러가 없습니다. -1 = 예상하지 않은 런 타임 오류가 발생하였습니다. 10001 = 이미 존재하는 "로그인 이메일" 입니다. | cs |
마지막으로 69행에서, 이 예외를 `exception_log` 테이블에 기록할 지 여부를 결정합니다.
유저가 중복 이메일을 입력하는 것은 디버깅이 필요한 예외가 아니기 때문에, 굳이 로깅하지 않기로 합니다. (FALSE)
Stored Procedure 를 아래와 같이 실행합니다.
존재하는 이메일 주소인 apple@test.com이 입력되었기 때문에 10001을 리턴하는 것을 확인할 수 있습니다.
1 2 3 4 5 6 | SET @pi_vch_login_email = 'apple@test.com'; SET @pi_dt5_now = NOW(0); CALL usp_add_student (@pi_vch_login_email, @pi_dt5_now, @po_int_return); SELECT @po_int_return; | cs |
두 번째 예제 : 수강하기
목표 :
1. 중복 수강이면 예외 처리한다.
2. 학생이 존재하지 않으면 예외 처리한다.
3. 강의가 존재하지 않으면 예외 처리한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | -- ----------------------------------------------------- -- PROCEDURE usp_add_attending -- ----------------------------------------------------- USE exception_test; DELIMITER $$ DROP PROCEDURE IF EXISTS `usp_add_attending`$$ CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_attending`( IN pi_int_student_id int -- 학생 ID , IN pi_int_lecture_id int -- 강의 ID , IN pi_dt5_now datetime(0) -- 현재 서버 시각 , OUT po_int_return int -- 리턴 값 ) DETERMINISTIC SQL SECURITY DEFINER CONTAINS SQL COMMENT ' author : doeyull.kim e-mail : purumae@gmail.com created date : 2018-06-21 description : 학생이 강의를 수강합니다. parameter : IN pi_int_student_id int -- 학생 ID , IN pi_int_lecture_id int -- 강의 ID , IN pi_dt5_now datetime(0) -- 현재 서버 시각 , OUT po_int_return int -- 리턴 값 return value : 0 = 에러가 없습니다. -1 = 예상하지 않은 런 타임 오류가 발생하였습니다. 10002 = 존재하지 않는 학생입니다. 10003 = 존재하지 않는 강의입니다. 10004 = 이미 수강했습니다. usage : SET @pi_int_student_id = ; SET @pi_int_lecture_id = ; SET @pi_dt5_now = ; CALL usp_add_attending (@pi_int_student_id, @pi_int_lecture_id, @pi_dt5_now, @po_int_return); SELECT @po_int_return; ' proc_body: BEGIN DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_add_attending'; DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0; DECLARE v_txt_call_stack text; DECLARE v_bit_write_exception_log boolean DEFAULT FALSE; DECLARE v_vch_sql_state varchar(5); DECLARE v_int_error_no int; DECLARE v_txt_error_msg text; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END; DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 v_vch_sql_state = RETURNED_SQLSTATE , v_int_error_no = MYSQL_ERRNO , v_txt_error_msg = MESSAGE_TEXT; ROLLBACK; SET v_txt_call_stack = CONCAT('{"pi_int_student_id":', IF(pi_int_student_id IS NULL, 'null', pi_int_student_id) , ',"pi_int_lecture_id":', IF(pi_int_lecture_id IS NULL, 'null', pi_int_lecture_id) , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"')) , '}' ); -- 런 타임 에러 중 논리 에러로 분류할 항목을 필터링 IF v_int_error_no = 1452 AND v_txt_error_msg LIKE '%fk-student-attending%' THEN SET po_int_return = 10002; SET v_bit_write_exception_log = TRUE; ELSEIF v_int_error_no = 1452 AND v_txt_error_msg LIKE '%fk-lecture-attending%' THEN SET po_int_return = 10003; SET v_bit_write_exception_log = TRUE; ELSEIF v_int_error_no = 1062 AND v_txt_error_msg LIKE '%PRIMARY%' THEN SET po_int_return = 10004; SET v_bit_write_exception_log = TRUE; END IF; IF v_bit_write_exception_log THEN -- exception_log table에 기록하는 예외인 경우 INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date) VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0)); ELSEIF po_int_return = 0 OR po_int_return IS NULL THEN -- exception으로 분류하지 않는 그 밖의 런 타임 에러인 경우 INSERT error_log (proc_name, proc_step, sql_state, error_no, error_msg, call_stack, proc_call_date, log_date) VALUES (v_vch_proc_name, v_iny_proc_step, v_vch_sql_state, v_int_error_no, v_txt_error_msg, v_txt_call_stack, pi_dt5_now, NOW(0)); SET po_int_return = -1; RESIGNAL; END IF; END; SET v_iny_proc_step = 1; INSERT attending (student_id, lecture_id) VALUES (pi_int_student_id, pi_int_lecture_id); SET po_int_return = 0; END proc_body$$ DELIMITER ; | cs |
`attending` 테이블에 insert 할 때...
1. 존재하지 않는 학생이면 부모인 `student` 테이블에 참조할 레코드가 없으므로, 1452 에러 발생 & fk-student-attending 이 메세지에 포함
2. 존재하지 않는 강의이면 부모인 `lecture` 테이블에 참조할 레코드가 없으므로, 1452 에러 발생 & fk-lecture-attending 이 메세지에 포함
3. 이미 수강한 강의라면 Primary Key 중복이므로, 1062 에러 발생 & PRIMARY 가 메세지에 포함
71 72 73 74 75 76 77 78 79 80 81 82 | IF v_int_error_no = 1452 AND v_txt_error_msg LIKE '%fk-student-attending%' THEN SET po_int_return = 10002; SET v_bit_write_exception_log = TRUE; ELSEIF v_int_error_no = 1452 AND v_txt_error_msg LIKE '%fk-lecture-attending%' THEN SET po_int_return = 10003; SET v_bit_write_exception_log = TRUE; ELSEIF v_int_error_no = 1062 AND v_txt_error_msg LIKE '%PRIMARY%' THEN SET po_int_return = 10004; SET v_bit_write_exception_log = TRUE; END IF; | cs |
각각의 예외를 정의하는 고유값 10002, 10003, 10004를 po_int_return 에 할당합니다.
그리고 이번에는 예외가 발생한 것을 로깅합니다. (SET v_bit_write_exception_log = TRUE)
아래 84행의 IF문 분기에 의해 예외가 `exception_log` 에 기록됩니다.
84 85 86 . . | IF v_bit_write_exception_log THEN -- exception_log table에 기록하는 예외인 경우 INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date) VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0)); | cs |
Stored Procedure 를 아래와 같이 실행합니다.
1. 첫 번째 실행에서는 정상적으로 실행되고, 두 번째 실행에서는 중복 수강 예외인 10004를 리턴합니다.
1 2 3 4 5 6 7 | SET @pi_int_student_id = 1; SET @pi_int_lecture_id = 1; SET @pi_dt5_now = NOW(0); CALL usp_add_attending (@pi_int_student_id, @pi_int_lecture_id, @pi_dt5_now, @po_int_return); SELECT @po_int_return; | cs |
위 SP를 두 번 실행한 후, `exception_log` 테이블을 조회한 결과입니다.
2. pi_int_student_id 에 2를 입력하여, 존재하지 않는 학생에 대한 예외를 테스트합니다.
1 2 3 4 5 6 7 | SET @pi_int_student_id = 2; SET @pi_int_lecture_id = 1; SET @pi_dt5_now = NOW(0); CALL usp_add_attending (@pi_int_student_id, @pi_int_lecture_id, @pi_dt5_now, @po_int_return); SELECT @po_int_return; | cs |
10002를 리턴하고, `exception_log` 테이블에 다음과 같이 로깅됩니다.
3. pi_int_lecture_id 에 2를 입력하여, 존재하지 않는 강의에 대한 예외를 테스트합니다.
1 2 3 4 5 | SET @pi_int_student_id = 1; SET @pi_int_lecture_id = 2; SET @pi_dt5_now = NOW(0); CALL usp_add_attending (@pi_int_student_id, @pi_int_lecture_id, @pi_dt5_now, @po_int_return); | cs |
10003을 리턴하고, `exception_log` 테이블에 다음과 같이 로깅됩니다.
다음 글에서는 에러 핸들링 최종화.. SIGNAL 문을 사용해서 예외를 처리하는 방법을 소개하겠습니다.
'MySQL > Stored Procedure' 카테고리의 다른 글
| [MySQL / Stored Procedure] 에러 핸들링 (5) SIGNAL (0) | 2018.06.27 |
|---|---|
| [MySQL / Stored Procedure] 에러 핸들링 (3) CONSTRAINT (上) (0) | 2018.06.20 |
| [MySQL / Stored Procedure] 에러 핸들링 (2) Error Logging (2) | 2018.06.18 |
| [MySQL / Stored Procedure] 에러 핸들링 (1) DECLARE ... HANDLER (0) | 2018.06.15 |
| [MySQL/Stored Procedure] 접근 제어 설정 (SQL SECURITY) (0) | 2018.06.04 |
