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
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 row in the order determined by the SELECT statement. You cannot fetch row in the reversed order. In addition, you cannot skip row 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 data from other connections will affect the data being used by an asensitive cursor, therefore it is safer if you don’t update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.
Working with MySQL cursor
First, you have to declare a cursor by using the
DECLARE cursor_name CURSOR FOR SELECT_statement;
The cursor declaration must be after any variable declaration. If you declare a cursor before variables declaration, MySQL will issue an error. A cursor must always be associated with a
Next, you 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 cursor.
Then, you can use the
FETCH statement to retrieve the next row from the cursor and move the cursor to the subsequent row in the result set.
FETCH cursor_name INTO variables list;
And finally, you call the
CLOSE statement to deactivate the cursor and release the memory associated with it as follows:
When the cursor is no longer used, you should close it.
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;
Where 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 are going to developer a stored procedure that build an email list of all employees in the
employees table in our MySQL sample database.
First, we declare some variables, a cursor for looping through the email of employees, and a
NOT FOUND handler:
DECLARE finished INTEGER DEFAULT 0; DECLARE email varchar(255) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Next, we open the
email_cursor by using the
Then, we loop over the email and concatenate all emails separated by a semicolon(:):
get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email;
Finally, we close the cursor using the
build_email_list stored procedure is as follows:
DELIMITER $$ CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000)) BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email; CLOSE email_cursor; END$$ DELIMITER ;
You can test the
build_email_list stored procedure using the following script:
SET @email_list = ""; CALL build_email_list(@email_list); SELECT @email_list;
In this tutorial, we have shown you how to use MySQL cursor to iterate through a result set and process each individual row accordingly.