A Cursor is a database structure that allows for the traversal of the records in a result set. In Sybase T-SQL a cursor functions by the following steps:
- Declare the Cursor:
To use a cursor you first must declare it by providing a name and the SELECT query that will provide the result set it will be traversing through. The Syntax for declaring the Cursor is as follows:
DECLARE cursor_name CURSOR FOR SELECT ....
- Open the Cursor:
Once the Cursor is declared we must open the cursor to utilize it:
- Fetch Rows:
Now that the Cursor is ready we use the FETCH command to return a result row. When fetching a row we need to fetch it into variables defined prior to the Fetch. To provide the user with if the query was successful the @@sqlstatus variable is available, where 0 means success, 1 = error, and 2 is no more data. We can use the @@sqlstatus variable in a WHILE condition to traverse the entire result set.
FETCH cursor_name INTO @var1, @var2, ... WHILE ( @@sqlstatus = 0 ) BEGIN // do something with the data FETCH cursor_name INTO @var1, @var2, ... END
- Close the Cursor:
After traversal of the result set we close the cursor by use of the CLOSE command:
- Deallocate the Cursor:
Last we deallocate the cursor so that Cursor is removed from memory and can not be opened again:
DEALLOCATE CURSOR cursor_name
Be advised that when using SELECT statements that JOIN multiple tables or have sub-queries that performance can be affected. To optimize it is good practice to perform such selects into a temporary table and that the cursor is set up to select data from that temp table.