MySQL - Scope Rules for Handlers

While working with stored procedures in MySQL if an exception or occurs the execution of the procedure terminates abruptly, to avoid this you need to handle the exceptions in MYSQL.

MySQL provides a handler to handle the exceptions in the stored procedures. You can handle these exceptions by declaring a handler using the MySQL DECLARE ... HANDLER Statement.

Example

Assume we have created a table with name tutorials in MySQL database using CREATE statement as shown below −

CREATE TABLE tutorials (
   ID INT PRIMARY KEY,
   TITLE VARCHAR(100),
   AUTHOR VARCHAR(40),
   DATE VARCHAR(40)
);

Now, we will insert 5 records in tutorials table using INSERT statements −

Insert into tutorials values
(1, 'Java', 'Krishna', '2019-09-01'),
(2, 'JFreeCharts', 'Satish', '2019-05-01'),
(3, 'JavaSprings', 'Amit', '2019-05-01'),
(4, 'Android', 'Ram', '2019-03-01'),
(5, 'Cassandra', 'Pruthvi', '2019-04-06');

Let us create another table to back up the data −

CREATE TABLE backup (
   ID INT,
   TITLE VARCHAR(100),
   AUTHOR VARCHAR(40),
   DATE VARCHAR(40)
);

Following is procedure demonstrates the usage of the HANDLER statement, it backups the contents of the tutorials table to the backup table using cursors −

DELIMITER // 
CREATE PROCEDURE ExampleProc()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE tutorialID INTEGER;
      DECLARE tutorialTitle, tutorialAuthor, 
	  tutorialDate VARCHAR(20);
      DECLARE cur CURSOR FOR SELECT * FROM tutorials;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN cur;
      label: LOOP
         FETCH cur INTO tutorialID, tutorialTitle, 
		 tutorialAuthor, tutorialDate;
         INSERT INTO backup VALUES(tutorialID, tutorialTitle, 
		 tutorialAuthor, tutorialDate);
         IF done = 1 THEN LEAVE label;
         END IF;
      END LOOP;
      CLOSE cur;
   END//
DELIMITER ;

You can call the above procedure as shown below −

CALL ExampleProc;

If you verify the contents of the backup table you can see the inserted records as shown below −

select * from backup;

Output

Following is the output of the above query −

Scope rules for handlers

Following are the scope rules for handlers −

• The scope of the handlers declared is limited to the statements following the handler declarations in the block.

• If you declare a handler in a particular block the scope of it is limited to the block with in which the handler is created. You cannot activate it for the conditions outside it.

• You can define two kinds of handlers, specific handlers (a MySQL error code, SQLSTATE value, or condition name) and general handlers (condition in the SQLWARNING, SQLEXCEPTION, or NOT FOUND class).

• Following is the precedence of various types of handlers.

MySQL error code handler-> SQLSTATE value handler-> 
SQLEXCEPTION -> general SQLWARNING-