Cursor

In MySQL, a cursor is a database object that lets you step through the rows of a result set one by one, typically inside a stored procedure or function. Instead of handling all matching rows in one big batch (as regular SELECT or UPDATE does), a cursor processes records sequentially, which is useful when you need per‑row logic that depends on external conditions, multiple steps, or external systems.

Cursors are most common in stored programs that must implement complex business rules, batch processing, or workflows where each row triggers a different set of actions, such as logging, validation, or calling other procedures.

How a Cursor Works

A cursor is tied to a specific SELECT statement and follows a four‑step pattern:

1. DECLARE the cursor and the SELECT that defines its result set.

2. OPEN the cursor to execute the underlying query and prepare the data for reading.

3. Fetch rows one at a time into local variables using the FETCH statement.

4. CLOSE the cursor when done to free up internal resources.

During the fetch loop, MySQL places the column values of the current row into the declared variables, and your code can then perform ordinary SQL operations (INSERT, UPDATE, DELETE, CALL, etc.) based on those values.

Syntax and Life Cycle

MySQL requires you to declare the cursor and its associated variables before opening it. You also usually declare a continue handler to handle the NOT FOUND condition when the last row has been read.

Basic Example

In this example, the cursor reads every active employee row, places the values into emp_id, emp_name, and emp_salary, and then inserts a log entry for each one. The process repeats until all rows are consumed.

Key Cursor Statements

• DECLARE CURSOR:

  • Defines the cursor and links it to a SELECT statement.

  • Must appear before OPEN and inside the BEGIN ... END block.

• OPEN cursor_name:

  • Executes the underlying SELECT and prepares the result set for fetching.

• FETCH cursor_name INTO var1, var2, ...:

  • Reads the next row and stores its column values in the declared variables.

• CLOSE cursor_name:

  • Releases the cursor and its internal resources.

• Continue handler for NOT FOUND:

  • A CONTINUE HANDLER that detects when there are no more rows and sets a flag to exit the loop. MySQL raises the NOT FOUND condition when FETCH reaches the end.

When to Use Cursors

Use a cursor when:

• You need row‑by‑row processing, such as:

  • Validating each record against business rules.

  • Calling external procedures or functions for each row.

  • Performing conditional inserts or updates that depend on the current row and other system state.

• You are implementing batch or ETL‑style logic that is easier to express in an iterative style (for example, “process each order and update multiple dependent tables”).

• You want fine‑grained control over the order and conditions under which each row is processed, including early exits or complex branching.

Downsides and Performance

• Performance overhead:

  • Iterating over rows one by one is usually much slower than set‑based operations such as UPDATE ... WHERE, INSERT ... SELECT, or JOIN‑based queries.

• Resource usage:

  • Each open cursor consumes memory and internal structures; leaving cursors open can waste resources, especially in long‑running procedures.

• Increased complexity:

  • Managing OPEN, FETCH, CLOSE, and loop‑control logic makes the code longer and harder to debug.

Because of these drawbacks, best practice in MySQL is to prefer set‑based SQL whenever possible and limit cursors to scenarios where per‑row control is genuinely required.

Cursor vs Set‑Based Operations

In many cases, you can mix cursors and set‑based operations: for example, use a cursor only for the “custom logic” step while still relying on set‑based SQL for bulk data changes.

For beginners, a cursor is like a fingerprint scanner going through a guest list one name at a time: it reads one record, checks it, and logs the visit, then moves to the next name, repeating until the list is finished. In MySQL, it does the same thing with database rows, giving you a way to implement detailed, step‑by‑step workflows inside stored procedures.

Summary

A cursor in MySQL allows stored procedures and functions to iterate over a result set row by row, enabling custom per‑row logic that cannot be expressed easily with pure SQL. It is declared, opened, fetched, and closed using specific SQL statements and usually paired with a NOT FOUND handler to control the loop. While very useful for complex, row‑oriented workflows, cursors should be used sparingly because set‑based operations are generally more efficient in MySQL.