mysql 存储过程获取异常、回滚、手动提交

mysql_catch_execption.sql

drop procedure if exists catch_execption;
create procedure catch_execption(in V_ID bigint)
begin
    DECLARE result varchar(100) DEFAULT '00000';

    DECLARE code CHAR(5);
    DECLARE msg TEXT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN

        GET DIAGNOSTICS CONDITION 1
            code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
        ROLLBACK;
        set result = CONCAT('call [pro_dr_archive] failed, error = ',code,', message = ',msg);
        select result;
    END;

    start transaction ;

    -- add your process

    COMMIT;

    select result;
end;