What is a cursor, and how do you use one in MySQL?

Question

Accepted Answer

A cursor is a database object that lets you retrieve and process the rows of a result set one at a time instead of operating on the whole set at once. It is declared inside a stored procedure or function and is typically used when row-by-row logic cannot be expressed as a single set-based SQL statement.

Key Points:
• Cursors are opened, fetched from in a loop, and then closed to release resources.
• A CONTINUE HANDLER FOR NOT FOUND is commonly used to detect when the cursor has no more rows.
• They are useful for row-dependent calculations, such as running totals or per-row business rules.
• Cursors are slower than set-based operations because they process one row per iteration, so they should be a last resort.
• Most reporting or bulk-update problems can be solved faster with a single UPDATE/SELECT statement than with a cursor.

Example:
A bank needs to calculate monthly interest for every account individually rather than in one bulk update, so it opens a cursor over the accounts table, fetches one account at a time, computes the interest, and moves to the next row.

Code Example:
```
DELIMITER //
CREATE PROCEDURE CalculateInterest()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE account_id INT;
    DECLARE balance DECIMAL(10, 2);
    DECLARE interest DECIMAL(10, 2);
    DECLARE account_cursor CURSOR FOR
        SELECT Account_ID, Balance FROM accounts;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN account_cursor;
    read_loop: LOOP
        FETCH account_cursor INTO account_id, balance;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET interest = balance * 0.05;
        SELECT account_id, interest AS Calculated_Interest;
    END LOOP;
    CLOSE account_cursor;
END //
DELIMITER ;
```

Interview Tip:
A concise interview answer is:

"A cursor lets me iterate over a result set row by row inside a stored procedure, which is useful when logic can't be expressed as a single set-based query, like calculating interest per account. I declare it, open it, fetch in a loop until a NOT FOUND handler flips a done flag, then close it -- but I only reach for a cursor when a plain UPDATE or aggregate query genuinely can't do the job, since cursors are much slower than set operations."