mariadb procedure exception 처리 예제

mariadb procedure exception 처리 예제

mariadb procedure exception 처리 예제

안녕하세요.
오늘은 mariadb에서 procedure나 function을 개발하실때 exception 예외처리를 어떻게 하는지에 대한 예제를 기록해보도록 하겠습니다.

1. exception 이란?

mariadb에서 procedure나 function을 사용하실때 exception을 이용하여 SQL에러나 데이터가 없을때, 혹은 어떤 로직의 상황에서 벗어나야 할때 exception처리를 해줘야 정확한 SP를 작성 할 수 있습니다.
예외처리를 잘 해야 안그래도 디버깅도 어려운 procedure나 function을 제대로 사용 할 수 있겠죠.

2. exception 종류

저는 exception을 크게 3종류를 사용하고 있습니다.
하나는 not found, sqlexception, user_define 이렇게 세가지를 쓰는데, 이 세가지면 충분히 어지간한건 다 만들수가 있습니다. 그럼 하나씩 그 예제를 보도록 하겠습니다.

 

 

 

3. not found

먼저 예제 코드를 보도록 하겠습니다.

declare exit handler
        for not found
        begin
            rollback;
            
            set retCode = 21;
            set retMsg = '해당 data 없음.';
        end;

not found의 예제 인데요, 이건 procedure나 function에서 내부 로직에서 select를 했을때 데이터가 없을때 위와 같이 선언을 해주면 해당 exception을로 빠지게 되고 begin ~ end 사이의 구문이 실행이 되겠죠. 즉, rollback이 된 후에 retCode와 retMsg 변수에 각각의 값이 들어가고 종료 되는 예외처리 예제가 되겠습니다.

처음에 declare exit handler 라고 선언을 했기때문에 이 예외로 빠지게 되면 begin ~ end 사이가 실행되고나서 해당 SP는 실행이 종료가 됩니다. 종료가 되지 않게 하려면 continue를 exit 대신 써주면 되겠습니다.

4. sqlexception

이것도 먼저 예제 코드를 먼저 보도록 할께요.

declare exit handler
        for sqlexception
        begin
            rollback;
            get diagnostics condition 1
                @rs = RETURNED_SQLSTATE , @mt = MESSAGE_TEXT;
            set retCode = @rs;
            set retMsg = concat('알수 없는 오류: ', @mt);
        end;

sqlexception 예외처리는 procedure나 function에서 내부 로직에서 SQL 에러가 났을때 이쪽으로 예외처리가 되게 됩니다. 위와 마찬가지로 exit handler라서 예외처리가 되면서 종료가 되는 형태의 구문 입니다. 그리고, rollback이 되구요, get diagnostics~ 부터 두줄이 있는데, 이는 어떤 SQL에러가 났는지를 체크하면서 @rs 변수에는 해당 error code를 저장하고 @mt에는 error message를 저장하게됩니다. 그리고 나서 retCode와 retMsg에 각각을 저장을 다시 합니다.

이렇게 SQL 구문 또는 문법에러를 예외처리 할 수 있습니다.

 

 

 

5. user_define 

user_define 은 말그대로 사용자 지정 예외처리 방법 입니다. 꼭 어떤 에러가 났다기 보다 어떤 변수의 값이나 로직중간에 빠져나가야 하는 상황을 만들어 줄 수 있는것 입니다. 다음과 같이 선언 할 수 있습니다.

DECLARE USER_today CONDITION FOR SQLSTATE '45000';
    
DECLARE EXIT HANDLER FOR USER_today
    BEGIN
        ROLLBACK;
        select CONCAT('User: ',row_count(),' : ');
        RESIGNAL;
    END;

먼저 USER_today와 같이 사용자가 지정하는 어떠한 이름으로 condition을 하나 선언을 해줍니다.
그리고 그 아래에 handler를 선언을 하면서 같은 이름으로 해줘야 합니다.

그리고나서 작동법은 다른 예외처리들과 같습니다. begin ~ end 사이가 실행됩니다. 그런데, 위에서는 데이터가 없거나 SQL이 error 날때 해당 예외가 발생이 되는데, 이건 어떻게 되는 걸 까요?

바로 다음과 같이 호출 할 수 있습니다.

 IF p_param = '1' then
        SIGNAL USER_today;
END IF;

SP내부에서 위와 같이 어떤 조건을 주고, 그 안에서 signal 이라는 명령으로 예외 상황을 호출 할 수 있습니다.

그리고, 이러한 예외 처리와 관련된 선언 부분은 다음 포스트를 참고 하셔서 위치를 잘 선정하여 선언 해주셔야 합니다.
꼭 참고 하시길 바랍니다.
감사합니다.

2021.08.25 - [Database/mariaDB administrator] - [Mysql] Cursor declaration after handler declaration 에러 해결 방법

[Mysql] Cursor declaration after handler declaration 에러 해결 방법

2020.07.14 - [Database/mariaDB administrator] - Mysql objects 개념 정리 for 개발자

Mysql objects 개념 정리 for 개발자

2021.10.27 - [Database/mariaDB administrator] - mysql 실무에 유용한 sql 로직 모음 #01

mysql 실무에 유용한 sql 로직 모음 #01

by.sTricky