2022-1-7数据库 事务

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_03_tx`()
BEGIN
	# 事务TRANSACTION
	
	DECLARE t_error INT;
	
	# handler控制器，捕获异常
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
	
	# 开启事务
	START TRANSACTION;	
	
	UPDATE stu_info SET stu_name = '关婷娜' WHERE id = 22;
	
	INSERT INTO stu_info SET id = 3, stu_name='abc';
	
	IF t_error = 1 THEN
		ROLLBACK; 
		SELECT "事务执行失败" as remark;
		# 回滚事务：事务中的SQL语句，全部回滚到未执行之前的状态
	ELSE
		COMMIT;
		SELECT "事务执行成功" as remark;
		# 提交事务
	END IF;
END