Summary: In this tutorial, you will learn how to use the MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.
Introduction to the MySQL cursor
To control a result set within a stored procedure, use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row individually.
The MySQL cursor is read-only, non-scrollable, and responsive.
- Read-only: You cannot update the data in the underlying table through the cursor
- Non-scrollable: You can only get rows in the order determined by the SELECT statement. You cannot get rows in the reverse order. In addition, you cannot skip rows or jump to a specific row in the result set.
- Responsive: There are two types of cursors: sensitive cursor and insensitive cursor. A sensitive cursor points to the actual data, while an insensitive cursor uses a temporary copy of the data. A sensitive cursor works faster than an insensitive cursor because you don’t have to make a temporary copy of the data. However, any changes made to data from other connections will affect the data that is being used by a sensitive cursor, therefore it is more secure if you do not update the data that is being used by a sensitive cursor. The MySQL cursor is sensitive.
.
You can use MySQL cursors in stored procedures, stored functions, and triggers.
Working with
the MySQL cursor
First, declare a cursor using the DECLARE:DECLARE statement
cursor_name CURSOR TO SELECT_statement; Code language: SQL (Structured Query Language) (sql)The
cursor declaration must be after any variable declaration. If you declare a cursor before variable declarations, MySQL will issue an error. A cursor must always be associated with a SELECT statement.
Then open the cursor using the OPEN statement. The OPEN statement initializes the result set for the cursor, so you must call the OPEN statement before getting rows from the result set.
OPEN cursor_name; Code language: SQL (Structured Query Language) (sql)
Then use the FETCH statement to retrieve the next row pointed to by the cursor, and move the cursor to the next row in the result set.
List of variables FETCH cursor_name IN; Code language: SQL (Structured Query Language) (sql)
After that, check if there are any rows available before searching for them.
Finally, disable the cursor and free the memory associated with it using the CLOSE: CLOSE statement
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 the MySQL cursor, you must also declare a NOT FOUND driver to handle the situation where the cursor could not find any rows.
Because every time the FETCH statement is called, 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 get the data and a condition is generated. The controller is used to control this condition.
To declare a NOT FOUND handler, use the following syntax:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; Code language: SQL (Structured Query Language) (sql)
The finish is a variable to indicate that the cursor has reached the end of the result set. Note that the controller declaration must appear after the variable and cursor declaration within the stored procedures.
The following diagram illustrates how the MySQL cursor works.
MySQL cursor example
We will develop a stored procedure that creates an email list of all employees in the employee table of the sample database.
First, declare some variables, a cursor to loop through employee emails, and a controller NOT FOUND:
DECLARE DEFAULT INTEGER 0 terminated;
DECLARE emailAddress varchar(100) DEFAULT “”; – declare cursor for the email of employees DEClARE curEmail CURSOR FOR SELECT email FROM employees; – declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; Code language: SQL (Structured Query Language) (sql)
Then open the cursor using the OPEN:OPEN
curEmail statement; Code language: SQL (Structured Query Language) (sql)
Then, iterate the email list and concatenate all emails where each email is separated by a semicolon(;)
: getEmail: LOOP FETCH curEmail INTO emailAddress; IF finished = 1 THEN LEAVE getEmail; END YES; – create email list SET emailList = CONCAT(emailAddress,”;”,emailList); END LOOP getEmail; Code language: SQL (Structured Query Language) (sql)After that, inside the loop,
we use the terminated variable to check if there is an email in the list to end the loop.
Finally, close the cursor using the CLOSE statement: CLOSE
email_cursor; Code language: SQL (Structured Query Language) (sql)The createEmailList
stored procedure is as follows:
DELIMITER $$ CREATE PROCEDURE createEmailList ( INOUT emailList varchar(4000) ) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE emailAddress varchar(100) DEFAULT “”; – declare cursor for the email of employees DEClARE curEmail CURSOR FOR SELECT email FROM employees; – declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN curEmail; getEmail: LOOP FETCH curEmail INTO emailAddress; IF finished = 1 THEN LEAVE getEmail; END YES; – create email list SET emailList = CONCAT(emailAddress,”;”,emailList); END LOOP getEmail; CLOSE curEmail; DELIMITER END$$ ; Code language: SQL (Structured Query Language) (sql)
You can test the createEmailList stored procedure by using the following script:
SET @emailList = “”; CALL createEmailList(@emailList); SELECT @emailList; Code language: SQL (Structured Query Language) (sql)
In this tutorial, we’ve shown you how to use the MySQL cursor to iterate a result set and process each row accordingly.