Database Reference
In-Depth Information
24
In this chapter, you learn what cursors are and how to use them.
As you have seen in previous chapters, MariaDB retrieval operations work
with sets of rows known as result sets. The rows returned are all the rows that
match a SQL statement—zero or more of them. Using simple
SELECT
state-
ments, there is no way to get the first row, the next row, or the previous ten
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 MariaDB server—not 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, MariaDB cursors (like those in
MySQL) may only be used within stored procedures (and functions).
Using cursors involves several distinct steps:
1.
Before a cursor can be used it must be declared (defined). This process
does not actually retrieve any data; it merely defines the
SELECT
state-
ment to be used.
