Cursors - Processing Rows One at a Time

Introduction

Sometimes in SQL, we need to process each row one by one — not all at once like regular queries. That's where cursors come into play. Think of a cursor like a for-loop that allows you to work with one row at a time. In school systems, this could be useful for assigning ranks, sending notifications, or logging updates for each student individually.

What is a Cursor?

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.

Cursor Components

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

Sample Table – students

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);

1. Basic Cursor Example – Print Student Names

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

2. Cursor with Computation – Update Grades

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 to Use Cursors

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

Best Practices

• 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

Real-World School Use Cases

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

Summary

Cursors give SQL the ability to step through each row of a result set and perform actions one at a time. While powerful, they should be used wisely. When regular queries fall short, cursors let you take control — row by row, decision by decision.

What’s Next?

Next, we’ll look into SQL Optimization Techniques to make your queries more efficient and your database faster.