MySQL Cursors — Walking Through Result Sets One Row at a Time

Most of the time you don't need cursors. SQL is built for set-based operations — UPDATE everything that matches a WHERE, SELECT all the rows you need at once. But sometimes you really do need to go row by row. Maybe you're calculating something that depends on the previous row's value. Maybe you need to call another stored procedure for each row. That's when you reach for a cursor.

A cursor just walks through a query result one row at a time.

Contents

• The Four Steps
• DECLARE
• OPEN
• FETCH
• CLOSE
• The NOT FOUND Handler
• Loops
• A Real Example
• Things to Watch Out For

The Four Steps

Every cursor follows the same routine:

DECLARE  -- name it, give it a SELECT
OPEN     -- fire the query
FETCH    -- grab one row
CLOSE    -- clean up

That's it. Skip CLOSE and you're leaking memory until the session dies.

DECLARE

You give the cursor a name and a SELECT statement. The SELECT can be as simple or as hairy as you need — joins, filters, grouping, all fair game.

DECLARE emp_cursor CURSOR FOR
    SELECT name, salary FROM employees WHERE active = 1;

Order matters inside a procedure. First your variables, then cursors, then handlers. MySQL will yell at you if you get it wrong.

-- 1. variables
DECLARE done INT DEFAULT 0;
DECLARE emp_name VARCHAR(100);

-- 2. cursors
DECLARE emp_cursor CURSOR FOR SELECT name, salary FROM employees;

-- 3. handlers
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Also — cursors only work inside stored programs (procedures, functions, triggers). You can't just run them in a plain SQL script.

OPEN

This runs the SELECT and builds the result set in memory. No rows are returned yet, just queued up.

OPEN emp_cursor;

At this point the cursor sits right before the first row, waiting for you to FETCH.

FETCH

Pulls the next row into your variables. The columns go in the order you wrote them in the SELECT — match them up carefully or you'll get garbage (or an error).

FETCH emp_cursor INTO emp_name, emp_salary;

Every FETCH moves forward one row. When there's nothing left, MySQL fires a NOT FOUND condition. That's how you know you're done.

One annoying thing — if your variable list doesn't match the column list, MySQL throws an error at runtime, not at creation. Double-check your SELECT matches your INTO.

CLOSE

Releases the memory. Do this.

CLOSE emp_cursor;

MysQL won't clean up after you when the procedure ends. If you OPEN it, CLOSE it.

The NOT FOUND Handler

Without this, your procedure blows up when FETCH runs out of rows. The standard pattern:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

You declare a variable called done (default 0), and this handler sets it to 1 when there are no more rows. Then in your loop you check done and bail out.

I showed the declaration order above — variables → cursors → handlers. If you put the handler before the cursor, MySQL rejects the whole procedure. Just follow the order.

Loops

You need a loop to keep FETCHing until done. Here are the three ways to write it in MySQL.

LOOP ... LEAVE (the one everyone uses)

CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT name, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;

    row_loop: LOOP
        FETCH emp_cursor INTO emp_name, emp_salary;

        IF done THEN
            LEAVE row_loop;
        END IF;

        -- your per-row code
        SELECT CONCAT(emp_name, ' earns ', emp_salary);
    END LOOP;

    CLOSE emp_cursor;
END;

You label the loop (row_loop:) and LEAVE it when done fires. Clean, readable, doesn't hide anything.

WHILE

Checks the condition before each iteration. If the table is empty, the body never runs — which is actually nice.

OPEN emp_cursor;

WHILE done = 0 DO
    FETCH emp_cursor INTO emp_name, emp_salary;

    IF NOT done THEN
        SELECT CONCAT(emp_name, ' earns ', emp_salary);
    END IF;
END WHILE;

CLOSE emp_cursor;

REPEAT

Checks after each iteration. The body always runs at least once, which is a problem on empty result sets — you'll FETCH, get nothing, and still enter the body. That's why you need the IF NOT done check inside.

OPEN emp_cursor;

REPEAT
    FETCH emp_cursor INTO emp_name, emp_salary;

    IF NOT done THEN
        SELECT CONCAT(emp_name, ' earns ', emp_salary);
    END IF;
UNTIL done END REPEAT;

CLOSE emp_cursor;

I'd stick with LOOP ... LEAVE. It's the most explicit and the hardest to misread.

A Real Example

Procedure that gives a 10% raise to everyone making under 50k, and prints who got it.

DELIMITER $$

CREATE PROCEDURE give_bonus()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10,2);

    DECLARE bonus_cursor CURSOR FOR
        SELECT id, name, salary FROM employees
        WHERE salary < 50000;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN bonus_cursor;

    bonus_loop: LOOP
        FETCH bonus_cursor INTO emp_id, emp_name, emp_salary;

        IF done THEN
            LEAVE bonus_loop;
        END IF;

        UPDATE employees
        SET salary = salary * 1.10
        WHERE id = emp_id;

        SELECT CONCAT(emp_name, ' got a bonus — was ', emp_salary, ', now ', ROUND(salary * 1.10, 2));
    END LOOP;

    CLOSE bonus_cursor;
END$$

DELIMITER ;

Call it:

CALL give_bonus();

Yes, you could do this with a single UPDATE. That's kind of the point — cursors are overkill for simple updates. But if the logic were more complex (different percentages per role, cap at a max, logging each change), the cursor starts making sense.

Things to Watch Out For

Cursors are slow. Like, noticeably slow. A cursor looping through 10,000 rows and updating them one by one takes way longer than a single UPDATE. The overhead of FETCH adds up fast. If you can do it with a set-based statement, do that instead.

Forward only. You can't go back a row. You can't jump to row 50. You FETCH and move on. That's the whole API.

Memory. The entire result set sits in server memory while the cursor is open. A cursor over a 5-million-row table will hurt.

Nested cursors are a headache. You can put a cursor inside another cursor's loop. But now you need two done variables, two handlers, two CLOSE calls, and you'll probably mess up the scope at least once. Avoid it if you can.

Cursors need a stored program. You can't write a cursor in a raw SQL file and run it with mysql CLI. Wrap it in a procedure.

Before you write a cursor, ask yourself: is there a way to do this with a regular SQL statement? Most of the time, there is. Cursors are a last resort, not a starting point.