Introduction to mysql cursor

By admin | January 17, 2017 | No Comments

mysql cursor

To handle a result set inside a stored procedure, you use a cursor. MySQL cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

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 don’t update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.

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

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

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 result set.

Finally, you call the CLOSE statement to deactivate the cursor and release the memory associated with it as follows:

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 FETCHstatement, 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:

The following diagram illustrates how MySQL cursor works.

MySQL Cursor Steps

MySQL Cursor Example

We are going to develop a stored procedure that builds an email list of all employees in theemployees table in the MySQL sample database.

First, we declare some variables, a cursor for looping over the emails of employees, and a NOT FOUND handler:

Finally, we close the cursor using the CLOSE statement:

In this tutorial, we have shown you how to use MySQL cursor to iterate a result set and process each row accordingly.

 

Source :http://www.mysqltutorial.org/mysql-cursor/

Show Buttons
Hide Buttons