Cursors in MySQL Stored Procedures

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.

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 select – foreach – 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`@ 

Truncated by Planet PHP, read more at the original (another 19617 bytes)

more