MySQLにおいて、トランザクションが必要なストアドプロシージャを書く必要があったので、その雛形を書き留めておきます。ちょっと苦労したのは、DECLARE EXIT HANDLERして明示的にROLLBACKしておかないと、途中でエラーがあってもCOMMITまでズンズン進んでしまうということでした。
- MySQL 5.6.4以降
DELIMITER //
CREATE PROCEDURE プロシージャ名()
BEGIN
-- DECLARE 変数
-- DECLARE CURSOR
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;
ROLLBACK;
END;
-- DECLARE 追加のHANDLER
START TRANSACTION;
-- ここに処理を書く
COMMIT;
SELECT 'Success!' AS result FROM DUAL;
END//
DELIMITER ;
CALL プロシージャ名();
エラーがあった場合はGET DIAGNOSTICS CONDITION 1により、エラー内容をSELECT文で表示するようにしています。これがないとROLLBACKして正常終了するだけで、どんなエラーが発生したのか分からないので。
またDECLAREの順序には決まりがあるみたいで、変数→カーソル→ハンドラーの順に並んでいないとエラーになります。