事務控制語句
DELIMITER //
CREATE PROCEDURE multi_table_insert(IN user_id INT, OUT ret_code INT)
BEGIN
DECLARE exit_flag INT DEFAULT 0;
-- 使用 LOOP + LEAVE 模擬 try-catch
main_loop: LOOP
START TRANSACTION;
INSERT INTO table1 (f_user_id) VALUES (user_id);
IF ROW_COUNT() < 1 THEN
SET ret_code = -1; ROLLBACK; LEAVE main_loop;
END IF;
INSERT INTO table2 (f_user_id) VALUES (user_id);
IF ROW_COUNT() < 1 THEN
SET ret_code = -1; ROLLBACK; LEAVE main_loop;
END IF;
INSERT INTO table3 (f_user_id) VALUES (user_id);
IF ROW_COUNT() < 1 THEN
SET ret_code = -1; ROLLBACK; LEAVE main_loop;
ELSE
SET ret_code = 0; COMMIT; LEAVE main_loop;
END IF;
END LOOP main_loop;
SELECT ret_code;
END //
DELIMITER ;
關鍵技巧解析
| 技巧 | 作用 |
|---|
ROW_COUNT() | 檢查上一條 INSERT/UPDATE/DELETE 影響行數 |
LOOP + LEAVE | 模擬結構化異常處理，避免巢狀 IF 深度過深 |
出口統一設定 ret_code | 呼叫端可透過 OUT 參數獲知成敗 |
每步失敗即 ROLLBACK + LEAVE | 確保前序操作全數回滾 |
常見錯誤模式
START TRANSACTION;
INSERT INTO t1 ...;
SAVEPOINT sp1;
INSERT INTO t2 ...;
IF error THEN ROLLBACK TO SAVEPOINT sp1; END IF;
COMMIT;
驗證測試
