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


Summary: in this tutorial, you will learn how to use MySQL handler to handle exceptions or errors encountered in stored procedures.

When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes.

Declaring a handler

To declare a handler, you use the  DECLARE HANDLER statement as follows:

If a condition whose value matches the  condition_value , MySQL will execute the statement and continue or exit the current code block based on the action .

The action accepts one of the following values:

  • CONTINUE :  the execution of the enclosing code block ( BEGIN … END ) continues.
  • EXIT : the execution of the enclosing code block, where the handler is declared, terminates.

The  condition_value specifies a particular condition or a class of conditions that activate the handler. The  condition_value accepts one of the following values:

  • A MySQL error code.
  • A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or  SELECT INTO variable_list statement.
  • A named condition associated with either a MySQL error code or SQLSTATE value.

The statement could be a simple statement or a compound statement enclosing by the BEGIN and ENDkeywords.

MySQL error handling examples

Let’s look into several examples of declaring handlers.

The following handler means that if an error occurs, set the value of the  has_error variable to 1 and continue the execution.

The following is another handler which means that in case an error occurs, rollback the previous operation, issue an error message, and exit the current code block. If you declare it inside the BEGIN ENDblock of a stored procedure, it will terminate stored procedure immediately.

The following handler means that if there are no more rows to fetch, in case of a cursor or SELECT INTO statement, set the value of the  no_row_found variable to 1 and continue execution.

The following handler means that if a duplicate key error occurs, MySQL error 1062 is issued. It issues an error message and continues execution.

MySQL handler example in stored procedures

First, we create a new table named  article_tags for the demonstration:

The  article_tags table stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t create articles and tags tables, as well as the foreign keys in the  article_tags table.

Next, we create a stored procedure that inserts article id and tag id into the article_tags table:

Then, we add tag id 1, 2 and 3 for the article 1 by calling the insert_article_tags  stored procedure as follows:

After that, we try to insert a duplicate key to check if the handler is really invoked.

We got an error message. However, because we declared the handler as a CONTINUE handler, the stored procedure continued the execution. As the result, we got the tag count for the article as well.

MySQL Error Handling Example

If we change the CONTINUE in the handler declaration to EXIT , we will get an error message only.

Finally, we can try to add a duplicate key to see the effect.

MySQL error handling - duplicate keys

MySQL handler precedence

In case there are multiple handlers that are eligible for handling an error, MySQL will call the most specific handler to handle the error first.

An error always maps to one MySQL error code because in MySQL it is the most specific. An SQLSTATEmay map to many MySQL error codes, therefore, it is less specific. An SQLEXCPETION or an SQLWARNING is the shorthand for a class of SQLSTATES values so it is the most generic.

Based on the handler precedence’s rules,  MySQL error code handler, SQLSTATE handler and SQLEXCEPTIONtakes the first, second and third precedence.

Suppose we declare three handlers in the  insert_article_tags_3 stored procedure as follows:

We try to insert a duplicate key into the article_tags table by calling the stored procedure:

As you see the MySQL error code handler is called.

MySQL handler precedence

Using a named error condition

Let’s start with an error handler declaration.

What does the number 1051 really mean? Imagine you have a big stored procedure polluted with those numbers all over places; it will become a nightmare to maintain the code.

Fortunately, MySQL provides us with the DECLARE CONDITION statement that declares a named error condition, which associates with a condition.

The syntax of the DECLARE CONDITION statement is as follows:

The condition_value  can be a MySQL error code such as 1015 or a SQLSTATE value. The condition_valueis represented by the condition_name .

After the declaration, we can refer to condition_name instead of condition_value .

So we can rewrite the code above as follows:

This code is obviously more readable than the previous one.

Notice that the condition declaration must appear before handler or cursor declarations.

블로그 이미지

레몬도리 LemonDory

개발자의 이야기

댓글을 달아 주세요

출처: http://purumae.tistory.com/199


[MySQL / Stored Procedure] 에러 핸들링 시리즈


