Cursors in MySQL

What Is a Cursor in MySQL?

A cursor is a stored-program mechanism that reads a SELECT result one row at a time. Normal SQL is set-oriented: one statement describes the result for many rows. A cursor is useful only when each row requires procedural work that cannot be expressed safely and clearly as one set-based statement.

DECLARE cur_overdue CURSOR FOR
  SELECT order_id, customer_id, total_amount
  FROM orders_cursor_lab
  WHERE status = 'PENDING'
    AND order_date < p_cutoff
  ORDER BY order_id;

Cursor Properties and Required Declaration Order

MySQL cursors inside stored programs are:

• Asensitive: the server may or may not copy the result table, so do not design around seeing concurrent changes.
• Read-only: the cursor does not update its current result row.
• Nonscrollable: FETCH moves forward; it cannot skip backward or jump to an arbitrary row.

Declarations must appear at the start of the compound block in this order:

1. local variables;
2. named conditions;
3. cursors;
4. handlers.

DECLARE v_done BOOLEAN DEFAULT FALSE;     -- variable
DECLARE no_more_rows CONDITION FOR SQLSTATE '02000';
DECLARE cur_orders CURSOR FOR SELECT order_id FROM orders_cursor_lab;
DECLARE CONTINUE HANDLER FOR no_more_rows SET v_done = TRUE;

Putting the handler before the cursor, or any declaration after an executable statement, produces a syntax error.

Build a Reproducible Reminder Queue Lab

DROP TABLE IF EXISTS reminder_queue_lab;
DROP TABLE IF EXISTS orders_cursor_lab;

CREATE TABLE orders_cursor_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE reminder_queue_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  reminder_text VARCHAR(200) NOT NULL,
  queued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

INSERT INTO orders_cursor_lab VALUES
(1,101,'PAID','2026-08-01',1200.00),
(2,101,'PENDING','2026-08-05',500.00),
(3,102,'PAID','2026-08-03',750.00),
(4,103,'CANCELLED','2026-08-04',300.00),
(5,101,'PAID','2026-08-10',1500.00),
(6,102,'PENDING','2026-08-11',900.00),
(7,104,'PENDING','2026-08-14',2200.00);

With cutoff 2026-08-12, orders 2 and 6 qualify. Order 7 does not. The primary key on the queue makes the procedure safely repeatable for the same order.

Complete Cursor Procedure: DECLARE, OPEN, FETCH, CLOSE

DELIMITER //

CREATE PROCEDURE queue_overdue_orders(IN p_cutoff DATE)
MODIFIES SQL DATA
BEGIN
  DECLARE v_done BOOLEAN DEFAULT FALSE;
  DECLARE v_order_id INT;
  DECLARE v_customer_id INT;
  DECLARE v_total DECIMAL(10,2);

  DECLARE cur_overdue CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders_cursor_lab
    WHERE status = 'PENDING'
      AND order_date < p_cutoff
    ORDER BY order_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

  IF p_cutoff IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'cutoff date is required';
  END IF;

  OPEN cur_overdue;

  read_loop: LOOP
    FETCH cur_overdue
      INTO v_order_id, v_customer_id, v_total;

    IF v_done THEN
      LEAVE read_loop;
    END IF;

    INSERT IGNORE INTO reminder_queue_lab
      (order_id, customer_id, reminder_text)
    VALUES
      (v_order_id, v_customer_id,
       CONCAT('Pending order ', v_order_id,
              ' amount ', FORMAT(v_total, 2)));
  END LOOP;

  CLOSE cur_overdue;
END //

DELIMITER ;

FETCH targets match the SELECT column count and compatible types. The NOT FOUND handler sets the flag after the last attempted FETCH; checking the flag immediately prevents processing stale variable values.

Call the Procedure and Trace the Result

CALL queue_overdue_orders('2026-08-12');

SELECT order_id, customer_id, reminder_text
FROM reminder_queue_lab
ORDER BY order_id;

The lifecycle is:

1. OPEN prepares the cursor result.
2. First FETCH loads order 2.
3. The loop inserts reminder 2.
4. Next FETCH loads order 6 and inserts reminder 6.
5. The next FETCH finds no row, activates NOT FOUND and sets v_done.
6. The loop leaves without using stale variables, then CLOSE releases the cursor.

Calling the procedure again inserts no duplicate because order_id is the queue primary key and the example uses INSERT IGNORE. In production, decide whether ignoring an existing row is truly the required policy.

Handlers, Nested Blocks and Operational Safety

NOT FOUND is SQLSTATE class '02'. A handler declared for it can also react to a SELECT ... INTO that returns no row in the same scope. Avoid unrelated SELECT INTO statements inside the cursor loop, or isolate them in a nested block with their own handler.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

-- For a separate optional lookup, use a nested block
BEGIN
  DECLARE v_lookup_missing BOOLEAN DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_lookup_missing = TRUE;
  SELECT value_col INTO v_value
  FROM lookup_table WHERE id = v_order_id;
END;

• Validate parameters before OPEN.
• Use an ORDER BY when processing order matters.
• Do not COMMIT each row unless a deliberately designed batch process requires it.
• Keep transactions short and make writes idempotent for retry safety.
• Capture SQLEXCEPTION with a deliberate EXIT handler only when cleanup, rollback or RESIGNAL policy is defined.
• Log progress outside the cursor only when it does not distort the workload.

Prefer the Set-Based Alternative When Possible

The example does not truly require per-row procedural branching. One INSERT SELECT expresses the same work:

INSERT IGNORE INTO reminder_queue_lab
  (order_id, customer_id, reminder_text)
SELECT order_id,
       customer_id,
       CONCAT('Pending order ', order_id,
              ' amount ', FORMAT(total_amount, 2))
FROM orders_cursor_lab
WHERE status = 'PENDING'
  AND order_date < '2026-08-12';

The set-based form is shorter and lets the optimizer plan the work as a whole. Add an index for the filter when the workload justifies it:

CREATE INDEX ix_orders_status_date
ON orders_cursor_lab(status, order_date);

Cursor Use, Test and Review Checklist

1. Prove why set-based SQL is insufficient.
2. Bound the row count and index the cursor SELECT.
3. Follow variables, conditions, cursors, handlers declaration order.
4. Match FETCH columns, target variables and types.
5. Check the done flag immediately after FETCH.
6. Use ORDER BY if sequence affects results.
7. Test zero, one and many-row result sets.
8. Test duplicate/retry behavior and transaction rollback.
9. Avoid handler collisions with SELECT INTO.
10. Measure duration, locks, temporary space and queue growth.

Review stored procedures and handlers, query optimization and the next topic, MySQL Event Scheduler.

Official References

• MySQL 8.4: Cursors
• MySQL 8.4: Cursor DECLARE Statement
• MySQL 8.4: DECLARE HANDLER Statement

Cursor properties, declaration order, lifecycle and NOT FOUND handling were checked against the official MySQL 8.4 manual.