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!

Thanks,
Hary

Thanks for this post proved to be helpful, but would the nesting impact the performance?

Thanks
-Sunny
[/quote]

(10-21-2008 07:16 AM)sybaseadmin Wrote:  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!

Thanks,
Hary