Cursors for row iteration in MySQL - Mini Project: Build & Apply

Using Cursors for Row Iteration in MySQL

📖 Scenario: You are managing a small bookstore database. You want to process each book's sales data one by one to calculate some custom logic later.

🎯 Goal: Build a MySQL stored procedure that uses a cursor to iterate over all books in the books table and fetch their book_id and sales values.

📋 What You'll Learn

Create a books table with book_id and sales columns

Insert three specific rows into the books table

Declare a cursor to select book_id and sales from books

Open the cursor and fetch rows one by one inside a loop

Close the cursor after iteration

💡 Why This Matters

🌍 Real World

Cursors are useful when you need to process rows one at a time in databases, such as generating reports or performing row-wise calculations.

💼 Career

Database developers and administrators often use cursors in stored procedures to handle complex row-by-row operations that cannot be done with simple SQL queries.

1

Create the books table and insert data

Create a table called books with columns book_id (INT) and sales (INT). Then insert these exact rows: (1, 150), (2, 200), and (3, 120).

MySQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Declare variables and the cursor

Inside a stored procedure, declare two variables v_book_id and v_sales as INT. Then declare a cursor named book_cursor to select book_id and sales from the books table.

MySQL

CREATE TABLE books (book_id INT, sales INT);
INSERT INTO books (book_id, sales) VALUES (1, 150), (2, 200), (3, 120);

CREATE PROCEDURE process_books()
BEGIN
    -- Declare variables and cursor here
    -- Your code here
END;

Hint

Use DECLARE inside the procedure to create variables and the cursor.

3

Open the cursor and fetch rows in a loop

Inside the process_books procedure, open the book_cursor. Then create a loop that fetches v_book_id and v_sales from the cursor. Use a NOT FOUND handler to exit the loop when no more rows are available.

MySQL

CREATE TABLE books (book_id INT, sales INT);
INSERT INTO books (book_id, sales) VALUES (1, 150), (2, 200), (3, 120);

CREATE PROCEDURE process_books()
BEGIN
    DECLARE v_book_id INT;
    DECLARE v_sales INT;
    DECLARE book_cursor CURSOR FOR SELECT book_id, sales FROM books;
    DECLARE done INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open cursor and loop to fetch rows
    -- Your code here
END;

Hint

Use a CONTINUE HANDLER to detect when the cursor has no more rows. Use a loop with FETCH and check the done flag to exit.

4

Complete the procedure with cursor close

Ensure the process_books procedure closes the book_cursor after the loop ends to release resources.

MySQL

CREATE TABLE books (book_id INT, sales INT);
INSERT INTO books (book_id, sales) VALUES (1, 150), (2, 200), (3, 120);

CREATE PROCEDURE process_books()
BEGIN
    DECLARE v_book_id INT;
    DECLARE v_sales INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE book_cursor CURSOR FOR SELECT book_id, sales FROM books;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN book_cursor;
    read_loop: LOOP
        FETCH book_cursor INTO v_book_id, v_sales;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Here you could process each row
    END LOOP;
    -- Close the cursor here
    -- Your code here
END;

Hint

Always close the cursor after finishing the loop to free resources.

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