A cursor allows a programmer to traverse a set of records retrieved by a relational database query in a sequential, one-by-one fashion. As such, a cursor forms an interface between the relationally oriented, declarative SQL language and the procedurally oriented calling context. A database cursor is like a pointer positioned somewhere in a resultset which can be moved programmatically to browse through all the records in the resultset. So, that's quite analogous to the wordprocessor cursor you use to browse through the characters and words on your computer screen.
(For those that are wondering what an implicit cursor is - well, that's just a piece of memory used by the database server to work with resulsets internally. Implicit cursors are not accessible via an externally exposed API, whereas explicit cursors are.)
Lots of php programmers are probably familiar with the cursor concept:
//connect to the database server
$connection = mysql_connect(
//get the 'cursor'
$cursor = $mysql_query(
'SELECT * FROM table1'
//browse through the 'cursor'
$row = mysql_fetch_row(
//process records, one by one.
The cursor interface is really convenient when you need to do complex resultset processing. Processing a resultset is quite common in a client program, especially when the resultset needs to be displayed or printed, but somwtimes, it can be quite useful to do resultset processing on the database server side.
In most cases, purely data-oriented problems can usually be solved without the explicit resulset processing provided by cursors. It is often much better to use implicit resulset processing using 'ordinary' SQL statements. In fact, ordinary SQL statements are very powerful, and should be used whenever it's possible.
However, in some cases, the cursor interface is easier to write and maintain, and in some cases, there's no ordinary SQL substitute for the cursor interface.
Let's think of an example that is screaming for explicit resultset processing.
Suppose we have a table of employees,
empand a stored procedure
p_calculate_salary. Now, assume that this stored procedure contains all the logic and business rules we need to calculate an employee's salary for a given month, including benefits, bonuses, and witheld fines for taking part in health or educational plans. What's more, the procedure does not only calculate all that, but also makes the necessary changes elsewhere in the system, trigger autonomous processes such as sending each employee it's monthly and (when applicable) annual salary reports.
For those that are still not convinced that all this logic should be embedded in a procedure, think of the following scenario. Suppose that the user that needs to calculate the salaries is not authorized to directly access the data that is needed to perform the business logic implemented by the procedure, and that the procedure is created using
SQL SECURITY DEFINER. In that case, there really is no way to solve this using ordinary SQL. Resultset processing using a cursor is really the only way in this case.
To conveniently do the salary run for a particular month, we could use a cursor to traverse the list of employees, getting the relevant employee data and plugging that into the procedure. This type of use case is typical for a cursor: We do not need any direct output, so an ordinary
SELECTis not necessary, spurious even.
Also, we cannot call a procedure in an ordinary
SELECT, and it is not always possible to wrap all the business rules into a function (which could be used inside a
This salary run is all back-end stuff. We do not want some external script doing this. We want to keep all those business rules and logic as closely tied to the database as we can, especially when the internals of the procedure need to refer to the database. So, that's why the database procedure is really the solution we want to use to calculate the salaries. Once we admit to that, we really cannot avoid cursor-based resultset processing anymore. So let's dig into some resultset processing...
The pseudocode for this operation would look like this:
for all records in the employee table do:
get the current employee record
calculate salary for the current employee record
until all employee records are processed.
In Oracle, you could write it like this:
v_col1 ....; --declare 1..N variables to access the record
v_col2 ....; --I left out the datatypes intentionally
cursor csr_emp --define the cursor
open csr_emp; --open the cursor
loop --traverse the cursor
fetch csr_emp into --fetch current record
exit when csr_emp%notfound; --stop traversal when no more records
p_calculate_salary( --process a record
close csr_emp; --release resources
exception when others then --capture all errors
if csr_emp%isopen then --release resources
This snippet illustrates the general pattern for working with a cursor:
- You define or declare the cursor.
- You open the cursor
- You fetch a record from the cursor, storing field values into variables
- You check if there are more records, and if so, repeat step 3. If not, proceed
- You close the cursor when you've fetched the last record.
Now, compare this to the equivalent MySQL syntax:
declare v_col1 ....; -- define variables to store record fields
declare v_col2 ....; -- datatypes intentionally ommitted
declare v_colN ....;
declare v_notfound -- define the loop control variable
BOOL default FALSE;
declare csr_emp -- define the cursor
declare continue handler -- handle cursor exhaustion
for not found
set v_notfound := TRUE; -- mark our loop control variable
declare exit handler -- handle other errors
close csr_emp; -- free resources before exit
open csr_emp; -- open cursor
fetch csr_emp into -- fetch record values
if v_not_found then -- exit the loop when the cursor is exhausted
call p_calculate_salary( -- process a record
close csr_emp; -- free resources
The ingredients are about the same for both examples. Both Oracle and MySQL open the cursor, fetch from it, and close it when it runs out of records.
The difference between MySQL and Oracle boils down to the difference in the way the loop control is handled.
In Oracle you could use a so-called cursor attribute. Cursor attributes are essentially builtin, readonly variables associated with the cursor. That's actually quite like member variables encountered in object oriented languages. To refer to a particular cursor attribute, you use this syntax:
cursor_name%attribute_name. Oracle defines the following cursor attributes:
In our snippet, we used the
NOTFOUNDattribute to check wheter the cursor was exhausted in order to exit the loop. We also used the
ISOPENattribute in the general catch-all
WHEN OTHERSexception handler wich allows us to explicitly close the cursor when it was left open when an exception occurred in the inner block.
Although MS SQL Server's Transact SQL dialect does not define cursor attributes, there's a device there used to write cursor loops that in practice resembles the Oracle
NOTFOUNDcursor attributes: the
@@FETCH_STATUS(session wide) global variable. Theres a similar global resembling the Oracle
The difference with Oracle cursor attributes is that in MS SQL, there's only one instance of the globals, reflecting the status of the last cursor statement. In practice, this is of no consequence because you usually only reference the cursor attributes in the immediate vincinity of the statements that could change the cursor status. T-SQL has one other device that provides information about the status of an arbitrary cursor (not just the current one), the
CURSOR_STATUS(), but usually, the globals are sufficient for most purposes.
MySQL does not define any cursor attributes, not any globals that provide cursor status information. In fact, cursor functionality is limited to opening a cursor, fetching values from it's current record, and closing it. Like in other rdbms products, fetching from an exhausted cursor will raise an error situation in MySQL. Because there's no general way to know on beforehand if the cursor is exhausted, there's nothing left to do but fetching from the cursor and handling the error situation. Always.
So, in MySQL, we must declare a
HANDLERfor the builtin
NOT FOUNDcondition. Usually, we want to continue our procedure when the cursor is exhausted, so this will usually be a
CONTINUEhandler. Inside the handler statement, we must place the statements that will update our loop control variable. Like in the Oracle case, we still have to check our loop control variable to actually exit the loop to stop the cursor traversal process.
When writing these loops for MySQL it might seems a bit awkward at first to use these contructs, especially when you are used to cursor attributes or cursor status globals. In my opinion, it's actually quite daft to have to rely on general error handling mechanisms for handling cursor exhaustion situations. After all, there's nothing exceptional or erroneous about the cursor becoming exhausted. But for now, it's no use sulking. This is just how MySQL does these things right now.