[Stored Procedure] 12. Stored Procedure 의 MySQL 에러 처리

요약: 이번 강좌는 저장 프로시저에서 예외나 에러를 만났을 때 처리하기 위한 MySQL handler를 사용하는 방법에 대해서 배워 볼 것이다.

저장 프로시저 내부에서 에러가 발생했을 때, 실행중인 현재 코드 단락에서 계속 실행하거나 종료하는 것 처럼 적당하게 처리하는 것이 중요하며, 의미 있는 에러메세지를 부여해야 한다.

MySQL은 경고나 예외 같은 일반적인 조건 부터 특정한 조건(특정한 에러 코드) 까지 처리하는 handler를 정의하는데 쉬운 방법을 제공한다.

 

handler 선언

handler를 선언하기 위해서 아래 처럼 DECLARE HANDLER문을 사용할 수 있다.

DECLARE action HANDLER FOR condition_value statement;

만약 조건이 condition_value의 값과 일치하다면, MySQL은 statement를 실행하고, 현재 코드 단락을 계속 실행하거나 빠져나가도록 한다.

action은 아래의 값 들 중 하나를 가진다.

- CONTINUE : 코드 단락으로 둘러 싸여 있는(BEGIN ... END) 부분의 실행을 계속한다.
- EXIT : 코드 단락으로 둘러 싸여 있는 부분에서 실행되면 handler는 선언되어 지고

condition_value는 특정한 조건이나 핸들러를 시작하는 조건의 클래스를 지정한다. condition_value는 아래 값 들 중 하나를 가진다.

- MySQL 에러 코드
- 표준 SQLSTATE 값. 아니면 SQLSTATE 값의 클래스의 shorthand SQLWARNING, NOTFOUND, SQLEXCEPTION 조건이 될 수 있다.
- MySQL 에러 코드 나 SQLSTATE 값과 연관되어 있는 조건

statement 는 간단한 statement 이거나 BEGIN END 키워드로 싸여있는 혼합된 statement 일 수 있다.

 

MySQL 에러 처리 예제

handler를 선언하는 몇 개의 예제를 살펴보자.

아래 handler는 에러가 발생하면 has_error 변수의 값을 1로 설정하고 다음 내용을 실행하는 의미를 가지고 있다.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

아래는 case에서 어떤 에러가 발생하면, 이전 연산자로 되돌아가고, 에러 메시지를 발행하고, 현재 코드 단락을 빠져나가는 의미를 가지는 handler이다. 만약 저장 프로시저의 BEGIN END 부분 내부에서 선언이 되어 있다면, 즉시 저장 프로시저를 종료할 것 이다.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

아래 handler는 만약 SELECT INTO 문 이나 cursor의 케이스인 경우에 더 이상 가져올 행이 없다면 no_row_found 변수의 값을 1로 설정하고 계속 실행하는 의미를 가지는 handler 이다.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

아래 handler는 중복 키 에러가 발생하면 MySQL 에러 1062가 발행된다. 이 핸들러는 에러 메시지를 발행하고 계속 실행한다.

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

 

저장 프로시저에서 MySQL handler 예제

첫번째로, 우리는 테스트를 위해 article_tags의 이름을 가진 새로운 테이블을 생성한다.

CREATE TABLE article_tags (
    article_id INT,
    tag_id     INT,
    PRIMARY KEY(article_id,tag_id)
);

article_tags 테이블은 articles와 tags 사이의 관계를 저장한다. 각 article은 많은 tags와 반대의 경우를 가지고 있다. 간단하게 할 수 있도록, articles과 tags 테이블을 만들지 않고 article_tags 테이블에 외래키 지정도 하지 않을 것이다.

다음으로, article_tags 테이블에 article id와 tag id를 삽입하는 저장 프로시저를 만들 것이다.

DELIMITER $$

CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
BEGIN

 DECLARE CONTINUE HANDLER FOR 1062
 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;

 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);

 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

