Sybase Cursors

SQL is a set based language. This is excellent for many things but sometimes you want to step through a table processing one row at a time. An example would be if you were updating two or more tables, conditionally, from a single table. It might be possible to come up with acceptable set queries to perform the task but doing it in a linear manner gives far more control, albeit at the cost of greater processing time for the job.

Cursors provide a mechanism for dealing with data one row at a time. In essence, you must declare the cursor and memory variables in which to store the table's columns; create some form of control loop and use the fetch command to populate the memory variables. Here's a very simplistic example...

DECLARE @Salesman   CHAR(20),
        @SalesCount INT,
        @LastUpdate DATETIME

DECLARE SalesRead CURSOR
FOR
  SELECT employee,
         running_total,
         update
    FROM sales_control sc1
   WHERE update = (SELECT max(update)
                     FROM sales_control sc2
                    WHERE sc2.employee = sc1.employee)

OPEN SalesRead
FETCH SalesRead INTO @Salesman,
                     @SalesCount INT,
                     @LastUpdate

WHILE (@@sqlstatus=0)
   BEGIN
      -- Do whatever processing is required
      :
      :
      :
      -- last thing in the loop is to fetch next row...
      FETCH SalesRead INTO @Salesman,
                           @SalesCount INT,
                           @LastUpdate   
   END

CLOSE SalesRead


So, What's Happening Here?

First we declared the variables we would use to hold the output from the cursor and then we declared the cursor itself. Note that a cursor may be based on practically any legal read query and it's when there's a need to process data based on very complex queries that cursors come into their own.

Then we opened the cursor and read the first result row into the variables. At this point, Sybase sets @@sqlstatus to report what  happened when we did the read. 0 means that the read succeeded. We use this condition as the test for the loop which we now enter. Within the loop, we can carry out any legal Sybase processing. The important thing to note is that the final task within the loop is to read the next row from the cursor.

When there are no more rows to process, @@sqlstatus will be non-zero so we'll fall out of the loop. Logically, the thing to do here is to close the cursor and then proceed with any further processing.

Wheels Within Wheels.

Cursors and loops may be nested so you can legitimately do something like the following:

DECLARE variables
DECLARE cursor1
DECLARE cursor2
OPEN cursor1
FETCH cursor1 INTO variables
WHILE (@@sqlstatus=0)
   BEGIN
      OPEN cursor2
      FETCH cursor2 into variables
      WHILE (@@sqlstatus=0)
         BEGIN
            -- Do whatever processing is required
            :
            :
            :
            FETCH cursor2 INTO variables
         END
         CLOSE cursor2
         -- Do processing for main cursor
         :
         :
         :
         FETCH cursor1 INTO variables
    END
CLOSE cursor1

Provided you keep firmly in mind what cursor is performing which action you may nest as deeply as the permitted level of loop nesting for your version of Sybase. The problems arise when you lose track of what's going on, which is easily done if you are not fanatical about indentation and comments! 

Advantages and Disadvantages.

Cursors are sometimes the only practical way of translating business requirements into code and when they are useful they are invaluable.

Cursors are slow in Sybase and they can massively extend the run time for large batches. Also, they add more than one level of complexity, which is often made worse by the fact that cursors are, generally, at their most useful when dealing with exceptionally complex business logic.

Used with care, cursors can be a useful weapon in your armoury but used carelessly they can bite you hard!



Find out more by searching Google here...

Google