MySql stored procedure exception handling example _ MySQL

MySql stored procedure exception handling example bitsCN.com

MySql stored procedure exception handling example:

I checked a lot of information on the internet and found that the exception handling information about mysql is a list of error numbers, we may want to record more accurate error messages to the log.

The following is the sample code. When an exception occurs, the exception information is stored in the log table and the subsequent statements are run.

If you have better suggestions, please kindly advise.

MySql stored procedure exception handling example

-- ---------------------------------------------------------------------------------- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- revoke DELIMITER $ create definer = 'root' @ '% 'procedure 'merge _ brandproductkey '() begin declare continue handler for sqlwarning, SQLEXCEPTION begin insert into t_runninglog values (current_timestamp (), 'error in mergebrandproductkey', left (mysql_error (), 255); commit; end; insert into t_runninglog values (current_timestamp (), 'Start in MergeBrandProductKey ', ''); commit; -- normal update brandproductkey as bpk, (select bp. brandproductid, bp. brandproductenname, bp. brandid from brandproduct as bp inner join (select brandid, brandproductid from brandproductdriverrelation group by brandid, brandproductid) as bpr on bp. brandid = bpr. brandid and bp. brandproductid = bpr. brandproductid) as bpp set bpk. brandproductid = bpp. brandproductid where bpk. brandproductid = 0 and bpk. computertype = 2 -- 0 and bpk. brandid = bpp. brandid and upper (bpk. brandproductkeyname) = upper (replace (bpp. brandproductenname, '',''); commit; insert into t_runninglog values (current_timestamp (), 'rule' normal in MergeBrandProductKey ', ''); commit; -- sony rule 1 -- VPCEA37EC --> (VPCEA37EC/B, VPCEA37EC/L, VPCEA37EC/P, VPCEA37EC/W) update brandproductkey as bpk, (select bp. brandproductid, bp. brandproductenname, bp. brandid from brandproduct as bp inner join (select brandid, brandproductid from brandproductdriverrelation group by brandid, brandproductid) as bpr on bp. brandid = bpr. brandid and bp. brandproductid = bpr. brandproductid and bp. brandid = 60) as bpp set bpk. brandproductid = bpp. brandproductid where bpk. brandproductid = 0 and bpk. computertype = 2 -- 0 and bpk. brandid = bpp. brandid and bpp. brandproductenname like concat (bpk. brandproductkeyname, '/%'); commit; insert into t_runninglog values (current_timestamp (), 'rulessony 1 in MergeBrandProductKey ', ''); commit; -- sony rule 2 -- VGN-TZ37N_X --> VGN-TZ37N/X update brandproductkey as bpk, (select bp. brandproductid, bp. brandproductenname, bp. brandid from brandproduct as bp inner join (select brandid, brandproductid from brandproductdriverrelation group by brandid, brandproductid) as bpr on bp. brandid = bpr. brandid and bp. brandproductid = bpr. brandproductid and bp. brandid = 60) as bpp set bpk. brandproductid = bpp. brandproductid where bpk. brandproductid = 0 and bpk. computertype = 2 -- 0 and bpk. brandid = bpp. brandid and upper (bpk. brandproductkeyname) = upper (replace (bpp. brandproductenname, '/', '_'); commit; insert into t_runninglog values (current_timestamp (), 'rule sony 2 in MergeBrandProductKey ', ''); commit; -- lenovo rule 1 -- ZHAOYANG E45 --> ZHAOYANG E45 update brandproductkey as bpk, (select bp. brandproductid, bp. brandproductenname, bp. brandid, bpr. driverid from brandproduct as bp inner join (select brandid, brandproductid, max (driverinfoid) as driverid from brandproductdriverrelation group by brandid, brandproductid) as bpr on bp. brandid = bpr. brandid and bp. brandproductid = bpr. brandproductid and bp. brandid = 37) as bpp set bpk. brandproductid = bpp. brandproductid where bpk. brandproductid = 0 and bpk. computertype = 2 -- 0 and bpk. brandid = bpp. brandid and bpk. brandproductkeyname <> ''and instr (bpp. brandproductenname, SUBSTRING_INDEX (bpk. brandproductkeyname, '',-1)> 0 and bpp. brandproductenname regexp concat ('^ [^/x00-/xff] +', SUBSTRING_INDEX (bpk. brandproductkeyname, '',-1), '$'); commit; insert into t_runninglog values (current_timestamp (), 'rule lenovo 1 in MergeBrandProductKey ',''); commit; insert into t_runninglog values (current_timestamp (), 'finish in mergebrandproductkey', ''); commit; END

Here, this statement is used to trigger an error when the following statement is executed. after saving the error message to the log table, the stored procedure will continue to be executed backward when an error occurs.

    DECLARE CONTINUE HANDLER FOR SQLWARNING,SQLEXCEPTION    begin        insert into t_runninglog values(current_timestamp(),'error in MergeBrandProductKey',left(mysql_error(),255));        commit;    end;

The syntax structure of HANDLER is as follows:

DECLARE handler_type handler for condition_value [,...] sp_statement handler_type: CONTINUE | EXIT condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code Handlers type: 1, EXIT: exit the current code block when an error occurs (may be a subcode block or main code block) 2. CONTINUE: CONTINUE to execute the subsequent code condition_value: condition_value when an error is sent. standard SQLSTATE definitions are supported; SQLWARNING is a stenographer of all SQLSTATE codes starting with 01. not found is a stenographer of all SQLSTATE codes starting with 02. SQLEXCEPTION is for all SQLSTATE codes NOT captured by SQLWARNING or not found. except the SQLSTATE value, mySQL Error code is also supported, but for mysql, the priority is as follows: MySQL Error code> SQLSTATE code> naming condition

The mysql_error () function returns the text error message generated by the previous MySQL operation.

Reference: mysql_error

BTW: I am not familiar with mysql. I used oracle and recently used mysql. so I wrote this stored procedure to process data regularly on a weekly basis, if you have any suggestions for kids shoes, please comment on them. I will accept them modestly, but do not attack them. thank you.

BitsCN.com