Cursors in MySQL Stored Procedures

Share this article

After my previous article on Stored Procedures was published on SitePoint, I received quite a number of comments. One of them suggested further elaboration on CURSOR, an important feature in Stored Procedures.

As cursors are a part of a Stored Procedure, we will elaborate a bit more on SP in this article as well. In particular, we will see how to return a dataset from an SP.

Key Takeaways

  • Cursors in MySQL are an essential component in stored procedures, acting as a “pointer” in C/C++ or an iterator in PHP’s foreach statement. They allow for the traversal and manipulation of a dataset, and are implemented within a stored procedure, carrying its benefits and limitations.
  • Cursors can be leveraged to calculate complex data results, such as the longest win/lose streak in a sports season, reducing data transfer amounts by returning the processed result instead of raw data. This can be done by declaring a cursor, opening it, fetching data from it, and closing it once the operation is complete.
  • Stored procedures can return scalar results or datasets. When returning a dataset from a stored procedure, a temporary table can be created to hold the output, with the structure requested by the output. The data returned from the stored procedure can then be inserted into the temporary table, and the dataset can be returned by selecting from the temporary table.
  • Cursors can be used in conjunction with other SQL statements within a stored procedure, allowing for complex operations on data. They are, however, read-only and non-scrollable, meaning rows can only be fetched in the order they appear in the result set, and the rows cannot be updated. Cursors can also be slower than a single SQL statement for large result sets, as they require more server resources.

What is a CURSOR?

A cursor can’t be used by itself in MySQL. It is an essential component in stored procedures. I would be inclined to treat a cursor as a “pointer” in C/C++, or an iterator in PHP’s foreach statement.

With cursors, we can traverse a dataset and manipulate each record to accomplish certain tasks. When such an operation on a record can also be done in the PHP layer, it saves data transfer amounts as we can just return the processed aggregation/statistical result back to the PHP layer (thus eliminating the selectforeach – manipulation process at the client side).

Since a cursor is implemented in a stored procedure, it has all the benefits (and limitations) of an SP (access control, pre-compiled, hard to debug, etc).

The official documentation on cursors is located here. It contains only four commands that are related to cursor declaration, opening, closing, and fetching. As mentioned above, we will also touch on some other stored procedure statements. Let’s get started.

A real world question

My personal website has a page showing the scores of my favorite NBA team: LA Lakers. The table structure behind it is straightforward:

Fig 1. The Lakers matches status table structure

I have been updating this table since 2008. Some of the latest records showing Lakers’ 2013-14 season are shown below:

Fig 2. The Lakers matches status table data (partial) for 2013-2014 season

(I am using MySQL Workbench as the GUI tool to manage my MySQL databases. You can use your favorite tool.)

Well, I have to admit Lakers are not playing very well these days. 6 consecutive losses up to Jan 15th. I get this “6 consecutive losses” by manually counting from the last played match all the way up (towards earlier games) and see how long an “L” (meaning a loss) in winlose column can appear. This is certainly doable but if the requirement becomes more complicated in a larger table, it takes more time and is more error prone.

Can we do this with a single SQL statement? I am not an SQL expert and I haven’t been able to figure out how to achieve the desired result (“6 consecutive losses”) from one SQL statement. The input of gurus will be highly appreciated – leave it in the comments below.

Can we do this in PHP? Yes, of course. We can retrieve the game data (particularly, the winlose column) for current season and do a traverse on the records to calculate the current longest win/lose streak. But to do that, we will have to grab all data for that year and most of the data will be wasted (as it is not likely for a team to have a win/lose streak for more than 20+ games in a 82-game regular season). However, we don’t know how many records should be retrieved into PHP to determine the streak, so this waste is a must. And finally, if the current win/lose streak is the only thing we want to know from that table, why pull all the raw data?

Can we do this via other means? Yes, it is possible. For example, we can create a redundant table specifically designed to store the current win/lose streak. Every insertion of the record will update that table too. But this is way too cumbersome and too error prone.

So, what is a better way to achieve this result?

Using Cursor in a Stored Procedure

As the name of this article suggests, we will see a better alternative (in my view) to solve this problem: using cursor in a Stored Procedure.

