mysql创建有带异常处理功能事务的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `procAddVehiclePointsChange`(
in plateNumber varchar(10),in businessType varchar(50),in changeType int,
in value int, in account varchar(50),in remark varchar(100),
out result int
)
begin
DECLARE pointsBefore,pointsAfter INTEGER DEFAULT 100;
declare success integer default 1;
declare grade integer default 2;
-- 车辆积分表中是否有此车
declare hasPoints integer default 0;
-- 定义异常处理程序，如果有异常则回滚事务，并输出异常信息
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  GET DIAGNOSTICS CONDITION 1
        @error_code = RETURNED_SQLSTATE,
        @error_message = MESSAGE_TEXT;

    -- 输出异常信息
     SELECT CONCAT('Error Code:', @error_code) AS 'Error', CONCAT('Error Message:', @error_message) AS 'Message';
END;
select count(0) into hasPoints from  tms_vehicle_points where plate_number = plateNumber;
-- 取出车辆变更前的积分
if hasPoints = 1 then 
	SELECT points into pointsBefore FROM tms_vehicle_points where plate_number = plateNumber;
end if;
-- 计算变更后的积分
if changeType = 0 then
	set pointsAfter = pointsBefore + value;
else
	set pointsAfter = pointsBefore - value;
end if;

-- 根据变更后的积分设置车辆等级
if pointsAfter < 61 then
	set grade = 0;
else
		if pointsAfter < 81 then
			 set grade = 1;
	  else
				if pointsAfter < 101 then 
					set grade = 2;
			  else
						if pointsAfter < 121 then
							set grade = 3;
						else
								if pointsAfter < 141 then
									set grade = 4;
								else
									set grade = 5;
								end if;
						end if;
				end if;
		end if;
end if;

-- 开始事务
START TRANSACTION;
-- 如果车辆积分表中无此车辆则直接插入，否则更新车辆积分
if hasPoints = 0 then
	INSERT INTO `tms`.`tms_vehicle_points`(`grade`, `plate_number`, `points`, `create_time`, `create_by`,  `remark`) VALUES ( grade, plateNumber, pointsAfter, now(), account,remark);
else
UPDATE `tms`.`tms_vehicle_points` SET `grade` = grade, `points` = pointsAfter,  `update_by` = account, `update_time` = now(), `remark` = remark WHERE `plate_number` = plateNumber;
end if;


if ROW_COUNT() > 0 then
	-- 如果影响行数大于0则继续新增加积分变更记录
	INSERT INTO `tms`.`tms_vehicle_points_change`(`plate_number`, `before_change`, `after_change`, `change_type`, `business_type`, `create_time`, `create_by`, `remark`, `update_by`, `update_time`, `value`) VALUES (plateNumber, pointsBefore, pointsAfter, changeType, businessType, now(), account, remark, null, null, value);
end if;
if row_count() = 0 then
	set success = 0;
end if;
 
 IF success = 1 THEN
        COMMIT;
    ELSE
        ROLLBACK;
 END IF;
 set result = success;
END