mysql存储过程异常_mysql存储过程异常处理

DELIMITER $$USE`mtnoh_aaa_platform`$$DROP PROCEDURE IF EXISTS`proc_eoms_electric_power_generation_check_engine`$$CREATE DEFINER=`dwgl`@`%` PROCEDURE`proc_eoms_electric_power_generation_check_engine`(

p_oil_engine_idINT,

p_task_idINT,

p_task_snVARCHAR(40),

P_task_userVARCHAR(255),

P_task_use_dateDATETIME,

P_task_end_dateDATETIME)BEGIN

DECLARE p_oil_engine_Status,P_oil_engine_use_status VARCHAR(300);DECLARE p_engine_id,p_engine_id_old,p_use_task_id,p_work_flow_version_id INT;DECLARE p_use_task_sn VARCHAR(40);DECLARE result_code CHAR(5) DEFAULT '00000'; DECLARE msg TEXT;DECLARE set_error_code CHAR(1);DECLARE result_info,success_info TEXT;DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGINGET DIAGNOSTICS CONDITION1 result_code = RETURNED_SQLSTATE, msg =MESSAGE_TEXT;ROLLBACK;UPDATEmtdwgl_res.tb_irms_material_oil_engine oeSET oil_engine_use_status = '空闲'

WHERE oe.deleted = '0' AND oe.oil_engine_id =p_oil_engine_id;SET result_info = CONCAT('校验油机状态失败,错误代码:',result_code,',错误信息:',CHAR(13),CHAR(10),IFNULL(msg,''));SELECTresult_info;END;

STARTTRANSACTION;SELECToe.oil_engine_Status,oe.oil_engine_use_status,oe.oil_engine_id,ic.task_id,ic.task_snINTOp_oil_engine_Status,P_oil_engine_use_status,p_engine_id,p_use_task_id,p_use_task_snFROMmtdwgl_res.tb_irms_material_oil_engine oeLEFT JOINmtnoh_aaa_resource.tb_task_electricity_generation_info_current icON oe.oil_engine_id =ic.oil_engine_idWHERE oe.deleted = '0' AND oe.oil_engine_id =p_oil_engine_id

LIMIT1;SELECTic.oil_engine_idINTOp_engine_id_oldFROMmtnoh_aaa_resource.tb_task_electricity_generation_info_current icWHERE ic.task_id = p_task_id AND ic.task_sn =p_task_sn

LIMIT1;IF (p_oil_engine_Status = '在用' AND P_oil_engine_use_status = '空闲') OR p_use_task_sn = p_task_sn THEN

SET p_work_flow_version_id :=mtnoh_aaa_platform.get_work_flow_version_id(p_task_sn);IF IFNULL(p_engine_id_old,p_oil_engine_id) <> p_oil_engine_id THEN

UPDATEmtdwgl_res.tb_irms_material_oil_engine oeSET oil_engine_use_status = '空闲'

WHERE oe.deleted = '0' AND oe.oil_engine_id =p_engine_id_old;END IF;UPDATEmtdwgl_res.tb_irms_material_oil_engine oeSET oil_engine_use_status = '发电'

WHERE oe.deleted = '0' AND oe.oil_engine_id =p_oil_engine_id;INSERT INTOmtnoh_aaa_resource.tb_task_electricity_generation_info_current

(oil_engine_id,work_flow_version_id,task_id,task_sn,task_user,task_use_date,task_end_date)VALUES(p_oil_engine_id,p_work_flow_version_id,p_task_id,p_task_sn,P_task_user,P_task_use_date,P_task_end_date)ON DUPLICATE KEY UPDATE task_user =P_task_user

,task_use_date=P_task_use_date

,task_end_date=P_task_end_date;ELSE

IF IFNULL(p_engine_id,-1) <> -1 THEN

SELECTp_oil_engine_Status,P_oil_engine_use_status;END IF;END IF;COMMIT;END$$

DELIMITER ;