70.605
Efficient Data Retrieval with Cursors

---

Objectives

Upon completion of this lesson, you will be able to:

• explain the need for cursors
• know when to use cursors
• use cursors in MySQL stored procedures

---

Overview

In relational databases, a cursor is a programming construct that allows a developer to traverse and manipulate the result set returned by a SQL SELECT query. It is typically used in stored procedures or other database programming scenarios where the developer needs to work with individual rows of a result set one at a time.

Cursors can be useful in situations where the data needs to be processed row-by-row and where it is not practical or efficient to load the entire result set into memory at once. For example, if a stored procedure needs to perform some calculations on each row of a large result set, using a cursor can help to conserve memory and reduce the amount of processing required at any given time.

However, it is important to note that cursors can also be a source of performance problems if not used carefully, since they can require additional overhead and may lead to locking and blocking issues in concurrent database access situations if not properly managed. Therefore, it is generally recommended to use cursors only when necessary and to consider alternative approaches, such as in-memory traversal, whenever possible.

Cursors in MySQL

To create and use a cursor in MySQL, follow these steps:

1. Declare the cursor: To declare a cursor, you can use the DECLARE statement followed by a unique name for the cursor and the SELECT statement that defines the result set. For example:

DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;

2. Open the cursor: To open the cursor, you can use the OPEN statement followed by the cursor name. For example:

OPEN my_cursor;

3. Fetch the rows: To fetch the rows one by one, you can use the FETCH statement followed by the cursor name and the variable names that will hold the values of the columns in each row. For example:

FETCH my_cursor INTO var1, var2, var3;

You can use a loop to fetch all the rows one by one until there are no more rows to fetch.

4. Close the cursor: When you are done fetching the rows, you should close the cursor using the CLOSE statement followed by the cursor name. For example:

CLOSE my_cursor;

The cursors provided by MySQL are embedded cursors and are subject to some restrictions and have certain characteristics. Specifically, they are:

READ ONLY, i.e., is not possible to update any table through a cursor

Non-Scrollable, i.e., with cursors you can only retrieve records from a table in one direction

Asensitive, i.e., cursors are insensitive to the changes that are made in the table and any modifications made in the table are not reflected in the cursor; that implies that a cursor moving over all the records in a table would not see any changes made to the table after the cursor is created

Sample Database

The SQL statement below creates the customers and export tables used in the examples.

Using Cursors from Stored Procedures

Cursors are most commonly employed within stored procedures. The example below defines a simple stored procedure that uses a cursor:

CREATE PROCEDURE ExportDataProc()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE custID INTEGER;
      DECLARE custProf, custOrigin VARCHAR(64);
      DECLARE aCursor CURSOR FOR SELECT cid, profession, country FROM customers;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN aCursor;
      label: LOOP
      FETCH aCursor INTO custID, custProf, custOrigin;
      INSERT INTO export VALUES(custID, custProf, custOrigin);
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;
      CLOSE aCursor;
   END

To demonstrate the the cursor and stored procedure work, we will call the procedure from R:

status <- dbExecute(conn = con,
                    "CALL ExportDataProc();")

Summary

Cursors in relational databases allow a developer to linearly traverse the result set returned by a SQL SELECT query. They are useful when data needs to be processed row-by-row, and loading the entire result set into memory at once is not appropriate.

MySQL cursors are read-only, non-scrollable, and insensitive to changes made to the table while the cursor is active.

Developers should use cursors only when necessary and consider alternative approaches such as in-memory traversal.

---

Files & Resources

All Files for Lesson 70.605

---

See Also

• 70.907 – Stored Procedures in MySQL

References

TutorialsPoint: MySQL Cursor DECLARE Statement

Errata

None collected yet. Let us know.