Cursors for row iteration in MySQL - Step-by-Step Execution

Concept Flow - Cursors for row iteration

Declare Cursor

↓

Open Cursor

↓

Fetch Row

↓

Check if Row Exists?

No→Close Cursor

Yes↓

Process Row

↓

Fetch Next Row

↩Back to Check if Row Exists?

This flow shows how a cursor is declared, opened, and used to fetch and process rows one by one until no more rows remain, then it closes.

Execution Sample

MySQL

DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE cur CURSOR FOR SELECT id, name FROM users;
OPEN cur;
FETCH cur INTO @id, @name;
WHILE NOT done DO
  -- process row
  FETCH cur INTO @id, @name;
END WHILE;
CLOSE cur;

This code declares a done flag and handler, declares a cursor to select id and name from users, opens it, fetches each row into variables, processes them, and closes the cursor.

Execution Table

Step	Action	Cursor State	Fetched Values	Condition done	Next Step
1	DECLARE done, handler, cursor for SELECT id, name FROM users	Not opened	None	N/A	Open cursor
2	OPEN cursor	Opened, ready to fetch	None	N/A	Fetch first row
3	FETCH first row	Opened	id=1, name='Alice'	0 (row fetched)	Process row
4	Process row (id=1, name='Alice')	Opened	id=1, name='Alice'	0	Fetch next row
5	FETCH next row	Opened	id=2, name='Bob'	0	Process row
6	Process row (id=2, name='Bob')	Opened	id=2, name='Bob'	0	Fetch next row
7	FETCH next row	Opened	id=3, name='Carol'	0	Process row
8	Process row (id=3, name='Carol')	Opened	id=3, name='Carol'	0	Fetch next row
9	FETCH next row	Opened	None	1 (no row)	Close cursor
10	CLOSE cursor	Closed	None	N/A	End

💡 No more rows to fetch, done = 1, cursor closed.

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 7	After Step 9
@id	NULL	1	2	3	NULL
@name	NULL	Alice	Bob	Carol	NULL
done	0	0	0	0	1

Key Moments - 3 Insights

Why do we check done after each FETCH?

Because done tells us if the FETCH got a row (0) or not (1). We stop fetching when no rows remain, as shown in steps 3, 5, 7 (0) and step 9 (1).

What happens if we forget to CLOSE the cursor?

The cursor stays open, which can cause resource leaks. Step 10 shows the proper closing after fetching all rows.

Can we process rows before fetching the first one?

No, we must FETCH first to get data. Step 3 fetches the first row before processing in step 4.

Visual Quiz - 3 Questions

Look at the execution table, what are the values of @id and @name after step 5?

A @id=3, @name='Carol'

B @id=1, @name='Alice'

C @id=2, @name='Bob'

D NULL, NULL

💡 Hint

Check the 'Fetched Values' column at step 5 in the execution table.

At which step does the cursor detect no more rows to fetch?

A Step 9

B Step 7

C Step 3

D Step 10

💡 Hint

Look for done = 1 in the execution table.

If we skip the CLOSE cursor step, what is the likely effect?

A Cursor automatically closes after last fetch

B Cursor remains open, wasting resources

C Fetch will fail immediately

D No effect, code runs normally

💡 Hint

Refer to key moment about closing cursor and step 10 in the execution table.

Concept Snapshot

DECLARE done INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE cursor FOR SELECT ...;  -- Define cursor
OPEN cursor;  -- Start cursor
FETCH cursor INTO variables;  -- Get one row
WHILE NOT done DO  -- Loop while rows exist
  -- Process row
  FETCH cursor INTO variables;  -- Next row
END WHILE;
CLOSE cursor;  -- Release cursor

Full Transcript

This visual execution shows how a MySQL cursor works step-by-step. First, the done flag and CONTINUE HANDLER are declared, then the cursor is declared for a SELECT query. Then it is opened to start fetching rows. Each FETCH gets one row into variables, and done tells if a row was fetched (0) or not (1). We process each row while NOT done. When no rows remain, done becomes 1, and we close the cursor to free resources. Variables @id and @name hold the current row's data. This process lets us handle rows one by one in SQL procedures.