MySQL ストアドプロシージャのHANDLERの順番には注意

ストアドプロシージャにて、トランザクションをかける場合、HANDLERの順番には注意しましょう！という話です。

Logicool ロジクール Bluetoothマウス M558

HANDLERだけ書くと、別途MySQLがもっているHANDLER構文も入ってしまいますが、例外処理（DECLARE 〜 HANDLER）の話を今回はします。

先に正しい順番を書きますね。

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SELECT _sqlstate, _errno, _text;
      ROLLBACK;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      GET DIAGNOSTICS CONDITION 1 _sqlstate = RETURNED_SQLSTATE, _errno = MYSQL_ERRNO, _text = MESSAGE_TEXT;
    END;

最初に書くべきもの

• DECLARE EXIT HANDLER FOR SQLEXCEPTION
• DECLARE CONTINUE HANDLER FOR NOT FOUND
• DECLARE CONTINUE HANDLER FOR SQLWARNING

この順番に注意してください。 とうのも、SQLWARNINGってNOT FOUNDも、SQLWARNINGに部類に入ってしまいますので、NOT FOUNDで先に拾っておくのが無難な書き方です。 何かのSQLを実行時、NOT FOUNDがでた場合、自分で、エラーを書くのが無難だと思います。

例えばこんな感じで。

root:BEGIN

  
  BEGIN
    DECLARE _not_found tinyint UNSIGNED DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SELECT _sqlstate, _errno, _text;
      ROLLBACK;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      GET DIAGNOSTICS CONDITION 1 _sqlstate = RETURNED_SQLSTATE, _errno = MYSQL_ERRNO, _text = MESSAGE_TEXT;
    END;


    main:BEGIN
        SELECT 
           user_id
        FROM 
            users
       WHERE
           user_id = _user_id ;

       IF _not_found = 1 THEN
          SELECT 'ERROR: Data not found ' ;
          ROLLBACK ;
          LEAVE main ; 
       END IF  ;

       UPDATE 
           users 
        SET
           password = _passwd
        WHERE
            user_id = _user_id
        ;
        COMMIT;

   END ;
 END ;
END$$