Looping Through All
Data in a Cursor
Cursors are
commonly used with WHILE loops to process every row returned by the query
individually. The first fetch operation is performed outside of the loop. This
obtains the first row, if one is present, and sets a flag that specifies
whether the cursor is exhausted or if further rows are present. The flag is
accessed using the @@FETCH_STATUS function. If the fetch successfully found a
row, the return value will be zero. A WHILE loop can use this function to
determine whether further iterations are required.
To demonstrate,
execute the following batch. This loops through all of the billing data. To
simulate the integration to the third party system, information from each row
is outputted to the Messages window.
DECLARE @ CustomerID
UNIQUEIDENTIFIER
DECLARE @ContractNumber
INT
DECLARE @Duration
DECIMAL(4,2)
DECLARE @ Amount MONEY
DECLARE Customer CURSOR
FOR SELECT Customer
Name, ContractNumber, Service Duration, Amount
FROM Customer
OPEN Customer
FETCH NEXT FROM Customer
INTO @CustomerID,
@ContractNumber, @Duration, @Amount
WHILE @@FETCH_STATUS
= 0
BEGIN
PRINT
'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'
FETCH
NEXT FROM Customer
INTO
@CustomerID, @ContractNumber, @Duration, @Amount
END
CLOSE Customer
DEALLOCATE Customer
|
Insensitive Cursors
By default, cursors
read each row of data from database tables as required, according to the query
that is specified. If the data is modified after the cursor is opened, the
modified data will be presented when fetched. If a user has deleted rows, these
will not be accessible via the cursor. In some situations this is undesirable.
An insensitive cursor is not affected by
changes to the underlying data. When the cursor is opened, a temporary table is
created in the tempdb database. This table is populated with the results of the
query immediately. Once populated, each fetch retrieves information from the
temporary table, rather than the live data.
To specify that a
cursor is insensitive, add the INSENSITIVE clause immediately after the cursor
name in the declaration. For example:
DECLARE Customer INSENSITIVE
CURSOR
FOR SELECT Customer
Name, ContractNumber, Service Duration, Amount
FROM Customer
|
Scrollable Cursors
A forward only
cursor is also known as a non-scrollable cursor. The alternative is a scrollable
cursor. Scrollable cursors are more flexible than forward only versions as they
allow movement both forwards and backwards through the data. They also allow
you to directly jump to the first or last rows or to a specific row number from
the query. However, scrollable cursors generally give lower performance than
forward only cursors.
To declare a
scrollable cursor, add the SCROLL clause to the declaration as follows:
DECLARE Customer SCROLL
CURSOR
FOR SELECT Customer
Name, ContractNumber, Service Duration, Amount
FROM Customer
|
When using a
scrollable cursor, six FETCH commands are available:
§ FETCH NEXT. Retrieves the
next row.
§ FETCH PRIOR. Retrieves the
previous row.
§ FETCH FIRST. Retrieves the
first row from the query results.
§ FETCH LAST. Retrieves the
final row of the query results.
§ FETCH ABSOLUTE. Retrieves a
specific row from the results. The row is provided as an integer. If positive, the
row is counted from the start of the data set. A value of one indicates the
first row, two indicates the second row and so on. If the value is negative,
the row is counted from the end of the results. A value of -1 indicates that
the last row should be retrieved.
§ FETCH RELATIVE. Used with an
integer parameter n, this FETCH statement retrieves the row that is n
results from the current row. A value of one retrieves the next row, -1 obtains
the previous row, etc.
FETCH NEXT
FETCH PRIOR
FETCH FIRST
FETCH LAST
FETCH ABSOLUTE 3
FETCH RELATIVE -1
|
The following
example fetches the last row from the cursor first. It then processes the rows
in reverse order using FETCH PRIOR:
DECLARE @ CustomerID
UNIQUEIDENTIFIER
DECLARE @ContractNumber
INT
DECLARE @Duration
DECIMAL(4,2)
DECLARE @ Amount MONEY
DECLARE Customer SCROLL
CURSOR
FOR SELECT @CustomerID,
@ContractNumber, @Duration, @Amount
FROM Customer
OPEN Customer
FETCH LAST FROM Customer
INTO @CustomerID,
@ContractNumber, @Duration, @Amount
WHILE @@FETCH_STATUS
= 0
BEGIN
PRINT
'Sending job ' + CONVERT(VARCHAR(36), @JobID) + ' to billing system.'
FETCH
PRIOR FROM Customer
INTO
@CustomerID, @ContractNumber, @Duration, @Amount
END
CLOSE Customer
DEALLOCATE
|
No comments:
Post a Comment