MySQL Cursor

Summary: in this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

Introduction to MySQL cursor

In MySQL, a cursor is a database object used for iterating the result of a SELECT statement.

Typically, you use cursors within stored procedures, triggers, and functions where you need to process individual rows returned by a query one at a time.

Here’s the basic syntax of a cursor:

-- declare a cursor
DECLARE cursor_name CURSOR FOR 
SELECT column1, column2 
FROM your_table 
WHERE your_condition;

-- open the cursor
OPEN cursor_name;

FETCH cursor_name INTO variable1, variable2;
-- process the data


-- close the cursor
CLOSE cursor_name;Code language: PHP (php)

How it works:

First, declare a cursor by using the DECLARE statement:

DECLARE cursor_name CURSOR FOR 
SELECT column1, column2 
FROM your_table 
WHERE your_condition;Code language: SQL (Structured Query Language) (sql)

The cursor declaration must come after any variable declaration. If you declare a cursor before the variable declarations, MySQL will issue an error. Additionally, a cursor must always associate with a SELECT statement.

Next, open the cursor using the OPEN statement:

OPEN cursor_name;Code language: SQL (Structured Query Language) (sql)

The OPEN statement initializes the result set for the cursor; therefore, you must call the OPEN statement before fetching rows from the result set.

Then, retrieve the next row pointed by the cursor and advance the cursor to the subsequent row in the result set using the FETCH statement:

FETCH cursor_name INTO variable1, variable2;Code language: SQL (Structured Query Language) (sql)

After that, check if there is any row available before fetching it.

Finally, deactivate the cursor and release the memory associated with it using the CLOSE statement:

CLOSE cursor_name;Code language: SQL (Structured Query Language) (sql)

It is a good practice to always close a cursor when it is no longer used.

When working with MySQL cursor, you must also declare a NOT FOUND handler to manage the situation when the cursor cannot find any row.

Each time you call the FETCH statement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to retrieve the data, and a condition is raised. The handler is used to handle this condition.

To declare a NOT FOUND handler, you use the following syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;Code language: SQL (Structured Query Language) (sql)

The finished is a variable to indicate that the cursor has reached the end of the result set.

Notice that the handler declaration must appear after the variable and cursor declarations within the stored procedures.

The following diagram illustrates how MySQL cursor works.

MySQL Cursor Example

We’ll develop a stored procedure that creates an email list of all employees in the employees table in the sample database:

The following example illustrates how to use a cursor to iterate all rows in the employees table and concatenate the email into a string:

DELIMITER $$

CREATE PROCEDURE create_email_list (
	INOUT email_list TEXT
)
BEGIN
	DECLARE done BOOL DEFAULT false;
	DECLARE email_address VARCHAR(100) DEFAULT "";
    
	-- declare cursor for employee email
	DECLARE cur CURSOR FOR SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = true;
	
    -- open the cursor
	OPEN cur;
	
    SET email_list = '';
	
    process_email: LOOP
		
        FETCH cur INTO email_address;
        
		IF done = true THEN 
			LEAVE process_email;
		END IF;
		
        -- concatenate the email into the emailList
		SET email_list = CONCAT(email_address,";",email_list);
	END LOOP;
    
    -- close the cursor
	CLOSE cur;

END$$

DELIMITER ;Code language: PHP (php)

How it works.

First, declare the variable done and initialize its value to false and the email_address and initialize its value to ''.

DECLARE done BOOL DEFAULT false;
DECLARE email_address VARCHAR(100) DEFAULT "";Code language: PHP (php)

Second, declare a cursor that will fetch email from the employees table:

DEClARE cur CURSOR FOR SELECT email FROM employees;Code language: SQL (Structured Query Language) (sql)

Third, open the cursor by using the OPEN statement:

OPEN cur;Code language: SQL (Structured Query Language) (sql)

Fourth, concatenate each email into the mail_list variable using a loop statement, separating email addresses by semicolons:

process_email: LOOP
		
        FETCH cur INTO email_address;
        
		IF done = true THEN 
			LEAVE process_email;
		END IF;
		
        -- concatenate the email into the emailList
		SET email_list = CONCAT(email_address,";",email_list);
	END LOOP;Code language: SQL (Structured Query Language) (sql)

Finally, close the cursor using the CLOSE statement:

CLOSE email_cursor;Code language: SQL (Structured Query Language) (sql)

The following tests the createEmailList stored procedure:

CALL create_email_list(@email_list); 
SELECT @email_list\GCode language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
@email_list: [email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];[email protected];
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use a MySQL cursor to process a result set row by row individually.

Was this tutorial helpful?