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

Need a 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;

Need a 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;

Need a 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;

Need a hint?

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