(TIL) My Sql Error Handler, SIGNAL, RESIGNAL / 수정

https://wecanstudy-everything.tistory.com/29

(TIL) 2022.05.03 SQL 용어 정리

지난 TIL의 마지막 항목 My Sql의 Declare Handler를 보충해봤습니다.

 

 

- 여러 개의 에러 핸들러 사용법

BEGIN -- outer block
  DECLARE EXIT HANDLER FOR ...;  -- handler H1
  DECLARE EXIT HANDLER FOR ...;  -- handler H2
  stmt1;
  stmt2;
END;

 

우선 순위

error code Handler > SQLSTATE vlalue Handler > 일반 SQL Warning, SQLEXCEPTION, NOT FOUND 처리기
                                                                                     (SQL EXCEPTION > SQL WARNING)

 

한 statement에서 여러 핸들러가 있는 경우 여러 경고를 생성할 수 있습니다.

이 경우 서버가 활성화하는 처리기의 선택은 비결정적(nondeterministic)이며 발생하는 상황에 따라 변경될 수 있습니다.

 

우선 순위 예

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
    SELECT 'SQLSTATE handler was activated' AS msg;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'SQLEXCEPTION handler was activated' AS msg;

  DROP TABLE test.t;
END;

이 프로시저에는 존재하지 않는 테이블을 삭제하려는 시도에 대해 발생하는 특정 SQLSTATE()와 SQLEXCEPTION이 있습니다.

 

두 핸들러는 모두 같은 블록과 같은 스코프 내에서 선언했습니다. 하지만 SQLSTATE가 SQLEXCEPTION보다 우선 순위가 높습니다.

 

따라서 결과는 하단과 같이 나오게 됩니다.

 

mysql> CALL p1();
+--------------------------------+
| msg                            |
+--------------------------------+
| SQLSTATE handler was activated |
+--------------------------------+

 

선언 위치

 

만약 여러 개의 핸들러가 서로 다른 범위에서 발생하는 경우

로컬 범위에 위치한 핸들러가 외부 범위 핸들러 보다 우선이 됩니다.

 

BEGIN -- outer block
  BEGIN -- inner block
    DECLARE EXIT HANDLER FOR ...;  -- handler H1
    stmt1;
  END;
  stmt2;
END;

 

 

선언 위치 예

CREATE PROCEDURE p2()
BEGIN -- outer block
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
      SELECT 'SQLSTATE handler was activated' AS msg;
  BEGIN -- inner block
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      SELECT 'SQLEXCEPTION handler was activated' AS msg;

    DROP TABLE test.t; -- occurs within inner block
  END;
END;

이 경우에는 좀 더 로컬인 핸들러가 실행됩니다.

 

mysql> CALL p2();
+------------------------------------+
| msg                                |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+

 

결과적으로 이때는 SQLEXCEPTION이 실행됩니다.

 

 

그렇다면 이 경우에는 어떨까요?

CREATE PROCEDURE p3()
BEGIN -- outer block
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'SQLEXCEPTION handler was activated' AS msg;
  BEGIN -- inner block
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
      SELECT 'SQLSTATE handler was activated' AS msg;
  END;

  DROP TABLE test.t; -- occurs within outer block
END;

 

위치가 다르고

SQLEXCEPTION과 SQLSTATE가 사용되었습니다.

 

결과는 다음과 같습니다.

mysql> CALL p3();
+------------------------------------+
| msg                                |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+

 

이유는 하나의 핸들러만이 DROP TABLE STATEMENT의 블럭에 선언되었기 때문입니다.

잘 보시면 SQLSTATE는 별도의 블럭으로 선언되어있습니다.

(블럭은 BEGIN과 END를 기준으로 보시면 됩니다)

 

 

 

SIGNAL문

SIGNAL은 오류를 반환하는 방법입니다.

 

오류를 반환하는 것은 SQLEXCEPTION이나 다른 에러로 발생시킬 수 있는 것 아닌가 생각하실 수 있습니다.

 

그런데 이 SQLEXCEPTION이 발생하지 않는 상황에 예외 처리가 필요할 수 있습니다.

 

대표적으로 수정하거나 삭제하려는 ROW가 존재하지 않는 경우가 있습니다.

그 외에도 해당 테이블에서 개발자끼리 정해놓은 약속들이 있어 해당 상황에는 에러를 발생해야 할 수도 있습니다.

 

그래서 이때 SIGNAL을 통해 임의의 에러를 발생시키고

DECLARE ... HANDLER문에서 핸들링을 할 수 있습니다.

 

SIGNL은 애플리케이션의 외부나 클라이언트에게 에러 정보를 제공합니다.

 

또한 오류(오류 번호, SQLSTATE값, 메시지)를 제어할 수 있습니다.

 

 

 

SIGNAL 문법

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

condition_name, simple_value_specification:
    (see following discussion)

 

추가된 예시

CREATE PROCEDURE addStudent(  
IN stud_id INT,   
IN stud_name VARCHAR(35),   
IN subject VARCHAR(25),   
IN marks INT,   
IN phone VARCHAR(15)  
)  
BEGIN  
    DECLARE C INT;  
    SELECT COUNT(student_id) INTO C  
    FROM student_info  
    WHERE  student_id = stud_id;  
  
    -- check if student id not exists  
    IF(C != 1) THEN   
        SIGNAL SQLSTATE '45000'  
        SET MESSAGE_TEXT = 'Student id not found in student_info table';  
    END IF;  
END $$  
DELIMITER ;

 

addStudent라는 프로시저를 만듭니다.

여기서는 먼저 저장 프로시저에서 전달한 입력 학생 ID로 총 학생을 계산합니다. 