Let’s create the first SP in MySQL Workbench as follows:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
    declare current_win char(1);
    declare current_streak int;
    declare current_status char (1);

    declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

    set current_streak=0;

    open cur;

    fetch cur into current_win;
    set current_streak = current_streak +1;

    start_loop: loop
        fetch cur into current_status;
            if current_status <> current_win then 
                leave start_loop;
            else
                set current_streak=current_streak+1;
            end if;

    end loop;

    close cur;

    select current_streak into longeststreak;
    select current_win into `status`;
END

In this SP, we have one input parameter and two output parameters. This defines the signature of the SP.

In the SP body, we also declared a few local variables to hold the streak status (win or lose, current_win), current streak and current win/lose status for a particular match.

declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

The above line is the cursor declaration. We declared a cursor named cur and the dataset bind to that cursor is the win/lose status for those matches played (thus its winlose column is either “W” or “L” instead of nothing) in a particular year ordered by id (the latest played games will have the highest ID) descending.

Though not displayed explicitly, we can imagine that this dataset will contain a series of “L”s and “W”s. Based on the data shown in Figure 2 above, it should be: “LLLLLLWLL…” (6 Ls, 1 Ws, etc).

To calculate the win/lose streak, we begin with the latest (and the first in the dataset) match data. When a cursor is opened, it always starts at the first record in the associated dataset.

After the first data is grabbed, the cursor will move to the next record. In this way, a cursor behaves very much like a queue, traversing the dataset in a FIFO (First In First Out) manner. This is exactly what we wanted.

After getting the current win/lose status and set the streak number, we continue to loop through (traverse) the remainder of the dataset. With each loop iteration, the cursor will “point” to the next record until we break the loop or all the records are consumed.

If the next win/lose status is the same as the current win/lose status, it means the streak goes on and we increase the streak number by 1 and continue the traversing; otherwise, it means the streak discontinues and we can leave the loop earlier.

Finally, we close the cursor and release the resources. Then we return the desired output.

Next, we can enhance the access control of the SP as described in my previous article.

To test the output of this SP, we will write a short PHP script:

<?php
$dbms = 'mysql';

$host = 'localhost';
$db = 'sitepoint';
$user = 'root';
$pass = 'your_pass_here';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$cn->exec('call streak(2013, @longeststreak, @status)');
$res=$cn->query('select @longeststreak, @status')->fetchAll();

var_dump($res); //Dump the output here to get a raw view of the output

$win=$res[0]['@status']='L'?'Loss':'Win';
$streak=$res[0]['@longeststreak'];

echo "Lakers is now $streak consecutive $win.\n";

This will output something like the following figure:

(This output is based on Lakers’ match up to Jan 15th, 2014.)

Return a dataset from a Stored Procedure

A few discussions went along on how to return a dataset from an SP, which constructs the dataset out of the results from a few repeated calls to another SP.

A user may want to know more from our previously created SP that only returns a win/lose streak for one year; thus we can get a table showing the win/lose streaks for all the years in a form like:

YEAR Win/Lose Streak
2013 L 6
2012 L 4
2011 L 2

(Well, a more useful result can be to return the longest win streak and loss streak in a particular season. This requirement can be easily expanded from the previous SP so I will leave it to interested parties to implement. For the purpose of this article, we will stick to the current win/loss streak.)

MySQL SP can only return scalar results (an integer, a string, etc), unless the result is returned by a select ... from ... statement (and it becomes a dataset). The issue here is that the table-form data we want to see does not exist in our current db structure and is constructed from another SP.

To tackle this, we need the help of a temporary table, or if situation allows and requires, a redundant table. Let’s see how we can achieve our target via a temporary table.

First, we will create a second SP as shown below:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
    declare cur_year, max_year, min_year int;

    select max(year), min(year) from lakers into max_year, min_year;

    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));

    set cur_year=max_year;

    year_loop: loop
        if cur_year<min_year then
            leave year_loop;
        end if;

        call streak(cur_year, @l, @s);
        insert into yearly_streak values (cur_year, @l, @s);

        set cur_year=cur_year-1;
    end loop;

    select * from yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;

END

A few key things to notice here:

  1. We determine the max year and min year by selecting from the table lakers;
  2. We created a temp table to hold the output, with the structure requested by the output (season, streak, win);
  3. In the loop, we first execute our previously created SP, with necessary parameters (call streak(cur_year, @l, @s);), then grab the data returned and insert into the temp table (insert into yearly_streak values (cur_year, @l, @s);).
  4. Finally, we select from the temp table and return the dataset, then do some cleaning (DROP TEMPORARY TABLE IF EXISTS yearly_streak;).

