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 asenstive cursors.
Creating a MySQL cursor
To create a MySQL cursor, you'll need to work with the
The Declare statement
DECLARE statement can declare variables, cursors, and handlers. There is a sequence of declarations that needs to be adhered to:
You must first declare at least one variable to use later with the
FETCH statement later on.
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.
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:
The Open statement
OPEN statement initializes the result from the
DECLARE cursor statement.
The Fetch statement
FETCH statement works as an iterator. It fetches the next row from the rows associated with the
SELECT statement in the cursor declaration.
<variable_list> is one or more variables from the variables declared earlier.
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.
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:
- 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.
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:
The output of this operation is:
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.
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.
Are you ready to try out cursors for yourself? If so, visit the link to download Arctype and get started!CodeProject