Cursors

So far, we have worked with SQL in a set-based manner — processing many rows at once.

In this lesson, we will explore Cursors, which allow SQL to process one row at a time.

What is a Cursor?

A cursor is a database object used to retrieve and process rows one by one from a result set.

Cursors are mainly used inside:

• Stored procedures
• Triggers (with care)

They are helpful when row-by-row logic is unavoidable.

Why Cursors Exist

SQL is designed to work with sets of data. However, some scenarios require:

• Sequential processing
• Complex row-level logic
• Conditional actions per row

Cursors exist to handle these cases.

Cursor Lifecycle

A cursor follows four main steps:

1. DECLARE the cursor
2. OPEN the cursor
3. FETCH rows one by one
4. CLOSE the cursor

Basic Cursor Syntax (MySQL)

DECLARE cursor_name CURSOR FOR
SELECT column FROM table;
  

Cursors must be declared before handlers and logic blocks.

Example Scenario

Suppose we want to process employee salaries one row at a time.

+----+----------+--------+
| id | name     | salary |
+----+----------+--------+
| 1  | Alice    | 75000  |
| 2  | Bob      | 72000  |
| 3  | Charlie  | 60000  |
+----+----------+--------+
  

Complete Cursor Example

DELIMITER $$

CREATE PROCEDURE process_salaries()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE emp_salary INT;

  DECLARE salary_cursor CURSOR FOR
  SELECT salary FROM employees;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET done = 1;

  OPEN salary_cursor;

  read_loop: LOOP
    FETCH salary_cursor INTO emp_salary;
    IF done = 1 THEN
      LEAVE read_loop;
    END IF;

    -- row-by-row logic here
    -- example: print or calculate
  END LOOP;

  CLOSE salary_cursor;
END $$

DELIMITER ;
  

Explanation of Key Parts

• DECLARE CURSOR – defines the result set
• OPEN – executes the SELECT query
• FETCH – retrieves one row
• HANDLER – detects end of data
• CLOSE – releases resources

NOT FOUND Handler

The NOT FOUND handler is critical.

It tells MySQL what to do when no more rows are available.

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
  

Without this handler, the cursor would throw an error.

When Should You Use Cursors?

Use cursors only when:

• Row-by-row logic is unavoidable
• Complex calculations per row are required
• Set-based SQL cannot solve the problem

When NOT to Use Cursors

Avoid cursors when:

• JOINs can solve the problem
• UPDATE statements can handle all rows
• Performance is critical

Set-based SQL is almost always faster.

Cursors vs Set-Based SQL

Common Beginner Mistakes

• Forgetting to CLOSE the cursor
• Missing NOT FOUND handler
• Using cursors unnecessarily
• Expecting cursors to be fast

Best Practices

• Always prefer set-based SQL
• Use cursors only as a last resort
• Keep cursor logic simple
• Document cursor usage clearly

What’s Next?

In the next lesson, we will explore Window Functions, which provide powerful analytics without row-by-row processing.