그리고 테이블에서 이 값을 확인하고 학생 수가 1이 아닐 경우 student_info 테이블에 학생 ID가 존재하지 않는다는 메시지와 함께 SQLSTATE 45000의 오류를 반환합니다.

 

없는 값을 호출한다면

이런식으로 결과가 나오게 됩니다.

 

예시

CREATE PROCEDURE p (pval INT)
BEGIN
  DECLARE specialty CONDITION FOR SQLSTATE '45000';
  IF pval = 0 THEN
    SIGNAL SQLSTATE '01000';
  ELSEIF pval = 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'An error occurred';
  ELSEIF pval = 2 THEN
    SIGNAL specialty
      SET MESSAGE_TEXT = 'An error occurred';
  ELSE
    SIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
  END IF;
END;

pval 가 0이면 '01' 로 시작하는 SQLSTATE 값은 경고 클래스의 신호이기 때문에 p() 는 경고를 알립니다. 

이 경고는 프로 시저를 종료하지 않고 프로 시저에서 돌아온 뒤에 SHOW WARNINGS 에서 확인할 수 있습니다.

 

pval 가 1이면 p() 는 오류를 통지하고 MESSAGE_TEXT 조건 정보 항목을 설정합니다. 

이 오류는 프로 시저를 종료하고 그 텍스트가 오류 정보와 함께 반환됩니다.

 

pval 가 2 인 경우,이 경우 명명 된 조건을 사용하여 SQLSTATE 값이 지정되어 있음에도 불구하고 같은 에러가 통지됩니다.

 

pval 가 다른 임의의 값이면 p() 는 처음에 경고를 통지하고 메시지 텍스트 및 오류 번호 조건 정보 항목을 설정합니다. 

이 경고는 프로 시저를 종료시키지 않기 때문에 실행이 계속되고, 그 후에 p() 는 오류를 통지합니다. 

 

이 오류는 프로 시저를 종료합니다. 경고에 의해 설정된 메시지 텍스트 및 오류 번호 오류에 의해 설정된 값으로 대체 그것이 오류 정보와 함께 반환됩니다.

 

 

같은 이름의 SIGNAL을 사용하는 경우

CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE my_error CONDITION FOR SQLSTATE '45000';
  IF divisor = 0 THEN
    BEGIN
      DECLARE my_error CONDITION FOR SQLSTATE '22012';
      SIGNAL my_error;
    END;
  END IF;
  SIGNAL my_error;
END;

만약 divisor가 0이라면 첫 번째 SIGNAL이 실행될 것입니다. 가장 안쪽의 my_error 조건이 적용되어 SQLSTATE '22012'가 발생합니다.

 

divisor가 0이 아닌 경우에는 2번째 SIGNAL이 실행될 것입니다. 가장 바깥쪽의 my_error 조건이 적용되어 SQLSTATE '45000'이 발생합니다.

 

 

RESIGNAL문

 

RESIGNAL은 저장 프로시저 또는 함수, 트리거 또는 이벤트 내의 복합 문 내에서 조건 핸들러를 실행하는 동안 사용할 수 있는 오류 조건 정보를 전달합니다.

 

RESISCONAL 문은 오류 또는 경고 핸들러 자체 내에서 사용해야 합니다.

그렇지 않으면 MySQL은 핸들러가 활성화되지 않을 때 RESIGNAL when the handler is not active이라는 오류 메시지를 생성합니다.

 

 

RESIGNAL은 정보를 전달하기 전에 일부 또는 모든 정보를 변경할 수 있습니다.

 

RESIGNAL은 SEGINAL과 관련되어 있습니다.

하지만 SIGNAL은 동일한 조건을 전달하는 대신 RESIGNAL은 기존 조건 정보를 수정 후 전달합니다.

 

 

문법

RESIGNAL [condition_value]
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name: {
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
}

condition_name, simple_value_specification:
    (see following discussion)

 

단순 RESIGNAL은 변경없이 오류를 전달합니다. 마지막 진단영역을 복원하여 현재 진단 영역으로 만듭니다.

즉, 진단 영역 스택을 꺼냅니다.

 

조건 핸들러 내에서 RESIGNAL은 일부 다른 작업을 수행한 다음 원래 조건 정보(핸들러에 입력하기 전에 존재했던 정보)를 변경하지 않고 전달합니다.

 

예시

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

 

 

새로운 신호 정보가 있는 RESIGNAL

 

새로운 신호 정보를 제공하므로 변경과 함께 오류를 전달하는 것을 의미합니다.

 

 

문법

RESIGNAL SET signal_information_item [, signal_information_item] ...;

 

예시

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

 

단독 RESIGNAL과 달리 SET절에 지정된 모든 항목이 변경됩니다.

 

 

조건 및 새 신호 정보가 있는 RESIGNAL

 

문법

RESIGNAL condition_value
    [SET signal_information_item [, signal_information_item] ...];

 

단독 작업과 동일한 듯 행동하지만 상태 값이나 신호 정보에 따라 진단 영역도 변경됩니다.

 

예시

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
SET @@max_error_count = 2;
CALL p();
SHOW ERRORS;

앞의 예시와 효과는 동일하나 RESIGNAL이 발생하면 마지막에 현재 조건 영역이 다르게 보입니다.

 

 

해당 예시의 CALL p() 및 SHOW ERRORS의 결과는 다음과 같습니다.

mysql> CALL p();
ERROR 5 (45000): Unknown table 'xx'
mysql> SHOW ERRORS;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1051 | Unknown table 'xx'               |
| Error |    5 | Unknown table 'xx'               |
+-------+------+----------------------------------+

 

 

 

출처:

https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

https://dev.mysql.com/doc/refman/5.6/en/handler-scope.html

https://dev.mysql.com/doc/refman/8.0/en/signal.html

https://dev.mysql.com/doc/refman/8.0/en/resignal.html