Cursors for row iteration in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a cursor in MySQL?

A cursor is a database object used to retrieve and process rows one at a time from a result set, allowing row-by-row operations.

beginner

List the main steps to use a cursor in MySQL.

1. Declare the cursor with a SELECT statement.<br>2. Open the cursor.<br>3. Fetch rows one by one.<br>4. Close the cursor when done.

beginner

Why do we need to close a cursor in MySQL?

Closing a cursor releases the memory and resources it uses. Not closing it can cause resource leaks and slow down the database.

intermediate

What happens if you fetch from a cursor but no more rows are available?

MySQL raises the NOT FOUND condition, indicating there are no more rows to fetch.

intermediate

Can you use a cursor to update rows while iterating in MySQL?

Yes, you can fetch rows with a cursor and then use UPDATE statements to modify those rows inside the loop.

What is the first step when using a cursor in MySQL?

A Open the cursor

B Declare the cursor

C Fetch a row

D Close the cursor

You must declare the cursor first with a SELECT statement before opening or fetching.

Which statement correctly closes a cursor named 'cur'?

A CLOSE cur;

B END cur;

C STOP cur;

D FINISH cur;

The correct syntax to close a cursor is CLOSE followed by the cursor name.

What condition indicates no more rows are available when fetching from a cursor?

A EMPTY

B NO ROWS

C END OF DATA

D NOT FOUND

MySQL uses the NOT FOUND condition to signal no more rows to fetch.

Can you fetch multiple rows at once using a cursor in MySQL?

A No, cursors fetch one row at a time

B Yes, cursors fetch all rows at once

C Yes, but only two rows at a time

D Only if you use a special option

Cursors are designed to fetch rows one by one for row-by-row processing.

What happens if you forget to close a cursor?

A Nothing, it is safe to leave open

B The cursor automatically closes after fetching

C Resources remain allocated, causing potential issues

D The database crashes

Not closing a cursor keeps resources allocated, which can slow down the database.

Explain how to use a cursor in MySQL to process rows one by one.

Think about the sequence of steps to handle each row individually.

Describe why cursors are useful and when you might use them.

Consider situations where you need to do something special with each row.