If your Interested to learn about the MySQL Stored Function
In MySQL, Cursor can also be created. Following are the steps for creating a cursor. MySQL cursor is read-only, non-scrollable and asensitive.
- Read-only: you cannot update data in the underlying table through the cursor.
- Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
- Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore, it is safer if you do not update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.
Working with MySQL cursor
First, declare a cursor by using the DECLARE
statement:
DECLARE cursor_name CURSOR FOR SELECT_statement;
Code language: SQL (Structured Query Language) (sql)
The cursor declaration must be after any variable declaration. If you declare a cursor before the variable declarations, MySQL will issue an error. A cursor must always associate with a SELECT
statement. Next, open the cursor by using the OPEN
statement. The OPEN
statement initializes the result set for the cursor, therefore, you must call the OPEN
statement before fetching 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 by the cursor and move the cursor to the next row in the result set.
FETCH cursor_name INTO variables list;
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 handle the situation when the cursor could not find any row. Because 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 get 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 variable and cursor declaration inside 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.

First, declare some variables, a cursor for looping over the emails of employees, and a NOT FOUND
handler:
<code> DECLARE finished INTEGER DEFAULT 0; DECLARE emailAddress varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE curEmail CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; </code><small>Code language: SQL (Structured Query Language) (sql)</small>
Next, open the cursor by using the OPEN
statement:
OPEN curEmail;
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 IF;
-- build email list
SET emailList = CONCAT(emailAddress,";",emailList);
END LOOP getEmail;
Code language: SQL (Structured Query Language) (sql)
After that, inside the loop, we used the finished
variable to check if there is an email in the list to terminate 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:
<code>DELIMITER $$ CREATE PROCEDURE createEmailList ( INOUT emailList varchar(4000) ) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE emailAddress varchar(100) DEFAULT ""; -- declare cursor for employee email 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 IF; -- build email list SET emailList = CONCAT(emailAddress,";",emailList); END LOOP getEmail; CLOSE curEmail; END$$ DELIMITER ;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
You can test the createEmailList
stored procedure using the following script:
SET @emailList = ""; CALL createEmailList(@emailList); SELECT @emailList;
Types of Cursor
1. Declare Cursor
A cursor is a select statement, defined in the declaration section in MySQL.
Syntax
DECLARE cursor_name CURSOR FOR Select statement;
Parameter:
cursor_name: name of the cursor
select_statement: select query associated with the cursor1
2. Open Cursor
After declaring the cursor the next step is to open the cursor using open statement.
Syntax
Open cursor_name;
Parameter:
cursor_name: name of the cursor which is already declared.
3. Fetch Cursor
After declaring and opening the cursor, the next step is to fetch the cursor. It is used to fetch the row or the column.
Syntax
FETCH [ NEXT [ FROM ] ] cursor_name INTO variable_list;
Parameter:
cursor_name: name of the cursor
variable_list: variables, comma separated, etc. is stored in a cursor for the result set
4. Close Cursor
The final step is to close the cursor.
Syntax
Close cursor_name;
Parameter:
Cursor_name: name of the cursor
Example for the cursor:
Step 1: Open the database and table.

Step 2: Now create the cursor.
Query:

Step 3: Now call the cursor.
Query:
SET @name_list =""; CALL list_name(@name_list); SELECT @name_list;
