Mysql stored Procedure exception handling sample code sharing _mysql

The following is an example code that stores exception information in the log table when an exception occurs, and continues to run subsequent statements.

If you have any better suggestions, please do not hesitate to enlighten me.

Stored procedure Exception Handling example

Copy Code code as follows:

-- --------------------------------------------------------------------------------
--Routine DDL
--note:comments before and the routine body won't 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, ' warnings in Mergebrandproductkey ', concat (@ @warning_count, ' Warnings '));
Commit
End
INSERT into T_runninglog values (Default,default, ' Start in Mergebrandproductkey ', ');
Commit
--Task execution principal begins
-- /*
-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
---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
---Bpk.computertype = 2--0
and Bpk.brandid = Bpp.brandid
and bpp.brandproductenname like Concat (Bpk.brandproductkeyname, '/% ');
Commit
INSERT into T_runninglog values (default,default, ' The rule of 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
---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, ' The rule of 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
---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, ' The rule of Lenovo 1 in Mergebrandproductkey ', ');
Commit
--HP Rule 1
--HP Compaq 6535s--> hp Compaq 6535s 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
---Bpk.computertype = 2--0
and Bpk.brandid = Bpp.brandid
and Bpk.brandproductkeyname <> '
and Bpp.brandproductenname = concat (Bpk.brandproductkeyname, ' notebook computer ');
INSERT into T_runninglog values (default,default, ' rule hp 1 in Mergebrandproductkey ', ');
Commit
--HP Rule 2
--HP Compaq 6535s--> hp Compaq 6535s 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
---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
-- */
--end of task execution body
INSERT into T_runninglog values (Default,default, ' finish in Mergebrandproductkey ', ');
Commit
End

The syntax structure for handler is as follows:

Copy Code code as follows:

DECLARE Handler_type handler for condition_value[,...] Sp_statement
Han Dler_type:continue | EXIT
Condition_value:sqlstate [value] sqlstate_value | condition_name | sqlwarning | Not FOUND | SQLEXCEPTION | Mysql_error_code
Handlers Type:
, exit: Exits the current code block (possibly a child code block or main code block) when an error occurs
, CONTINUE: Continue executing subsequent code when sending an error
Cond Ition_value:
Condition_value supports standard SQLSTATE definitions;
SQLWarning is shorthand for all SQLSTATE code that starts with 01
not Found is shorthand for all SQLSTATE code that starts with 02
SqlException is shorthand for all found code that is not captured by sqlwarning or not sqlstate
except SQLSTATE values, The MySQL error code is also supported
but for MySQL, the priority is as follows:
MySQL error code > SQLSTATE Code > Naming conditions