? MySQL Cursor

? Quick Overview

Cursors in MySQL allow you to iterate through a result set row by row. They are mainly used in stored procedures for processing query results sequentially when set-based operations are not suitable.

? Key Concepts

Cursor — a database object to iterate rows returned by a query.
OPEN — initializes the cursor and makes it ready for FETCH.
FETCH — retrieves the next row into variables.
CLOSE — releases the cursor and its resources.
Handlers — use CONTINUE HANDLER FOR NOT FOUND to detect the end of the result set.

⚙️ Syntax / Theory

? View Code Example

// Cursor declaration and usage outline
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO variable_list;
CLOSE cursor_name;

? Code Example

? View Code Example

-- Example stored procedure to process customers using a cursor
DELIMITER $$

CREATE PROCEDURE process_customers()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE c_name VARCHAR(100);
DECLARE c_email VARCHAR(100);

DECLARE customer_cursor CURSOR FOR
SELECT name, email FROM customers;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN customer_cursor;

read_loop: LOOP
FETCH customer_cursor INTO c_name, c_email;
IF done THEN
LEAVE read_loop;
END IF;

-- process each row: here we select a formatted string
SELECT CONCAT('Customer: ', c_name, ', Email: ', c_email);
END LOOP;

CLOSE customer_cursor;
END$$

DELIMITER ;

? Live Output / Explanation

What happens when this runs

When process_customers() is executed, MySQL opens the cursor for the SELECT name, email FROM customers query and iterates row-by-row. For each row it fetches the values into c_name and c_email, then runs the SELECT that outputs a formatted string. The loop exits when the NOT FOUND handler sets done = TRUE.

? Interactive Cursor Simulator

Click buttons to control the cursor manually.

Dataset (Table)

name	email
Alice Doe	alice@test.com
Bob Smith	bob@test.com
Charlie	charlie@test.com

Variables (Memory)

CURSOR_STATE: CLOSED

c_name = NULL
c_email = NULL

done = FALSE

System: Waiting to OPEN cursor...

✅ Tips & Best Practices

Always CLOSE your cursors after use to free resources.
Prefer set-based operations; use cursors only when you need row-by-row processing.
Declare CONTINUE HANDLER FOR NOT FOUND to safely detect the end of the result set.
Keep cursor loops simple and avoid heavy operations inside the loop for performance reasons.

? Try It Yourself / Practice Tasks

Create a stored procedure using a cursor to iterate through an employees table and print each employee's details.
Add logic to skip rows where a column meets a condition (e.g., salary < 30000) inside the loop.
Experiment with closing the cursor early and observe behavior when attempting further FETCH operations.