Want to write a CURSOR LOOP with MySQL?

Like all procedural database languages I know, the MySQL stored procedure language supports explicit cursors. I just wrote "explicit cursor" but for I'll refer to those by just saying "cursor".

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:

<?php
    //connect to the database server
    $connection = mysql_connect(
        $_SESSION['DB_SERVER']
    ,   $_SESSION['DB_USER']
    ,   $_SESSION['DB_PASSWORD']
    );
    //get the 'cursor'
    $cursor = $mysql_query(
        'SELECT * FROM table1'
    ,   $connection
    );
    //browse through the 'cursor'
    while(
        $row = mysql_fetch_row(
            $cursor
        )
    ){
        //process records, one by one.
        echo('<div>');
        foreach(
            $row 
        as  $field
        ) {
            echo('<span>'); 
            echo($field); 
            echo('</span>'); 
        }
        echo('</div>');
    }
?>

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, emp and 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 SELECT is 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 SELECT).

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:

    declare       
        v_col1 ....;                      --declare 1..N variables to access the record
        v_col2 ....;                      --I left out the datatypes intentionally
        ...
        v_colN ....; 
        cursor csr_emp                    --define the cursor
        as
        select *
        from   emp
        ;
    begin
        open csr_emp;                     --open the cursor
        loop                              --traverse the cursor 
            fetch csr_emp into            --fetch current record
                v_col1
            ,   v_col2
            ..
            ,   v_colN
            ;
            exit when csr_emp%notfound;   --stop traversal when no more records

            p_calculate_salary(           --process a record 
                v_col1
            ,   v_col2
            ...
            ,   v_colN
            );
        end loop;
        close csr_emp;                    --release resources 
    exception when others then            --capture all errors 
        if csr_emp%isopen then            --release resources 
            close csr_emp;
        end if;
    end;

This snippet illustrates the general pattern for working with a cursor:

1. You define or declare the cursor.


2. You open the cursor


3. You fetch a record from the cursor, storing field values into variables


4. You check if there are more records, and if so, repeat step 3. If not, proceed


5. You close the cursor when you've fetched the last record.

Now, compare this to the equivalent MySQL syntax:

    begin  
        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
            cusor for 
            select *
            from   emp;
        declare continue handler          -- handle cursor exhaustion
            for not found 
            set v_notfound := TRUE;       -- mark our loop control variable
        declare exit handler              -- handle other errors
            for sqlexception
            close csr_emp;                -- free resources before exit
        open csr_emp;                     -- open cursor 
        cursor_loop: loop
            fetch csr_emp into            -- fetch record values
               v_col1
            ,  v_col2
            ...
            ,  v_colN
            ;
            if v_not_found then           -- exit the loop when the cursor is exhausted
                leave cursor_loop;
            end if;
            call p_calculate_salary(      -- process a record
                v_col1
            ,   v_col2
            ...
            ,   v_colN
            );
        end loop;
        close csr_emp;                    -- free resources
    end;

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: FOUND, NOTFOUND, ROWCOUNT and ISOPEN.

In our snippet, we used the NOTFOUND attribute to check wheter the cursor was exhausted in order to exit the loop. We also used the ISOPEN attribute in the general catch-all WHEN OTHERS exception 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 FOUND and NOTFOUND cursor attributes: the @@FETCH_STATUS (session wide) global variable. Theres a similar global resembling the Oracle ROWCOUNT attribute: @@CURSOR_ROWS.

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 HANDLER for the builtin NOT FOUND condition. Usually, we want to continue our procedure when the cursor is exhausted, so this will usually be a CONTINUE handler. 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.