[MySQL] DECLARE ... HANDLER

참고

MySQL Documentation 13.6.7.2 DECLARE ... HANDLER Statement

설명

  • 처리하고자 하는 하나 이상의 컨디션을 특정하여 해당 컨디션 발생 시의 처리를 지정할 수 있음
  • 선언위치는 변수, 컨디션 선언 이후

syntax

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

handler_action

  • HANDLER의 statement 실행 후 수행할 작업을 나타냄
  • CONTINUE: 현재 프로그램을 계속 이어서 실행
  • EXIT: 현재 프로그램을 종료
  • UNDO: Not supported.

condition_value

  • mysql_error_code, SQLSTATE 또는 이를 특정해서 선언한 condition_name이 올 수 있다.
  • SQLWARNING: SQLSTATE가 01로 시작하는 클래스에 대한 Shorthand
  • NOT FOUND: SQLSTATE가 02로 시작하는 클래스에 대한 Shorthand, 주로 CURSOR를 사용할 때 사용(이때 정확히는 SQLSTATE value '02000')
  • SQLEXCEPTION: SQLSTATE가 '00', '01', '02'로 시작하지 않는 나머지

발생한 특정 컨디션을 처리하는 HANDLER가 없을 경우

SQLEXCEPTION

  • handler_action이 EXIT인 핸들러가 있는 것과 같이 프로그램을 종료 시킴

SQLWARNING

  • handler_action이 CONTINUE인 핸들러가 있는 것과 같이 프로그램을 진행 시킴

NOT FOUND

  • 정상적으로 발생한 컨디션의 경우 프로그램은 CONTINUE
  • 만약 SIGNAL 또는 RESIGNAL에 의해 발생했다면 프로그램은 EXIT

예시

예시 1

  • SQLSTATE '23000'(duplicate-key error)의 경우
  • 만약 해당 핸들러를 만들지 않았다면 SELECT @x의 값은 2였을 것
mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
       BEGIN
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
         SET @x = 1;
         INSERT INTO test.t VALUES (1);
         SET @x = 2;
         INSERT INTO test.t VALUES (1);
         SET @x = 3;
       END;
       //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)
  • 해당 컨디션을 무시하려면 아래와 같이 빈 BEGIN ... END 작성 
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

예시 2

  • 특정 BEGIN ... END 블록의 label_name을 핸들러의 statement에서 사용할 수 없음(ex. ITERATE label_name)
CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            ITERATE retry;    # illegal
          END;
        IF i < 0 THEN
          LEAVE retry;        # legal
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;
  • 대신 아래와 같이 작성하여 같은 해당 BEGIN ... END 블록을 빠져나가도록 할 수 있음
# using EXIT
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

# using block cleanup statements
DECLARE EXIT HANDLER FOR SQLWARNING
  BEGIN
    block cleanup statements
  END;
  • 일반적으로 아래와 같이 변수를 만들어 사용할 수 있다.
CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  DECLARE done INT DEFAULT FALSE;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            SET done = TRUE;
          END;
        IF done OR i < 0 THEN
          LEAVE retry;
        END IF;
        SET i = i - 1;
      END;
    UNTIL FALSE END REPEAT;
END;

links

social