MySQL Cursors

A cursor in MySQL is a database object used to retrieve and process rows from a result set one row at a time. While standard SQL queries return all matching rows at once, a cursor allows iterating through the result row by row — useful inside stored procedures when each row needs individual processing logic.

When to Use Cursors

Cursors are used inside stored procedures when row-by-row processing is required — for example, applying different logic to each row, generating a custom report, or processing batches of data conditionally.

Cursor Lifecycle

1. DECLARE — Define the cursor with a SELECT statement
2. OPEN — Execute the query and prepare rows for fetching
3. FETCH — Retrieve one row at a time into variables
4. CLOSE — Release the cursor when done

Syntax Overview

DECLARE cursor_name CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cursor_name;

fetch_loop: LOOP
    FETCH cursor_name INTO variable1, variable2;
    IF done = 1 THEN LEAVE fetch_loop; END IF;
    -- process row here
END LOOP;

CLOSE cursor_name;

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 72000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 45000);
INSERT INTO employees VALUES (3, 'Karan Das', 88000);

Example: Cursor to Apply Salary Bonus

DELIMITER //

CREATE PROCEDURE apply_bonus()
BEGIN
    DECLARE v_emp_id INT;
    DECLARE v_salary DECIMAL(10,2);
    DECLARE done INT DEFAULT 0;

    DECLARE emp_cursor CURSOR FOR
        SELECT emp_id, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;

    salary_loop: LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary;
        IF done = 1 THEN
            LEAVE salary_loop;
        END IF;

        IF v_salary < 50000 THEN
            UPDATE employees SET salary = v_salary * 1.10 WHERE emp_id = v_emp_id;
        ELSE
            UPDATE employees SET salary = v_salary * 1.05 WHERE emp_id = v_emp_id;
        END IF;
    END LOOP;

    CLOSE emp_cursor;
END //

DELIMITER ;

CALL apply_bonus();

The cursor fetches one employee at a time. Employees earning below 50000 get a 10% bonus; others get 5%.

Key Components Explained

DECLARE CONTINUE HANDLER

This handles the "no more rows" condition. When all rows have been fetched, MySQL triggers a NOT FOUND event. The handler sets the done flag to 1, which causes the loop to exit cleanly.

LEAVE

LEAVE loop_label exits the loop when the done flag is set.

Cursor Restrictions in MySQL

• Cursors are read-only — they cannot update the rows they are currently pointing to directly through the cursor itself.
• Cursors are forward-only — they move one direction, from the first row to the last.
• Cursors must be declared before any HANDLER declarations.

Key Points

• A cursor retrieves and processes query results one row at a time inside stored procedures.
• The lifecycle is: DECLARE → OPEN → FETCH in a loop → CLOSE.
• A CONTINUE HANDLER FOR NOT FOUND is essential to detect when the last row is reached.
• Cursors are useful for complex row-level logic that cannot be expressed as a single set-based query.
• Use cursors sparingly — set-based operations are faster than row-by-row processing in most cases.