MySQLストアドプロシージャで例外発生時に変数へ値を設定し、処理を継続するハンドラの書き方

MySQLのストアドプロシージャ内で例外が発生した場合、適切なエラーメッセージを返して例外を処理することは非常に重要です。例外をハンドリングしないまま放置すると、その例外が原因でアプリケーション全体が予期せず失敗する可能性があります。

MySQLには、例外が発生しても処理を中断せず、指定した変数に特定の値をセットした上で実行を続行する「CONTINUE HANDLER」という仕組みが用意されています。本記事では、主キー列に重複した値を挿入しようとするケースを例に、このハンドラの使い方を解説します。

CONTINUE HANDLERを使ったストアドプロシージャの作成例

以下の例では、学生情報をテーブルに挿入するストアドプロシージャを作成しています。ポイントは DECLARE CONTINUE HANDLER FOR SQLEXCEPTION の部分で、SQL例外が発生したときにOUTパラメータである got_error に 1 を代入し、処理を継続するよう定義している点です。

mysql> DELIMITER //
mysql> Create Procedure Insert_Studentdetails2(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20),OUT got_error INT)
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;
    -> INSERT INTO Student_detail
    -> (Studentid, StudentName, Address)
    -> Values(S_Studentid,S_StudentName,S_Address);
    -> Select * from Student_detail;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> Delimiter ;

通常のデータ挿入時の実行結果

まず、重複しない値を挿入してみます。INSERT文が正常に実行され、プロシージャ内のSELECT文の結果セットがそのまま返されます。このとき例外は発生しないため、got_error の値は変化しません。

mysql> CALL Insert_Studentdetails2(104,'Ram','Chandigarh',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address    |
+-----------+-------------+------------+
| 100       | Gaurav      | Delhi      |
| 101       | Raman       | Shimla     |
| 103       | Rahul       | Jaipur     |
| 104       | Ram         | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.04 sec)
Query OK, 0 rows affected (0.06 sec)

重複値挿入時にハンドラが動作する様子

次に、すでに存在する「studentid」列の値（104）を再度挿入してみます。主キーの重複によりSQL例外が発生しますが、CONTINUE HANDLERが定義されているため処理は停止せずそのまま継続されます。その結果として、プロシージャ内の SELECT * FROM student_detail の結果セットは正常に返され、同時に got_error 変数には 1 がセットされます。

mysql> CALL Insert_Studentdetails2(104,'Shyam','Hisar',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address    |
+-----------+-------------+------------+
| 100       | Gaurav      | Delhi      |
| 101       | Raman       | Shimla     |
| 103       | Rahul       | Jaipur     |
| 104       | Ram         | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.03 sec)

mysql> Select @got_error;
+------------+
| @got_error |
+------------+
| 1          |
+------------+
1 row in set (0.00 sec)

まとめ：エラー検知と処理継続を両立させる手法

このように、DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1; を宣言しておくことで、ストアドプロシージャ内で例外が発生しても処理を最後まで完走させつつ、変数を通じてエラーの発生を呼び出し元に通知できます。エラー発生時に即座に処理を中断したい場合はEXIT HANDLERを使用しますが、後続のクリーンアップ処理やログ出力などを継続的に実行したいケースでは、このCONTINUE HANDLERが非常に有効です。