MySQL Cursor

Master MySQL cursors for stored programs — DECLARE/OPEN/FETCH/CLOSE lifecycle, the strict variables→cursors→handlers DECLARE order, the CONTINUE HANDLER FOR NOT FOUND idiom, and the read-only/forward-only/asensitive properties. Includes the critical advice that set-based SQL is almost always faster than cursor loops.

A cursor is a database object that lets you walk through the rows returned by a SELECT statement one at a time. Instead of receiving the entire result set at once, you fetch row-by-row, process each, then move to the next.

Cursors only exist inside stored procedures, functions, and triggers — there's no top-level cursor syntax in MySQL. You declare the cursor, open it, fetch rows in a loop until exhausted, then close it.

MySQL cursors have specific characteristics that distinguish them from cursors in other databases:

Inside a BEGIN ... END block, declarations must appear in this exact order:

1. Local variables (DECLARE x INT;)
2. Cursors (DECLARE c CURSOR FOR ...;)
3. Handlers (DECLARE ... HANDLER FOR ...;)

Get the order wrong and MySQL throws a syntax error. The cursor must come after any variable it might use, and before any handler that references the cursor's NOT FOUND condition.

When FETCH runs out of rows, MySQL raises a NOT FOUND condition (SQLSTATE 02000). Without a handler, this would terminate the procedure. The standard idiom catches it and sets a flag:

DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Inside the loop, check the flag and LEAVE when set:

my_loop: LOOP
    FETCH my_cur INTO ...;
    IF done = 1 THEN LEAVE my_loop; END IF;
    -- process the row
END LOOP my_loop;

Both examples below use the same employees table. Run this once if it's not already loaded:

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id     INT UNSIGNED PRIMARY KEY,
    first_name      VARCHAR(20),
    last_name       VARCHAR(25)    NOT NULL,
    salary          DECIMAL(10, 2) NOT NULL,
    department_id   INT
);

INSERT INTO employees VALUES
    (100, 'Steven',    'King',      24000.00,  9),
    (101, 'Neena',     'Kochhar',   17000.00,  9),
    (102, 'Lex',       'De Haan',   17000.00,  9),
    (103, 'Alexander', 'Hunold',     9000.00,  6),
    (104, 'Bruce',     'Ernst',      6000.00,  6),
    (105, 'David',     'Austin',     4800.00,  6),
    (106, 'Valli',     'Pataballa',  4800.00,  6),
    (107, 'Diana',     'Lorentz',    4200.00,  6),
    (108, 'Nancy',     'Greenberg', 12000.00, 10),
    (109, 'Daniel',    'Faviet',     9000.00, 10);

Walk through the employees and build a single semicolon-separated string of full names. The procedure takes an INOUT parameter — meaning it can both read and write the value:

DROP PROCEDURE IF EXISTS listnames;

DELIMITER $$

CREATE PROCEDURE listnames(INOUT name TEXT)
BEGIN
    -- 1. Variables
    DECLARE done     BOOL          DEFAULT FALSE;
    DECLARE emp_name VARCHAR(255)  DEFAULT '';

    -- 2. Cursor
    DECLARE cur CURSOR FOR
        SELECT CONCAT(first_name, ' ', last_name) AS full_name
        FROM   employees;

    -- 3. Handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    SET name = '';

    process_list: LOOP
        FETCH cur INTO emp_name;
        IF done THEN LEAVE process_list; END IF;

        SET name = CONCAT(name, emp_name, ';');
    END LOOP process_list;

    CLOSE cur;
END$$

DELIMITER ;

-- Run it
SET @result = '';
CALL listnames(@result);
SELECT @result AS concatenated_names;

Same cursor pattern, different output style — emit a separate result row per employee. Notice emp_salary must be declared just like emp_first_name (the original tutorial used it without declaring):

DROP PROCEDURE IF EXISTS DisplayEmployeeInfo;

DELIMITER $$

CREATE PROCEDURE DisplayEmployeeInfo()
BEGIN
    -- All variables must be declared
    DECLARE done            INT            DEFAULT FALSE;
    DECLARE emp_first_name  VARCHAR(50);
    DECLARE emp_salary      DECIMAL(10, 2);    -- ← was missing in original

    DECLARE emp_cursor CURSOR FOR
        SELECT first_name, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO emp_first_name, emp_salary;

        IF done THEN
            LEAVE read_loop;        -- ← matches the loop label, not "process_list"
        END IF;

        SELECT CONCAT('Employee: ', emp_first_name,
                      ', Salary: ',  emp_salary) AS info;
    END LOOP read_loop;

    CLOSE emp_cursor;
END$$

DELIMITER ;

CALL DisplayEmployeeInfo();

1. Always close the cursor. MySQL closes it when the procedure returns, but explicit CLOSE is clearer and frees resources sooner.
2. Use CONTINUE HANDLER, not EXIT HANDLER, for the NOT FOUND case.
3. Declare variables → cursors → handlers in that exact order, or MySQL will reject the procedure.
4. Don't declare the same cursor twice in one block — it's a syntax error, however tempting it might be to copy a similar declaration.
5. Use the loop label correctly — LEAVE my_loop; must match exactly the label on my_loop: LOOP ... END LOOP my_loop;.
6. Prefer set-based SQL. Almost every cursor can be replaced with a faster UPDATE ... JOIN, aggregate, or window function.

• A MySQL cursor lets you walk through a SELECT result set one row at a time, inside a stored procedure, function, or trigger.
• The four-step lifecycle is DECLARE → OPEN → FETCH → CLOSE, and you must declare a CONTINUE HANDLER FOR NOT FOUND to detect the end of the result.
• MySQL cursors are read-only, forward-only, and asensitive — no UPDATE through the cursor, no scrolling backward.
• The strict declaration order is variables → cursors → handlers; MySQL rejects any other order.
• Cursors are slow compared to set-based SQL. Use them only when procedural per-row logic is genuinely required.