MySQL Cursor Tutorial and Example Code

This tutorial will give you a quick breakdown of using MySQL cursors in a stored procedure, stored functions, or triggers to run through a result from the SELECT statement.

In most cases, when executing SQL queries using either MySQL shell or MySQL workbench, we do not save the results, even though we have the functionality to save the results processed by the server using a stored procedure.

In this tutorial, I will not discuss stored procedures, functions, or triggers. Instead, I will simply dive into showing you how you can use MySQL cursors to run the results in the stored procedures.

Before we begin creating and using a cursor with examples, let us take a moment to discuss a few key points about a cursor that you should be aware of:

Features of a MySQL Cursor

A cursor is read-only and cannot update or remove data in the result set from the procedure.
A cursor needs to be declared before it can be used. The cursor definition is only a step to tell MySQL that such a cursor exists and does not retrieve and data.
You can only retrieve data in the order specified by the select statement and not in any reverse order, commonly known as non-scrollable.
You use a cursor by opening it and then perform fetch operations on the data stored.
You must close a cursor after the fetch operations complete.

Now that we know what a cursor means, we can begin to illustrate how it works using real-world examples:

Basic Usage

The general syntax to declare a cursor in MySQL is straightforward. We start by using the DECLARE keyword as shown in the sample query below:

How to Declare a Cursor

DECLARE cursor_name CURSOR FOR SELECT_expression;

The cursor_name is the name given to the cursor during the declaration. Notice that the declaration of a cursor should be after any declared variables to prevent MySQL from resulting in errors.

Next is the SELECT_expression, which stores the SELECT statement associated with the cursor.

How to Open a Cursor

Once we have a cursor declared and MySQL is aware that the cursor exists, we can start using it which requires the cursor to be opened.

The general syntax to open a cursor is as shown in the query below:

OPEN cursor_name;

This command opens the cursors referenced by its name and one can start using it.

How to Retrieve Data

Having the cursor open allows you to fetch the information stored in either a procedure, a function or a trigger.

The general syntax to fetch data using a cursor is as shown:

FETCH cursor_name INTO variables;

NOTE: As mentioned, ensure that the cursor is used after the variables are declared to avoid errors.

How to Close and Release a Cursor

Once the operations requiring the defined cursor are completed, it is best to close the cursor which frees up the memory associated with it.

Once a cursor has been closed, a user needs to re-open the cursor using the OPEN keywords (shown above) before using the cursor.

You do not need to declare the cursor after the close statement.

The general syntax to close a cursor is as shown in the query below:

CLOSE cursor_name;

Error Handling

MySQL cursor works by recursively reading the next row in the result set. If the next row is unavailable, the cursor will close and not return the data unless specified. This can be a problem especially after the cursor reaches the end of the result.

As a solution, a NOT FOUND handler is defined. This specifies the action to be taken if the next row is not found.

The general syntax for error handling when using a cursor is:

DECLARE CONTINUE HANDLER NOT FOUND SET terminate = true;

The terminate value is a variable used to specify that the cursor has reached the end of the result. The variable name can be any name as long it fits the MySQL variable naming convention.

NOTE: Like all the variables used in a cursor, it must be defined before it is used in the cursor.

Example Use Case

Let us create a cursor that collects customers’ emails available in the customer table of the Sakila sample database.

The resource for downloading and installing the Sakila database is below:

https://dev.mysql.com/doc/sakila/en/

The following illustrates a procedure that uses a cursor to fetch emails:

USE sakila;
DELIMITER $$
CREATE PROCEDURE createNewsletter(
INOUT emails VARCHAR(4000)
)
BEGIN
DECLARE terminate INT DEFAULT FALSE;
DECLARE emailAddr VARCHAR(255) DEFAULT "";
DECLARE collect_email CURSOR FOR SELECT email FROM sakila.customer WHERE (address_id > 100 AND address_id < 200);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET terminate = TRUE;
OPEN collect_email;
getEmails: LOOP
FETCH collect_email INTO emailAddr;
IF terminate = TRUE THEN
LEAVE getEmails;
END IF;
SET emails = CONCAT(emailAddr, "|", emails);
END LOOP getEmails;
CLOSE collect_email;
END$$
DELIMITER ;
SET @emails = "";
CALL createNewsLetter(@collect_email);
SELECT @collect_email;

Once the query executes, you will get an output as shown below:

Conclusion

In this tutorial, we walked through using MySQL cursors to parse the data stored in the result set. Consider the documentation to master how to implement the cursors.