Click here to Skip to main content
15,868,016 members
Articles / Database Development / MySQL

Using Cursors and for Loops in MySQL

Rate me:
Please Sign up or sign in to vote.
1.00/5 (1 vote)
19 Nov 2021CPOL4 min read 19.3K   1   11
Learn how to write a MySQL cursor or a MySQL loop
In this article, we will learn to use MySQL cursors and for loops, which function like iterative loops in general programming. We will also cover variables, cursor, and handler declaration.

Image 1

If you've ever wanted to learn how to write a MySQL cursor or a MySQL loop, you've come to the right place. Let's iterate!

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 asensitive 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.

SQL
DECLARE <variable_name>  <variable_type>
Declaring a variable

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.

SQL
DECLARE <cursor_name> CURSOR FOR <select_statement>
Declaring a cursor for a 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:

SQL
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Using a NOT FOUND handler

The Open Statement

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

SQL
OPEN <cursor_name>
The OPEN statement syntax

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.

SQL
FETCH <cursor_name> INTO <variable_list>
The FETCH statement syntax

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

SQL
FETCH <cursor_name> INTO a, b, c
An example variable list

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.

SQL
CLOSE <cursor_name>
The CLOSE statement syntax

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.

Image 2

We will create a cursor that:

  • 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

SQL
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;
The MySQL procedure

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:

SQL
SET @average_goals = 0.0;
CALL cursordemo(@average_goals);
SELECT @average_goals;
Executing the procedure we just created

The output of this operation is:

1.080954670906067

Image 3

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.

This article was originally posted at https://arctype.com/blog/mysql-for-loop-cursor

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Writer Arctype
United States United States
Download our free SQL editor at https://www.arctype.com
This is a Organisation

3 members

Comments and Discussions

 
QuestionMessage Closed Pin
29-Nov-21 21:29
Home Schooling29-Nov-21 21:29 
NewsMySQL is set based ... Pin
CHill6018-Nov-21 21:57
mveCHill6018-Nov-21 21:57 
GeneralRe: MySQL is set based ... Pin
Arctype SQL19-Nov-21 3:59
professionalArctype SQL19-Nov-21 3:59 
GeneralRe: MySQL is set based ... Pin
CHill6019-Nov-21 4:03
mveCHill6019-Nov-21 4:03 
GeneralRe: MySQL is set based ... Pin
ArtArts Corp22-Nov-21 14:05
ArtArts Corp22-Nov-21 14:05 
GeneralRe: MySQL is set based ... Pin
CHill6022-Nov-21 21:49
mveCHill6022-Nov-21 21:49 
GeneralRe: MySQL is set based ... Pin
ArtArts Corp23-Nov-21 1:26
ArtArts Corp23-Nov-21 1:26 
GeneralRe: MySQL is set based ... Pin
CHill6023-Nov-21 1:55
mveCHill6023-Nov-21 1:55 
GeneralRe: MySQL is set based ... Pin
ArtArts Corp23-Nov-21 2:37
ArtArts Corp23-Nov-21 2:37 
GeneralRe: MySQL is set based ... Pin
CHill6023-Nov-21 4:34
mveCHill6023-Nov-21 4:34 
GeneralRe: MySQL is set based ... Pin
ArtArts Corp23-Nov-21 6:19
ArtArts Corp23-Nov-21 6:19 
GeneralRe: MySQL is set based ... Pin
CHill6023-Nov-21 8:45
mveCHill6023-Nov-21 8:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.