MariaDB: Set up a Handler for Cursor's NOT FOUND condition

This MariaDB tutorial explains how to set up a handler for the NOT FOUND condition for a cursor in MariaDB with syntax and examples.

Description

If you try to fetch data from a cursor and there are no rows, MariaDB will raise a NO DATA error. You can set up a handler for the NOT FOUND condition so that your stored program does not terminate with an error.

Syntax

The syntax to set up a handler for the NOT FOUND condition for a cursor in MariaDB is:

DECLARE CONTINUE HANDLER FOR NOT FOUND [ set_condition ];

Parameters or Arguments

set_condition

The condition to set when the NOT FOUND condition is encountered by the cursor.

Example

Let's look at how to set up a handler for the NOT FOUND condition for a cursor in MariaDB.

First, we need to declare a variable that will be set when the NO DATA error occurs.

DECLARE done INT DEFAULT FALSE;

Next, we need to declare the cursor.

DECLARE c1 CURSOR FOR
  SELECT SUM(file_size)
  FROM pages
  WHERE site_name = name_in;

Finally, we need to declare a handler for the NOT FOUND condition for the cursor.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

Below is a function that demonstrates how to put all of these components together to set up a handler for the NOT FOUND condition for a cursor in MariaDB so that your stored program will not terminate with an error.

DELIMITER //

CREATE FUNCTION FindSize ( name_in VARCHAR(50) )
RETURNS INT READS SQL DATA

BEGIN

   DECLARE done INT DEFAULT FALSE;
   DECLARE TotalSize INT DEFAULT 0;

   DECLARE c1 CURSOR FOR
     SELECT SUM(file_size)
     FROM pages
     WHERE site_name = name_in;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   OPEN c1;
   FETCH c1 INTO TotalSize;

   CLOSE c1;

   RETURN TotalSize;

END; //

DELIMITER ;