To get the results, we create another short PHP script as shown below:

<?php
... // Here goes the db connection parameters

$cn=new PDO($dsn, $user, $pass);

$res=$cn->query('call yearly_streak')->fetchAll();

foreach ($res as $r)
{
    echo sprintf("In year %d, the longest W/L streaks is %d %s\n", $r['season'], $r['streak'], $r['win']);
}

And the display will be like:

Please note that the above is a bit different from calling our first SP.

The first SP does not return a dataset but only two parameters. In that case, we use PDO exec then query to fetch the output; while in the second SP, we returned a dataset from the SP, so we use PDO query directly to invoke the call to the SP.

Voila! We did it!

Conclusion

In this article, we dug further into MySQL stored procedures and took a look at the cursor functionality. We have demonstrated how to fetch scalar data by output parameters (defined as out var_name vartype in the SP declaration) and also fetch a calculated dataset via a temp table. During this process, a few statements otherwise used in stored procedures also surfaced.

The official documentation on the syntax of stored procedure and various statements can be found on the MySQL website. To create a stored procedure, please refer to these documents and to understand the statements, please see here.

Feel free to comment and let us know your thoughts!

Frequently Asked Questions (FAQs) about MySQL Cursors and Stored Procedures

What is the purpose of using cursors in MySQL stored procedures?

Cursors in MySQL stored procedures are used to handle multiple rows of data returned from a SELECT statement. They allow you to control the flow of rows from a result set by providing the ability to navigate through each row one at a time. This is particularly useful when you need to perform complex logic on each row, which would be difficult or impossible to achieve with a single SQL statement.

How do I declare a cursor in MySQL?

In MySQL, you declare a cursor within a stored procedure using the DECLARE keyword followed by the cursor name and the SELECT statement that retrieves the rows. Here’s an example:
DECLARE cursor_name CURSOR FOR SELECT_statement;
The SELECT statement can be any valid SELECT statement that retrieves rows from a table.

How do I use a cursor in a stored procedure?

After declaring a cursor in a stored procedure, you can use it by opening the cursor, fetching rows from it, and then closing it when you’re done. Here’s an example:
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @variable_name;
CLOSE cursor_name;
You can fetch rows from the cursor in a loop until no more rows are available.

What are the limitations of cursors in MySQL?

While cursors are powerful tools, they do have some limitations. For instance, cursors are read-only and non-scrollable. This means you can only fetch rows in the order they appear in the result set, and you cannot update the rows you fetch. Additionally, cursors can be slower than a single SQL statement for large result sets, as they require more server resources.

Can I use multiple cursors in a single stored procedure?

Yes, you can declare and use multiple cursors in a single stored procedure. Each cursor must have a unique name, and you must open, fetch from, and close each cursor separately.

How do I handle errors when using cursors?

You can handle errors when using cursors by declaring a CONTINUE HANDLER for the NOT FOUND condition. This handler will be invoked when a FETCH statement fails to retrieve a row. Here’s an example:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
In this example, the @done variable is set to 1 when no more rows are available to fetch.

What is the difference between a cursor and a SELECT statement?

A SELECT statement retrieves rows from a table, while a cursor allows you to control the flow of these rows. With a cursor, you can navigate through each row one at a time, which is useful when you need to perform complex logic on each row.

Can I use cursors outside of stored procedures?

No, cursors in MySQL can only be used within stored procedures or functions. They are not available in the standard SQL context.

How do I optimize the performance of cursors?

To optimize the performance of cursors, you should minimize the number of rows retrieved by the SELECT statement. You can do this by using WHERE clauses to filter out unnecessary rows. Additionally, you should close cursors as soon as you’re done with them to free up server resources.

Can I use cursors with other SQL statements?

Yes, you can use cursors with other SQL statements within a stored procedure. For instance, you can use a cursor to fetch rows from a SELECT statement, and then use an INSERT, UPDATE, or DELETE statement to modify the rows in some way.

Taylor RenTaylor Ren
View Author

Taylor is a freelance web and desktop application developer living in Suzhou in Eastern China. Started from Borland development tools series (C++Builder, Delphi), published a book on InterBase, certified as Borland Expert in 2003, he shifted to web development with typical LAMP configuration. Later he started working with jQuery, Symfony, Bootstrap, Dart, etc.

cursorsmariaDBmysqlPHPstored procedure
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week