I checked a lot of information on the Internet and found that mysql exception handling information is a list of error numbers. during normal operation, we may want to record more accurate error messages to the log.
I checked a lot of information on the Internet and found that mysql exception handling information is a list of error numbers. during normal operation, 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.
Example of handling Stored Procedure exceptions
The Code is as follows:
----------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
----------------------------------------------------------------------------------
DELIMITER $
Create definer = 'driveradmin' @ '%' PROCEDURE 'merge _ BrandProductKey '()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
Begin
Insert into t_runninglog values (default, default, 'exception in mergebrandproductkey', concat (@ error_count, 'errors '));
Commit;
End;
DECLARE CONTINUE HANDLER FOR SQLWARNING
Begin
Insert into t_runninglog values (default, default, 'warningings in mergebrandproductkey', concat (@ warning_count, 'warnings '));
Commit;
End;
Insert into t_runninglog values (default, default, 'start in mergebrandproductkey ','');
Commit;
-- Task execution subject starts
--/*
-- 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 (default, default, '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 (default, default, 'rule' sony 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 (default, default, '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 (default, default, 'rule' lenovo 1 in MergeBrandProductKey ','');
Commit;
-- HP rule 1
-- HP Compaq 6535 s --> HP Compaq 6535 s laptop
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 = 36
) 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 bpp. brandproductenname = concat (bpk. brandproductkeyname, 'laptop ');
Insert into t_runninglog values (default, default, 'rule-hp 1 in mergebrandproductkey ','');
Commit;
-- HP rule 2
-- HP Compaq 6535 s --> HP Compaq 6535 s Notebook PC
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 = 36
) 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 upper (bpp. brandproductenname) = upper (concat (bpk. brandproductkeyname, 'notebook pc '));
Insert into t_runninglog values (default, default, 'rule-hp 2 in mergebrandproductkey ','');
Commit;
--*/
-- Task execution subject ends
Insert into t_runninglog values (default, default, 'finish in mergebrandproductkey ','');
Commit;
END
The syntax structure of HANDLER is as follows:
The Code 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:
, EXIT: EXIT the current code block when an error occurs (it may be a Subcode block or a main code block)
, CONTINUE: CONTINUE executing the subsequent code when an error is sent
Condition_value:
Condition_value supports standard SQLSTATE definitions;
SQLWARNING is a shorthand for all SQLSTATE codes starting with 01.
Not found is a shorthand for all SQLSTATE codes starting with 02.
SQLEXCEPTION is a shorthand for all SQLSTATE code NOT captured by SQLWARNING or not found.
Besides the SQLSTATE value, the MySQL error code is also supported.
However, for mysql, the priority is as follows:
MySQL Error code> SQLSTATE code> naming Conditions
