Cursors
Definition
A cursor
is a database object that permits the data set generated by a query to be
processed one row at a time. The cursor provides a pointer to a single row and
allows the information in that row to be extracted and processed. Once that
processing is complete, the cursor can be moved to another row. This allows
row-by-row processing that may be difficult to accomplish using other
techniques.
Commonly a cursor is combined with a WHILE
loop to enable row-by-row processing of an entire data set. In such cases, the
WHILE loop uses a cursor status function to determine whether the end of the
data has been reached. If it has, the loop exits. If not, the next row is
retrieved, or fetched, from the cursor and the loop restarts.
Considerations
Some developers and database administrators
dislike database cursors. This is because cursors use resources whilst they are
open and read data from the database each time a new row is fetched. This can
increase network traffic, lower the available resources of the SQL Server
instance and lead to poor performance. However, cursors can be useful when
row-by-row processing is required, particularly within stored procedures that
do not return the data and so do not generate excessive network traffic. As
with all development techniques, you should consider all available options and
select the most appropriate for the situation.
Using Cursors
DECLARE cursor-name CURSOR FOR query
In this syntax, cursor-name is used to provide a name for the cursor. The query element is the SELECT statement
that will be used to obtain information from the database. To declare a cursor
that will use all of the data from the view created earlier, you can use the
following:
DECLARE Customer CURSOR
FOR SELECT Customer Name, ContractNumber,
Service Duration, Amount,
FROM Customer
|
Opening a Cursor
A cursor cannot be used until it has been
opened using the OPEN statement. On opening, the query is executed and the
cursor is populated. To open the billing cursor, use the following:
OPEN Customer
|
Once a cursor is opened you can determine
the number of rows it has using the @@CURSOR_ROWS function. This function
returns the number of rows of the latest cursor to be opened.
PRINT @@CURSOR_ROWS -- Outputs
"4"
|
Fetching the Next Data Row
The cursor declared above is a forward-only
cursor. This means that it is only possible to advance through the rows one by
one. It is not possible to jump to a specific position, skip rows or to reverse
through the data. As such, there is only one available command that can be used
to obtain a row from the cursor. This command is "FETCH NEXT".
FETCH NEXT FROM Customer
|
If you execute this command from SQL Server
Management Studio you will see the first row of data displayed in the results
area. Each subsequent execution of the fetch returns the next row until the
cursor is exhausted.
Closing a Cursor
Once you have
finished working with a cursor it must be closed to free the resources
associated with it and to clear any locks that the cursor has created. To close
the cursor, execute the following:
CLOSE Customer
|
Deallocating a
Cursor
A closed cursor can
be reopened and reused multiple times. This is because the data structures of
the cursor are not released when the cursor is closed. To release these
structures and destroy the cursor after closing you should deallocate it. To
deallocate the sample cursor, execute the following:
DEALLOCATE Customer
|
Retrieving Cursor
Data into Variables
The example above
is interesting as it shows the use of a cursor. However, in a real-world
situation it would not be particularly useful. Generally, you will wish to
fetch the row data into variables that can then be used for further processing.
For our example we require five variables for the five columns in the query.
Declare the five variables as follows:
DECLARE @JobID UNIQUEIDENTIFIER
DECLARE @ContractNumber
INT
DECLARE @Duration
DECIMAL(4,2)
DECLARE @EngineerCost
MONEY
DECLARE @PartsCost
MONEY
|
To fetch the data
from the cursor into the variables, use the INTO clause. This clause is
followed by a comma-separated list of the variables to be populated. The first
variable will receive the value of the first column in the query, the second
from the second column and so on. One variable must be supplied for each of the
query's columns.
FETCH NEXT FROM Customer
INTO @CustomerID,
@ContractNumber, @Duration, @Amount
|
No comments:
Post a Comment