Cursors — Processing Rows One at a Time

A procedure needs to inspect each student record and perform an operation that cannot be expressed clearly as one set-based statement.

A cursor provides a way to fetch a query’s rows one at a time in a stored program. It is useful when the task needs procedural row-by-row handling.

Declare the cursor, open it, fetch into variables until no more rows are available, and close it. MySQL uses a NOT FOUND handler to signal exhaustion. Check that signal immediately after FETCH so the final iteration does not process stale variables from the previous row.

Related concepts: Group and Summarize and Stored Procedures and Handle SQL Errors.

A cursor is a database object used to retrieve and process one row at a time from a query result set. It’s most useful when row-wise operations are needed — operations that aren’t possible with pure SQL set logic.

• DECLARE – Define the cursor with a SELECT query
• OPEN – Initialize the cursor
• FETCH – Retrieve the next row
• CLOSE – Release the cursor

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', 85),
(2, 'Diya Iyer', 92),
(3, 'Sneha Patil', 78),
(4, 'Mehul Agarwal', 90);

SQL · portable concepts; preserved examples identify their engine

DELIMITER $$

CREATE PROCEDURE print_students()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE s_name VARCHAR(50);

  DECLARE student_cursor CURSOR FOR
    SELECT name FROM students;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN student_cursor;

  read_loop: LOOP
    FETCH student_cursor INTO s_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SELECT CONCAT('Student: ', s_name);

  END LOOP;

  CLOSE student_cursor;
END$$

DELIMITER ;

Call it:

CALL print_students();

Student: Aarav Sharma  
Student: Diya Iyer  
Student: Sneha Patil  
Student: Mehul Agarwal

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE student_grades (
  roll_no INT,
  grade VARCHAR(2)
);

DELIMITER $$

CREATE PROCEDURE assign_grades()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE s_roll INT;
  DECLARE s_marks INT;
  DECLARE grade VARCHAR(2);

  DECLARE cur CURSOR FOR
    SELECT roll_no, marks FROM students;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  loop_through: LOOP
    FETCH cur INTO s_roll, s_marks;
    IF done THEN
      LEAVE loop_through;
    END IF;

    -- Simple grading logic
    IF s_marks >= 90 THEN
      SET grade = 'A+';
    ELSEIF s_marks >= 75 THEN
      SET grade = 'A';
    ELSE
      SET grade = 'B';
    END IF;

    INSERT INTO student_grades VALUES (s_roll, grade);

  END LOOP;

  CLOSE cur;
END$$

DELIMITER ;

Call the procedure:

CALL assign_grades();

Check the output:

SELECT * FROM student_grades;

roll_no | grade
--------+-------
1       | A
2       | A+
3       | B
4       | A+

• When operations need to be performed row-by-row
• For logging, auditing, or conditional actions
• For procedural logic that involves loops or branching

• Avoid cursors when a simple query can do the job
• Always CLOSE your cursors to free memory
• Use LOOP with EXIT conditions to avoid infinite loops

• Assigning ranks or grades to each student
• Iterating through attendance logs to identify defaulters
• Logging updates or triggering custom actions row-wise

Prefer a set-based query or update when it expresses the operation naturally. MySQL stored-program cursors are read-only and non-scrollable. A cursor is not a guarantee of efficient processing for a large dataset.

Replace a row-by-row sum of student marks with one set-based aggregate and state its result.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT SUM(marks) AS total_marks FROM practice_students;

All three marks can be summed directly. A cursor would add a loop, fetch state and exhaustion handling without changing this simple required result.

A cursor provides a way to fetch a query’s rows one at a time in a stored program. It is useful when the task needs procedural row-by-row handling.

All three marks can be summed directly. A cursor would add a loop, fetch state and exhaustion handling without changing this simple required result.

Continue your understanding with Group and Summarize and Stored Procedures and Handle SQL Errors.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.