여러가지 에러 상황에 대한 Exception handling이 필수적이다.
여기에서는 MySQL Stored routine에서의 예외 처리를 한번 알아보고자 한다.
우선 MySQL의 예외처리는 DECLARE ... HANDLER 구문을 이용하여
각 예외 케이스의 이벤트가 발생하면 그 Handler가 작동하는 형태로 구현이 가능하다.
HANDLER 정의 구문
DECLARE handler_type HANDLER
FOR condition_value [, condition_value] ...
handler_statements
CONTINUE
| EXIT
SQLSTATE sqlstate_value
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code
| condition_name
Handler type
- CONTINUE 정의된 handler_statements를 실행하고, Stored routine의 마지막 실행 지점으로 다시 돌아간다.
- EXIT 정의된 handler_statements를 실행하고, 현재 Handler가 정의된 BEGIN ... END 블록을 벗어난다. (만약, 현재 Handler가 최상위 블록에 정의되었다면, 현재 Stored routine을 종료하게 된다.)
Condition value
- SQLSTATE
${sqlstate_value} Stored routine에서 코드를 실행하던 중 어떤 이벤트가 발생했을 때, 그 이벤트의 SQLSTATE 값을 체크해서 실행되는 Handler를 정의할 때 사용. - SQLWARNINGStored routine에서 코드를 실행하던 중 SQLWarning이 발생했을 때, 실행되는 Handler를 정의할 때 사용. SQLWARNING은 "01"로 시작되는SQLSTATE 를 표현하는 동의어와 같은 것이다.
- NOT FOUNDSELECT 쿼리 문의 결과 건수가 1건도 없거나, CURSOR의 레코드를 마지막까지 읽은 경우 실행되는 Handler를 정의할 때 사용. NOT FOUND는 "02"로 시작되는 SQLSTATE를 표현하는 동의어와 같은 것이다.
- SQLEXCEPTIONSQLWARNING과 NOT FOUND 그리고 "00"(정상 처리)으로 시작되는 SQLSTATE 이외의 모든 케이스를 의미하는 키워드이다.
- ${mysql_error_code}코드 실행 중 어떤 이벤트가 발생했을 때, SQLSTATE값이 아닌 MySQL의 ErrorNo 값을 비교해서 실행되는 Handler를 정의할 때 사용.
- ${condition_name}DECLARE ${condition_name} CONDITION FOR ... 구문을 이용하여 사용자 정의 CONDITION을 생성할 수 있는데, 그 이름을 여기에 명시해서 Handler를 정의할 때 사용. (자세한 설명은 매뉴얼 참조)
Condition value 정의시 주의사항
- Condition value는 콤마(",")를 이용하여 여러 개 동시에 나열 하는 것이 가능하다.
- Condition value에 SQLSTATE '00000' 또는 MySQL ErrorNo 0 은 정의해서는 안 된다. 이 두 개의 값은 모두 정상 처리를 의미하는 SQLSTATE와 MySQL ErrorNo 값이다.
- SQLSTATE와 ErrorNo에 대한 자세한 사항은 "MySQL ErrorNo와 SqlState 는 무엇을 의미할까?"를 참조
Handler statements
- 특정 이벤트가 발생했을 때, 그 이벤트에 대한 처리(Handling) 코드를 여기에 명시하며, Handler statements에는 단순히 명령문 하나만 명시될 수도 있으며, BEGIN ... END 블록으로 정의된 코드 블록이 명시될 수도 있다.
예제로 간단한 Handler 정의 문장을 살펴보자.
예제 1)
DECLARE CONTINUE HANDLER
FOR SQLEXCEPTION SET error_flag=1;
SQLException (Sql State가 "00", "01",
"02" 이외의
값으로 시작되는 에러)이 발생했을 때, error_flag 변수의 값을 1로 설정하고, 마지막 실행했던 Stored routine의 코드로 돌아가서 계속 실행(CONTINUE)
예제 2)
DECLARE EXIT HANDLER FOR
SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Error occurred - terminating';
END;
SQLException (Sql State가 "00", "01",
"02" 이외의
값으로 시작되는 에러)이 발생했을 때, ROLLBACK을 실행하고, SELECT ... 문장을 실행 후, 이 Handler가 실행된 코드 블록을 벗어나거나, 이 Handler 가 Stored routine의 최상위 블록에 정의된 경우에는 실행을 종료한다.
Stored routine에서 실행되는 SELECT 문장의 결과를 Console이나 Client로 즉시 출력된다.
(이 예제에서는 " Error occurred -
terminating "라는 값이
출력된다.)
예제 3)
DECLARE CONTINUE HANDER
FOR 1062 SELECT 'Duplicate key in
index';
MySQL ErrorNo가 1062인 이벤트가 발생했을 때, Console이나 Client에 "Duplicate key in index"라는 결과 셋을 출력하고, Stored routine의 원래 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.
예제 4)
DECLARE CONTINUE HANDER
FOR SQLSTATE '23000' SELECT 'Duplicate
key in index';
MySQL SqlState가 "23000"인 이벤트가 발생했을 때, Console이나 Client에 "Duplicate key in index"라는 결과 셋을 출력하고, Stored routine의 원래 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.
예제 5)
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET process_done=1;
SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다.
예제 6)
DECLARE CONTINUE HANDLER
FOR SQLSTATE '02000' SET process_done=1;
SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다. ("02000"는 NOT FOUND를 의미하는 SQLSTATE값이다.)
예제 7)
DECLARE CONTINUE HANDLER
FOR 1329 SET process_done=1;
SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 process_done 변수 값을 1로 설정하고 Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다. (1329는 NOT FOUND를 의미하는 MySQL ErrorNo값이다.)
예제 8)
DECLARE EXIT HANDLER FOR SQLWARNING,
SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Process terminated, Because error';
SHOW ERRORS;
SHOW WARNINGS;
END;
SQLWARNING 이나 SQLEXCEPTION이 발생하면, 지금까지의 데이터 변경을 모두 ROLLBACK하고, 에러와 경고 메시지를 출력하고 Stored routine을 종료한다.
