Cursors for row iteration in MySQL

Introduction

Cursors let you look at each row in a table one by one. This helps when you want to do something with each row separately.

You want to send an email to each user in a list.

You need to update rows one at a time based on some complex logic.

You want to process orders one by one to check stock.

You want to generate a report by reading rows step-by-step.

Syntax

MySQL

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO variable_list;
-- process data
CLOSE cursor_name;

You must DECLARE the cursor before using it.

Always OPEN the cursor before FETCHing rows and CLOSE it when done.

Examples

Declare a cursor named user_cursor to select id and name from the users table.

MySQL

DECLARE user_cursor CURSOR FOR SELECT id, name FROM users;

Open the cursor and fetch the first row into variables user_id and user_name.

MySQL

OPEN user_cursor;
FETCH user_cursor INTO user_id, user_name;

Close the cursor when finished to free resources.

MySQL

CLOSE user_cursor;

Sample Program

This procedure uses a cursor to go through each user in the users table. For each user, it shows their ID and name.

MySQL

DELIMITER $$
CREATE PROCEDURE process_users()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE u_id INT;
  DECLARE u_name VARCHAR(100);
  DECLARE user_cursor CURSOR FOR SELECT id, name FROM users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN user_cursor;

  read_loop: LOOP
    FETCH user_cursor INTO u_id, u_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- For example, print user info (simulate with SELECT)
    SELECT CONCAT('User ID: ', u_id, ', Name: ', u_name) AS user_info;
  END LOOP;

  CLOSE user_cursor;
END$$
DELIMITER ;

CALL process_users();

Important Notes

Always declare a handler for NOT FOUND to know when the cursor has no more rows.

Cursors are slower than set-based queries, so use them only when needed.

Summary

Cursors let you handle rows one by one.

Declare, open, fetch, and close a cursor in that order.

Use a handler to detect when no more rows are left.