SQL Cursor Options
In our example so far we haven't specified any additional options for our
cursor, meaning that we're relying on using the default type of cursor in SQL. The
extra options that you can specify for a cursor can affect which fetch options you
can use, and what you can do with the data that your cursor will look at. This
part of the blog series examines the different options for cursors that you can use
in SQL.
The Scope of a Cursor
You can specify that your cursor will be either LOCAL or
GLOBAL. This setting affects where you can refer to your cursor
within the code you are writing, as described in the table below:
|
Setting
|
Description |
|
LOCAL |
This means that the cursor can only be referred to within the batch, stored
procedure, or trigger in which it was created. |
|
GLOBAL |
This means that the cursor can be referred to from anywhere within the
connection in which it was created, including any stored procedure which is
executed in that batch. |
For a local cursor you must make sure you perform all of the actions within a
single batch of statements. The code below would fail because a new batch is
started between declaring the cursor and opening it:
USE Movies
GO
DECLARE FilmCursor CURSOR LOCAL
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
GO
OPEN FilmCursor
The error message you would see is shown below:
The cursor is out of scope.
With a global cursor the code will work:
USE Movies
GO
DECLARE FilmCursor CURSOR GLOBAL
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
GO
OPEN FilmCursor
If you don't specify the scope of a cursor it will use whichever type is
specified by the Default Cursor option of the database. You can modify
this option as described below:
- In the Object Explorer, right-click on the name of your
database and choose Properties.
- In the dialog box which appears, select the Options
category from the list on the left hand side.
Choose to view the
Options page.
- Set the value of the Default Cursor option
and click OK.
Choose from the two options in the drop down list.
Specifying the Scroll Type
The scroll type of a cursor determines which fetch
statements you're allowed to use. The two settings you can use for the scroll
type are described in the table below:
|
Scroll Type |
Description |
|
FORWARD_ONLY |
The cursor can only move forwards through the set of records. You can only use
the FETCH NEXT statement with this type of cursor. |
|
SCROLL |
The cursor can move in any direction. You can use any of the fetch
statements with this type of cursor. |
If you don't specify which scroll type to use your cursor will default to
FORWARD_ONLY. If you try to use an unsupported fetch statement with this type of cursor you will see an error message
when you execute the code. The example below attempts to use the FETCH
FIRST statement on a FORWARD_ONLY cursor:
DECLARE FilmCursor CURSOR FORWARD_ONLY
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH FIRST FROM FilmCursor
The error message that appears when this code is executed is shown below:
You need to use a
SCROLL cursor if you want to use FETCH FIRST.
Specifying the Type of Record Set
This setting affects what you can do with the data returned by the cursor and
also which fetch options are available. The four options that you can apply for
this setting are described in the table below:
|
Record Set Type
|
Description |
|
STATIC |
This type of cursor makes a copy of the data it uses in
the tempdb database. If any changes are made to the base data
while this cursor is open you would not see those changes when you fetch
records. This type of cursor does not allow modifications to data. |
|
KEYSET |
When this type of cursor is opened it creates a table in the tempdb
database which records the membership and order of the rows in the record set.
This keyset
table stores the unique identifiers of the records in the record set. If other
users make changes to the non-key values in the record set this type of cursor
will reflect the changes when you fetch records. |
|
DYNAMIC |
This type of cursor always reflects all of the changes made to the data which
populates the record set. You can't use the FETCH ABSOLUTE
statement with this type of record set. |
|
FAST_FORWARD |
This type of cursor can only move forwards through the record set using the
FETCH NEXT statement. It has performance optimisations to make
it work faster but it doesn't allow you to modify any data. |
If you provide a value for this setting without specifying whether the cursor
is FORWARD_ONLY or SCROLL this option will be
set automatically according to the table below:
|
Specified record set type
|
Default scroll type |
|
FAST_FORWARD |
FORWARD_ONLY |
|
STATIC, KEYSET or DYNAMIC |
SCROLL |
Specifying the Lock Type
This setting affects what happens if you attempt to modify data returned by
the cursor. If you don't specify the setting for this option, by default the
cursor is allowed to modify data. The three values you can use for the lock type
are described in the table below:
|
Lock type
|
Description |
|
READ_ONLY |
You can't make any changes to data using this type of cursor. If you set the
record set type to FAST_FORWARD you will automatically create a
READ_ONLY cursor. |
|
SCROLL_LOCKS |
With this setting a record is locked as soon as the cursor scrolls to it meaning
that it will be available for modifications. This ensures that updates or
deletes performed by the cursor will always succeed. You cannot use this setting
if you have also specified STATIC or FAST_FORWARD. |
|
OPTIMISTIC |
Records are not locked as soon as they are read into the cursor. When you
attempt to modify data with this cursor SQL Server checks to see if the record
has been altered since it was read into the cursor - if so your update or delete
will fail. |
Combining Cursor Options
You can specify a scope, scroll type, record set type and lock type for a
cursor in a single declaration. The example below declares a global,
forward only, static, read only cursor:
DECLARE FilmCursor CURSOR GLOBAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
Take care when doing this as some settings are mutually exclusive. For
instance, you can't declare a FAST_FORWARD cursor which also
has the SCROLL option specified:
DECLARE FilmCursor CURSOR SCROLL FAST_FORWARD
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
Attempting to execute the code above results in the following error message:
If only all errors were described this clearly!
What's Next?
For the final part of this blog series we'll look at how you can use a cursor
to update the data in a record set.
