MySQL Cursor

In this blog, you will study how to use MySQL cursor in stored procedures to repeat through a result set given back by a SELECT statement.

Introduction to MySQL cursor

To handle a result set exclusive a stored procedure, you practice a cursor. A cursor lets you to iterate a set of rows returned by a request and process each row consequently.

MySQL cursor is read-only, non-scrollable and asensitive.

• Read-only: you cannot update data in the original table using the cursor.

• Non-scrollable: you can only get rows in the order resolute by the SELECT statement. You cannot get rows in the inverted order. In accumulation, you cannot skip rows or jump to an exact row in the result set.

• Asensitive: there are two types of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the real data, while an insensitive cursor uses a temporary replica of the data. An asensitive cursor does faster than an insensitive cursor because it does not have to type a impermanent copy of data. But, any change that ended to the data from other connections will move the data that is being used by an asensitive cursor, so, it is harmless if you do not update the data that is actuality used by an asensitive cursor. MySQL cursor is asensitive.

You can practice MySQL cursors in stored procedures, stored functions, and triggers.

At work with MySQL cursor

First, you must state a cursor by means of the DECLARE statement:

The cursor statement must be later any variable statement. If you state a cursor before variables statement, MySQL will rise an error. A cursor must always be related with a SELECT statement.

Next, you exposed the cursor by by means of the OPEN statement. The OPEN statement sets the result set for the cursor, so, you must call the OPEN declaration before fetching rows from the result set.

Then, you practice the FETCH statement to recover the next row pointed by the cursor and change the cursor to the subsequent row in the result set.

Later that, you can crisscross to see if there is any row existing before fetching it.

To finish, you call the CLOSE statement to disable the cursor and release the memory related with it as follows:

When the cursor is no extended used, you must close it.

Once at work with MySQL cursor, you must also declare a NOT FOUND handler to switch the condition when the cursor cannot discover any row. Because each time you call the FETCH declaration, the cursor goes to read the next row in the result set. When the cursor ranges the end of the result set, it will not be clever to get the data, and a condition is raised up. The handler is used to handle this state.

To state a NOT FOUND handler, you use the subsequent syntax:

The ended is a variable to designate that the cursor has touched the end of the result set. Notice that the handler statement must seem after variable and cursor statement inside the stored procedures.

The subsequent diagram shows how MySQL cursor works.

MySQL Cursor Example

We are working to develop a stored procedure that shapes an email list of all employees in the employees table in the example database.

First, state some variables, a cursor for circling over the emails of employees, and a NOT FOUND handler:

Next, exposed the email_cursor by means of the OPEN statement:

Then, repeat the email list, and concatenate all emails where each email is detached by a semicolon(;):

Afterward, inside the loop we used the v_finished variable to crisscross if there is an email in the list to dismiss the loop.

Lastly, close the cursor by means of the CLOSE declaration:

The build_email_list stored procedure is as trails:

You can trial the build_email_list stored procedure using the subsequent script:

In this blog, we have exposed you how to use MySQL cursor to repeat a result set and process each row consequently.