Cursors for row iteration in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of cursor iteration with FETCH

Consider the following MySQL stored procedure snippet that uses a cursor to iterate over a table employees with columns id and salary. What will be the value of total_salary after the cursor loop completes?

DECLARE done INT DEFAULT FALSE;
DECLARE emp_salary INT;
DECLARE total_salary INT DEFAULT 0;
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees WHERE id <= 3;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN emp_cursor;
read_loop: LOOP
  FETCH emp_cursor INTO emp_salary;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SET total_salary = total_salary + emp_salary;
END LOOP;
CLOSE emp_cursor;

Assuming the employees table has rows with id 1, 2, 3 and salaries 1000, 1500, 2000 respectively.

A Syntax error due to missing cursor declaration

B 4500

C 1000

D 0

💡 Hint

Think about how the cursor fetches each salary and adds it to the total.

The cursor fetches salaries 1000, 1500, and 2000 one by one and adds them to total_salary. So total_salary becomes 1000 + 1500 + 2000 = 4500.

📝 Syntax

intermediate

Identify the syntax error in cursor declaration

Which of the following cursor declarations in MySQL is syntactically correct?

A DECLARE CURSOR emp_cursor FOR SELECT name FROM employees;

B CURSOR DECLARE emp_cursor FOR SELECT name FROM employees;

C DECLARE emp_cursor FOR CURSOR SELECT name FROM employees;

D DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;

💡 Hint

Look at the order of keywords in the declaration.

The correct syntax is 'DECLARE cursor_name CURSOR FOR select_statement;'. Option D follows this syntax exactly.

🔧 Debug

advanced

Why does this cursor loop never end?

Given the following MySQL cursor loop, why does it never exit?

DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
-- Missing CONTINUE HANDLER

OPEN emp_cursor;
read_loop: LOOP
  FETCH emp_cursor INTO emp_name;
  IF done THEN
    LEAVE read_loop;
  END IF;
  -- process emp_name
END LOOP;
CLOSE emp_cursor;

A Because the FETCH statement syntax is incorrect.

B Because the cursor is not opened before the loop.

C Because the CONTINUE HANDLER for NOT FOUND is missing, so 'done' is never set to TRUE.

D Because the loop label is missing.

💡 Hint

Think about how the loop knows when to stop fetching rows.

Without the CONTINUE HANDLER for NOT FOUND, the 'done' variable never becomes TRUE, so the loop never leaves.

❓ optimization

advanced

Optimizing cursor usage for large datasets

You have a cursor iterating over a large table to calculate the sum of a numeric column. Which approach is more efficient in MySQL?

A Use a single SQL query with SUM() aggregate function instead of a cursor loop.

B Use a cursor with a small FETCH size and commit after each fetch.

C Use a cursor and store each row in a temporary table before summing.

D Use nested cursors to process rows in batches.

💡 Hint

Think about what SQL is best at doing.

SQL aggregate functions like SUM() are optimized and faster than row-by-row cursor processing.

🧠 Conceptual

expert

Understanding cursor scope and variable lifetime

In MySQL stored procedures, what happens to a cursor and its associated variables after the procedure ends?

A The cursor and variables are automatically closed and deallocated when the procedure ends.

B The cursor remains open and variables retain their values until the session ends.

C The cursor is closed but variables keep their values after procedure ends.

D The cursor and variables persist globally and can be accessed by other procedures.

💡 Hint

Consider the scope of variables and cursors inside stored procedures.

Cursors and variables declared inside a stored procedure exist only during its execution and are cleaned up afterward.

Practice

1. What is the main purpose of using a cursor in MySQL?

easy

A. To create new tables dynamically

B. To speed up bulk inserts

C. To process query results row by row

D. To backup the database automatically

Solution

1. Step 1: Understand cursor functionality

   Cursors allow you to handle each row of a query result individually instead of all at once.

2. Step 2: Identify the correct purpose

   Processing rows one by one matches the cursor's main use, unlike speeding inserts or creating tables.

3. Final Answer:

   To process query results row by row -> Option C

4. Quick Check:

   Cursor = row-by-row processing [OK]

Hint: Cursors = row-by-row handling, not bulk operations [OK]

Common Mistakes:

• Thinking cursors speed up inserts
• Confusing cursors with backup tools
• Assuming cursors create tables

2. Which of the following is the correct order of cursor operations in MySQL?

easy