아래와 같은 테이블을 만들고, Stored Procedure 실행 중에 SQL Exception 이 발생했을 때, 디버깅에 필요한 단서를 저장합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `error_log` (
    `error_log_id` SMALLINT(5UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '에러 로그 ID',
    `proc_name` VARCHAR(100NOT NULL COMMENT '프로시저 이름',
    `proc_step` TINYINT(3UNSIGNED NOT NULL COMMENT '프로시저 내에서 에러가 발생한 스텝 번호',
    `sql_state` VARCHAR(5NOT NULL COMMENT 'SQLSTATE',
    `error_no` INT(11NOT NULL COMMENT '에러 번호',
    `error_msg` TEXT NOT NULL COMMENT '에러 메세지',
    `call_stack` TEXT NULL COMMENT '프로시저 호출 파라미터',
    `proc_call_date` DATETIME(0NOT NULL COMMENT '프로시저 호출 일자',
    `log_date` DATETIME(0NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '로그 적재 일자',
    PRIMARY KEY (`error_log_id`))
ENGINE = InnoDB
COMMENT = 'DB 런타임 에러 로그';
cs


샘플로 사용할 Stored Procedure의 일부

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
CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_terms_agreement_for_campaign`(
      IN pi_int_campaign_id int UNSIGNED    -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED -- 소셜 유저 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-05-03
description : 캠페인 약관에 동의합니다.
parameter : 
      IN pi_int_campaign_id int UNSIGNED    -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED -- 소셜 유저 ID
    , IN pi_dt5_now datetime(0)             -- 현재 서버 시각
    , OUT po_int_return int                 -- 리턴 값
return value :
    0 = 에러가 없습니다.
    -1 = 예상하지 않은 런 타임 오류가 발생하였습니다.
'
proc_body: BEGIN
    DECLARE v_vch_proc_name varchar(100DEFAULT 'usp_add_terms_agreement_for_campaign';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 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_campaign_id":'IF(pi_int_campaign_id IS NULL'null', pi_int_campaign_id)
            , ',"pi_int_social_user_id":'IF(pi_int_social_user_id IS NULL'null', pi_int_social_user_id)
            , ',"pi_dt5_now":'IF(pi_dt5_now IS NULL'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
 
        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;
cs


1. 변수 선언

25
26
27
28
29
30
    DECLARE v_vch_proc_name varchar(100DEFAULT 'usp_add_terms_agreement_for_campaign';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;
cs

v_vch_proc_name : 현재 Stored Procedure의 이름

v_vch_proc_step : SQL Exception이 발생한 구문위 위치를 찾기 위해 사용합니다. 번거롭고 원시적이지만 긴 Stored Procedure를 디버깅해야할 때 유용합니다.

v_txt_call_stack : input parameter 값을 json 형태로 변환하여 담습니다. (5.6과의 호환을 위해 json 유형으로 선언하지 않습니다.)

v_vch_sql_state v_int_error_no v_txt_error_msg : GET DIAGNOSTICS 구문으로 얻는 SQL STATE, Error Number, Error Message 를 담을 변수


2. DECLARE ... HANDLER 구문

32
33
.
.
52
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
 
 
    END;
cs

SQLEXCEPTION condition 이 되었을 때, BEGIN ~ END 블럭의 내용을 실행하고 EXIT 즉, Stored Procedure 실행을 강제로 종료합니다.


3. GET DIAGNOSTICS 구문

34
35
36
        GET DIAGNOSTICS CONDITION v_vch_sql_state = RETURNED_SQLSTATE
            , v_int_error_no = MYSQL_ERRNO
            , v_txt_error_msg = MESSAGE_TEXT;
cs

GET DIAGNOSTICS 구문으로 디버깅에 필요한 다음 세 가지 condition information 을 가져옵니다.

RETURNED_SQLSTATE
MYSQL_ERRNO
MESSAGE_TEXT


4. Call Stack 추출

40
41
42
43
44
        SET v_txt_call_stack = CONCAT('{"pi_int_campaign_id":'IF(pi_int_campaign_id IS NULL'null', pi_int_campaign_id)
            , ',"pi_int_social_user_id":'IF(pi_int_social_user_id IS NULL'null', pi_int_social_user_id)
            , ',"pi_dt5_now":'IF(pi_dt5_now IS NULL'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
cs

Stored Procedure의 input parameter 값을 JSON 문자열로 만들어 v_txt_call_stack 변수에 담습니다.

TIP!!!
개발자가 매번 이 부분을 손으로 코딩하는 것은 바람직하지 않습니다. (단순 반복 작업 & 실수하기 쉬움) -> 자동화 하세요~


5. INSERT `error_log` ...

46
47
        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));
cs


6. 마무리

49
50
51
        SET po_int_return = -1;
 
        RESIGNAL;
cs

output parameter po_int_return

- Stored Procedure 가 정상적으로 실행 : SET po_int_return = 0;
- SQL Exception 이 발생하여 `error_log` 테이블에 로깅 : SET po_int_return = -1;


RESIGNAL

- error condition 정보를 Stored Procedure를 호출한 클라이언트에게 전합니다.
- RESIGNAL 하지 않으면 Stored Procedure를 호출한 클라이언트는 SQL Exception 이 발생했다는 사실을 감지하지 못하기 때문에, RESIGNAL 이 필요합니다.


블로그 이미지

레몬도리 LemonDory

개발자의 이야기

댓글을 달아 주세요