3

Does error handler can be declared outside procedure?

DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

INSERT INTO ad_type VALUES (3, 'test');

INSERT INTO ad_type_languages VALUES (3, 'TEST', 'en' , 'yes');

COMMIT;

when i try to execute this code i get an error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK' at line 3

when i execute it in the procedure it runs ok, but i need this in script without stored procedure.

Thanks in advance.

p.s.

will this three handles catch all errors?

Miljankg
  • 41
  • 3

1 Answers1

2

Declare exit handler can only be declared inside a stored procedure, function or trigger if i am not mistaken. There is no possibility to declare it outside that.

Xnoise
  • 502
  • 2
  • 9
  • That's right. As the documentation says: "DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements." So, it's also the case for all DECLARE statements. https://dev.mysql.com/doc/refman/8.0/en/declare.html – Pierre Oct 17 '18 at 09:49