A. DECLARE, FETCH, OPEN, CLOSE

B. DECLARE, OPEN, FETCH, CLOSE

C. FETCH, DECLARE, OPEN, CLOSE

D. OPEN, DECLARE, FETCH, CLOSE

Solution

1. Step 1: Recall cursor lifecycle

   First, you declare the cursor, then open it to start fetching rows, and finally close it when done.

2. Step 2: Match the correct sequence

   Only DECLARE, OPEN, FETCH, CLOSE follows the correct order of cursor operations.

3. Final Answer:

   DECLARE, OPEN, FETCH, CLOSE -> Option B

4. Quick Check:

   Cursor order = DECLARE -> OPEN -> FETCH -> CLOSE [OK]

Hint: Remember: Declare first, then open, fetch, and close last [OK]

Common Mistakes:

• Opening cursor before declaring
• Fetching before opening
• Closing before fetching all rows

3. Consider this MySQL cursor code snippet inside a stored procedure:

DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
  FETCH cur INTO @user_id;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SELECT @user_id;
END LOOP;
CLOSE cur;

What will this code output?

medium

A. It prints each user id from the users table one by one

B. It prints only the first user id

C. It causes a syntax error due to missing variable declaration

D. It prints nothing because the loop never runs

Solution

1. Step 1: Analyze cursor fetching loop

   The cursor fetches each id from users table into @user_id, then prints it until no rows remain.

2. Step 2: Understand handler and loop exit

   The handler sets done = TRUE when no more rows, causing loop to exit properly after printing all ids.

3. Final Answer:

   It prints each user id from the users table one by one -> Option A

4. Quick Check:

   Cursor loop prints all rows one by one [OK]

Hint: Handler sets done; loop prints all fetched rows [OK]

Common Mistakes:

• Assuming only first row prints
• Missing handler causes infinite loop
• Confusing session variables with local variables

4. You wrote this MySQL cursor code but it causes an infinite loop:

DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT name FROM employees;
OPEN cur;
read_loop: LOOP
  FETCH cur INTO @emp_name;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SELECT @emp_name;
END LOOP;
CLOSE cur;

What is the likely cause of the infinite loop?

medium

A. The SELECT statement inside loop causes the loop to restart

B. Cursor was not declared before opening

C. Variable @emp_name is not declared

D. Missing CONTINUE HANDLER to set done = TRUE when no rows left

Solution

1. Step 1: Check for handler presence

   The code lacks a CONTINUE HANDLER to set done = TRUE when no more rows are fetched.

2. Step 2: Understand loop exit condition

   Without setting done = TRUE, the IF condition never triggers LEAVE, causing infinite loop.

3. Final Answer:

   Missing CONTINUE HANDLER to set done = TRUE when no rows left -> Option D

4. Quick Check:

   No handler = infinite loop [OK]

Hint: Always add handler to set done flag for loop exit [OK]

Common Mistakes:

• Forgetting CONTINUE HANDLER
• Assuming loop exits automatically
• Misplacing variable declarations

5. You want to write a stored procedure that sums the salaries of all employees using a cursor. Which approach correctly uses a cursor to calculate the total salary?

DECLARE done INT DEFAULT FALSE;
DECLARE total_salary DECIMAL(10,2) DEFAULT 0;
DECLARE cur CURSOR FOR SELECT salary FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
  FETCH cur INTO @sal;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SET total_salary = total_salary + @sal;
END LOOP;
CLOSE cur;
SELECT total_salary;

What is the expected output of this procedure?

hard

A. The sum of all employee salaries

B. Syntax error due to missing variable declaration for @sal

C. Zero, because total_salary is not updated correctly

D. The procedure runs but returns NULL

Solution

1. Step 1: Verify declaration order and variables

   All local variables (done, total_salary) are declared before the cursor and handler. FETCH INTO @sal uses a session variable, which is valid.

2. Step 2: Trace execution

   The loop fetches each salary, adds it to total_salary until the handler sets done=TRUE, then SELECT outputs the sum.

3. Final Answer:

   The sum of all employee salaries -> Option A

4. Quick Check:

   Cursor loop + handler + accumulation = total sum [OK]

Hint: DECLARE order: locals first, then cursors, handlers. FETCH INTO @var OK [OK]

Common Mistakes:

• Declaring variables after cursors/handlers
• Thinking @vars require DECLARE
• Missing handler or wrong loop condition