Using Cursors and Loops in MySQL

Consider loops in general programming. They help you execute a specific sequence of instructions repeatedly until a particular condition breaks the loop. MySQL also provides a way to execute instructions on individual rows using cursors. Cursors in MySQL will execute a set of instructions on rows returned from SQL queries.

Properties of MySQL Cursors

• Non-Scrollable: You can only iterate through rows in one direction. You can't skip a row; you can't jump to a row; you can't go back to a row.
• Read-only: You can't update or delete rows using cursors.
• Asensitive: MySQL cursors point to the underlying data. It runs faster than an insensitive cursor. Insensitive cursors point to a snapshot of the underlying data, making it slower than the asenstive cursors.

Creating a MySQL Cursor

To create a MySQL cursor, you'll need to work with the DECLARE, OPEN, FETCH, and CLOSE statements.

The Declare Statement

The DECLARE statement can declare variables, cursors, and handlers. There is a sequence of declarations that needs to be adhered to:

• Variables
• Cursors
• Handlers

You must first declare at least one variable to use later with the FETCH statement later on.

DECLARE <variable_name>  <variable_type>

When declaring the cursor(s), you must attach a SELECT statement. Any valid SELECT statement will work. You also must declare at least one cursor.

DECLARE <cursor_name> CURSOR FOR <select_statement>

You also have to declare a NOT FOUND handler. When the cursor iterates and reaches the last row, it raises a condition that will be handled with the NOT FOUND handler. You can also declare other handlers depending on your needs. For example:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

The Open Statement

The OPEN statement initializes the result from the DECLARE cursor statement.

OPEN <cursor_name>

The Fetch Statement

The FETCH statement works as an iterator. It fetches the next row from the rows associated with the SELECT statement in the cursor declaration.

FETCH <cursor_name> INTO <variable_list>

The <variable_list> is one or more variables from the variables declared earlier.

FETCH <cursor_name> INTO a, b, c

If the next row exists, the variables store it; otherwise, a No Data condition with SQLSTATE of '02000' occurs. You can use the NOT FOUND handler to deal with this SQLSTATE.

The Close Statement

This statement closes the cursor opened in the `OPEN` statement.

CLOSE <cursor_name>

Using MySQL Cursors

Ready to start using MySQL cursors? First, you need to create a database and a table. In this demo, we will populate a table with data from this CSV file.

We will create a cursor that does the following:

• Loops through the football table.
• Calculates the average goals a home team that won a match scored at halftime.

Here's what the MySQL procedure should look like to accomplish this.

MySQL Procedure

DELIMITER $$ CREATE PROCEDURE cursordemo(INOUT average_goals FLOAT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE matches int DEFAULT(0);
DECLARE goals int DEFAULT(0);
DECLARE half_time_goals INT;
DECLARE team_cursor CURSOR FOR
SELECT
    HTHG
FROM
    epl.football
WHERE
    (FTR = "H");
    
DECLARE
    CONTINUE HANDLER FOR NOT FOUND
SET
    done = TRUE;

OPEN team_cursor;
teams_loop:
LOOP
    FETCH team_cursor INTO half_time_goals;
IF done THEN LEAVE teams_loop;
END IF;
SET
    goals = goals + half_time_goals;
SET
    matches = matches + 1;
END
LOOP
    teams_loop;
SET
    average_goals = goals / matches;
CLOSE team_cursor;

END $$ DELIMITER;

A procedure in MySQL is like a container that holds a sequence of instructions. Procedures are written in MySQL and stored in the database. We are defining the cursor inside a procedure because procedures are reusable. Executing the SQL procedure above will store the procedure in the database. We can call the procedure using its name, as shown below:

SET @average_goals = 0.0; 
CALL cursordemo(@average_goals); 
SELECT @average_goals;

The output of this operation is:

1.080954670906067

Caveats of MySQL Cursors

A good look at the example shows that a SQL query like SELECT AVG(HTHG) FROM epl.football WHERE (FTR="H"); will achieve the same result. It would be best if you only used cursors when dealing with one row at a time. Examples are integrity checks, index rebuilds. Note that each time a cursor fetches a row, it results in round network trips. So, it may end up slowing down your MySQL server depending on how large the operation is.

Conclusion

In this article, we have seen how to use MySQL cursors and for loops, which function like iterative loops in general programming. We also covered variables, cursor, and handler declaration.

Using cursors can be computationally expensive. It would be best to use only them when MySQL does not provide any other way to achieve the same result using standard queries or user-defined functions. But, if that's the case, they are very powerful tools.