Navicat Blog

Iterate over Query Result Sets Using a Cursor May 4, 2021 by Robert Gravelle

Being a transactional programming language, SQL is designed to execute its work in an all-or-nothing capacity. Meanwhile, procedural programming languages such as C# and Java are often iterative in nature. As such, they tend to loop over the same code until the stack is diminished and fully processed. Cursors are a notable exception to SQL's transactional approach. Like WHILE loops, cursors allow programmers to process each row of a SELECT result set individually by iterating over them. While many SQL purists shun cursors out of disdain or fear, they have their place in database development and are well worth learning. To that end, today's blog will describe when and how to use cursors within your stored procedures.

Cursors Defined

As mentioned above, a database cursor is a special control structure that enables traversal over the records in a database in order to process individual rows of a query result set for sequential processing. In Stored Procedures, a cursor makes it possible to perform complex logic on a row by row basis.

Cursors have three important properties:

  • Asensitive: The server may or may not make a copy of its result table.
  • Read-only: The data may not be updated.
  • Nonscrollable: Can be traversed only in one direction and cannot skip rows.

How to Use a Cursor

Using a cursor within a stored procedure is a four step process:

  • Declare a cursor.
  • Open a cursor.
  • Fetch the data into variables.
  • Close the cursor when done.

Declare a Cursor

The following statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor:

DECLARE cursor_name 
CURSOR FOR select_statement

Open a Cursor

The following statement opens a previously declared cursor.

OPEN cursor_name

Fetch the Data into Variables

This statement fetches the next row for the SELECT statement associated with the specified cursor (which must be open) and advances the cursor pointer. If a row exists, the fetched columns are stored in the named variable(s). The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.

FETCH [[NEXT] FROM] cursor_name 
INTO var_name [, var_name] ...

Close the Cursor When Done

This statement closes the cursor. An error occurs if the cursor is not open.

CLOSE cursor_name

A Practical Example

Here's the definition of a stored procedure (shown in Navicat for MySQL) that employs a cursor to generate a list of emails for all staff members in the Sakila sample database:

cursor_definition (81K)

Within the getEmail LOOP, the cursor iterates over the email list, and concatenates all emails separated by a semicolon (;). The finished variable informs the cursor to terminate the loop when there was no email fetched. Here is the value of the emailList after execution of the stored procedure:

cursor_result (22K)

Conclusion

In today's blog we learned when and how to use cursors within your stored procedures.

Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!

Navicat Blogs
Feed Entries
Blog Archives
Share