How to Loop Through Rows in MySQL

Looping through rows in MySQL involves iterating over a result set obtained from a query. This is often used to process or manipulate each row individually.

Understanding cursors for row-by-row processing

Cursors in MySQL provide a way to iterate over a result set from a SELECT query. They are essential for row-by-row operations in stored procedures.

DECLARE cursor_name CURSOR FOR select_statement;

Opening and closing a cursor

Before using a cursor, you need to open it. Once processing is done, always ensure to close it to release resources.

OPEN cursor_name;
-- Perform operations
CLOSE cursor_name;

Fetching rows with a cursor

The FETCH statement retrieves the next row from the cursor into variables.

FETCH cursor_name INTO var1, var2;

Looping through rows using cursors

Combine LOOP, FETCH, and EXIT WHEN NOT FOUND to iterate through each row.

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_name;

loop_name: LOOP
    FETCH cursor_name INTO var1, var2;
    IF done THEN
        LEAVE loop_name;
    END IF;

    -- Process each row here
END LOOP loop_name;

CLOSE cursor_name;

An example of row-by-row processing

Here’s a practical example demonstrating the whole process.

CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE myOrder INT;
    DECLARE cursorOrders CURSOR FOR SELECT order_id FROM orders;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursorOrders;

    read_loop: LOOP
        FETCH cursorOrders INTO myOrder;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Add custom logic for each order here
    END LOOP read_loop;

    CLOSE cursorOrders;
END;

This guide presents the essentials of looping through rows in MySQL using cursors. Cursors provide a controlled way to handle data row by row, especially within stored procedures. Remember to always open and close cursors and handle the end of the data set gracefully.