MySQL - DECLARE ... HANDLER Statement

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.

DECLARE ... HANDLER Statement

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.

Syntax

Following is the syntax of the MySQL HANDLER Statement −

DECLARE handler_action HANDLER
   FOR condition_value
   statement

The handler_action

The handler_action is the action to be performed when the given condition(s) are satisfied. You can provide the following as values for handler actions.

  • CONTINUE − The current program will continue execution of the procedure.

  • EXIT − This terminates the execution of the procedure.

  • UNDO − InnoDB does not support this action.

The condition_value

The condition_value is the condition to be satisfied, you can pass multiple condition values. You can provide the following as values for condition value.

  • mysql_error_code − This is an integer literal indicating the error code.

  • sqlstate_value − This is a 5-character string literal specifying the SQLSTATE value.

  • condition_name − The name of the user defined condition specified with DECLARE ... CONDITION.

  • SQLWARNING − Shorthand value for SQLSTATE starts with '01'.

  • NOT FOUND − Shorthand value for SQLSTATE starts with '02'.

  • SQLEXCEPTION − Short hand value to specify the exception.

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

The above query produces the following output −

ID TITLE AUTHOR DATE
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
Advertisements