Understanding Cursors

Note

Requires MySQL 5 Support for cursors was added to MySQL 5. As such, this chapter is applicable to MySQL 5 or later only.

As you have seen in previous chapters, MySQL retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statementzero or more of them. Using simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows, for example. Nor is there an easy way to process all rows, one at a time (as opposed to all of them in a batch).

Sometimes there is a need to step through rows forward or backward and one or more at a time. This is what cursors are used for. A cursor is a database query stored on the MySQL servernot a SELECT statement, but the result set retrieved by that statement. Once the cursor is stored, applications can scroll or browse up and down through the data as needed.

Cursors are used primarily by interactive applications in which users need to scroll up and down through screens of data, browsing or making changes.

Note

Only in Stored Procedures Unlike most DBMSs, MySQL cursors may only be used within stored procedures (and functions).