그 후, 아래 처럼 insert_article_tags 저장 프로시저를 호출해서 article 1에 tag id 1,2,3을 추가할 것이다.

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

 

그 후에, handler가 정말로 호출 되는지 확인하기 위해 중복 키를 삽입할 것 이다.

CALL insert_article_tags(1,3);

 

우리는 에러 메세지를 얻었다. 하지만 CONTINUE 핸들러로 handler를 선언했기 때문에 저장 프로시저는 실행을 계속 할 것이다. 결과로 에러 메시지 뿐만 아니라 기사들의 tag수도 얻을 수 있었다.

만약 CONTINUE 핸들러 대신에 EXIT핸들러로 선언한다면, 오직 에러메시지만 얻을 수 있을 것 이다.

DELIMITER $$

CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT)
BEGIN

 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 SELECT 'SQLException invoked';

 DECLARE EXIT HANDLER FOR 1062 
 SELECT 'MySQL error code 1062 invoked';

 DECLARE EXIT HANDLER FOR SQLSTATE '23000'
 SELECT 'SQLSTATE 23000 invoked';

 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);

 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

마지막으로 효과를 확인 해보기 위해 중복 키를 추가해볼 수 있다.

CALL insert_article_tags_2(1,3);

 

 

MySQL handler 우선순위

에러를 처리 하기 위해 알맞는 handler가 여러개 있을 경우에, MySQL은 에러를 처리 하기 위해 가장 정확한 것을 호출한다

에러는 MySQL 에서는 거의 정확하기 때문에 항상 하나의 MySQL 에러 코드에 매핑된다. 그에 비해 SQLSTATE는 덜 정확하기 때문에 많은 MySQL 에러코드에 매핑된다. SQLEXCPETION 이나 SQLWARNING은 SQLSTATES 값의 클래스에 속기 하므로 가장 일반적이다(? 해석이 어렵네요.. 댓글로 해석좀 알려주세요.. )

handler 우선순위 규칙에 따라, MySQL 에러코드 handler, SQLSTATE handler, SQLEXCEPTION 순으로 우선순위가 정해져 있다.

우리는 아래처럼 insert_article_tags_3 저장 프로시저에 3개의 handler들을 선언한 것을 제공한다:

DELIMITER $$

CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
BEGIN

 DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';

 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);

 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

 

우리는 저장 프로시저를 호출해서 article_tags 테이블에 중복키를 삽입하는 것을 시도할 수 있다.

CALL insert_article_tags_3(1,3);

 

그리고, MySQL 에러 코드 handler가 호출 된 것을 볼 수 있다.

 

이름있는 에러조건 사용하기

오류 처리 선언을 시작해보자.

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
SELECT * FROM abc;

숫자 1051의 실제 의미는 무엇일까?  모든 장소에 1051 숫자로 오염되어 있는 큰 저장 프로시저를 가지고 있다고 상상해보라; 코드를 유지하기에 악몽일 것이다.

다행이도, MySQL은 조건과 관련이 있는 이름 있는 에러 조건을 선언하는 DECLARE CONDITION 문을 우리에게 제공한다

DECLARE CONDITION 문의 구조는 아래와 같다:

DECLARE condition_name CONDITION FOR condition_value;

 

condition_value 는 1015 나 SQLSTATE 값 같은 MySQL 에러 코드이다. condition_value는 condition_name에 의해 나타난다.

선언 후에, 우리는 condition_value 대신에 condition_name을 표현할 수 있다.

그래서, 우리는 아래 처럼 위의 코드를 재 작성할 수 있다.

DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;

 

이 코드는 이전 코드 보다 더 읽기에 명확하다.

조건 선언은 handler 나 cursor 선언 앞에 나타나야 한다는 것을 알고 있어야 한다.

 

출처 : http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/

이전 글 : [Stored Procedure] 11. MySQL에서의 저장 프로시저 리스트 ( Listing Stored Procedures in a MySQL Database )

다음 글 : [Stored Procedure] 13. MySQL SIGNAL/RESIGNAL 문을 이용한 에러 조건 양식 ( Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements )