Using a CURSOR is not normally the best way to process through a set of records. Yet when a seasoned programmer moves to writing TSQL for the first time they frequently look for ways to process a sets of records one row at a time. They do this because they are not used to thinking about processing records as a set. In order to process through a TSQL record set a row at a time you can use a cursor. A cursor is a record set that is defined with the DECLARE CURSOR statement. Cursors can be defined as either read-only or updatable. In this article I will introduce you to using cursors to do record level processing one row at a time.

DECLARE CURSOR syntax

Now that you know a little about what a cursor is, let's look at the syntax of the DECLARE CURSOR statement. The DECLARE CURSOR has two different formats: ISO and Extended TSQL. Below you can find the syntax for both formats.

ISO Format:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

TSQL Extended format:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Where:

• cursor_name
  • Is the name you want to associate with the cursor being created.
• INSENSITIVE
  • Defines that a temporary table should be create, and that temporary table will be used to retrieve rows from the cursor.
• SCROLL
  • Specifies that you can scroll through the cursor using any one of the following FETCH options: FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE. This options cannot be used if the FAST_FORWARD option is also used.
• select_statement
  • Identifies the SELECT statement that will be used to populate the cursor.
• READ_ONLY
  • Means the cursor rows cannot be updated.
• FOR UPDATE [OF column_name [,…n]
  • Identifies the columns within the cursor that are updatable. If the FOR UPDATE is specified without a column list then all columns are updatable.
• LOCAL
  • Identifies the scope of the cursor to be local. A local cursor is only available to the batch, stored procedure or trigger in which the cursor was defined.
• GLOBAL
  • Identifies that the scope of the cursor to be at the connection level, which mean the cursor can be executed by any batch, stored procedure or trigger with the same connection.
• FORWARD_ONLY
  • Specifies that the cursor can only read from the beginning to the end of the cursor
• STATIC
  • Identifies that a copy of the rows in the cursor should be stored in a temp table, so if rows are updated while the cursor is open those updates are not reflected in the rows returned from the temporary table.
• KEYSET
  • Identifies that the membership and order of rows are set at the time SQL Server opens the cursor.
• DYNAMIC
  • Identifies rows in the cursor should reflect all updates.
• FAST_FORWARD
  • Identifies that the cursor is a FORWARD_ONLY, and READ_ONLY cursor.
• READ_ONLY
  • Makes it so the cursor can't be used for updates.
• SCROLL_LOCKS
  • Specific that rows are to be locked when they are read by a cursor that is performing an UPDATE or DELETE statement. SCROLL_LOCKS is not available for FAST_FORWARD or STATIC defined cursors
• TYPE_WARNING
  • Identifies that a message will be sent to client when the cursor is implicitly converted. Note for more information about converting cursors read books on line topic label “Implicit Cursor Conversion”

As you can see there are a lot of different options that can be used when you declare a cursor. I will not be covering all these options in this article. Additionally this article will focus on using the TSQL extended format. For more information about the DECLARE CURSOR syntax, please refer to Books Online documentation.

Sample Data for Examples

In order to demo using cursors I need some sample data. The code in Listing 1 will create my sample data.

USE tempdb;
GO
CREATE TABLE State (
    StateName  varchar(50),
    StateAbbr  char(2));
INSERT INTO dbo.State VALUES 
      ('Washington','WA'),
('Oregon','OR'),
('Idaho', 'ID'),
('Alaska','AK');

Listing 1: Script to create sample data

The code in Listing 1 creates a table named State, and populates it with 4 different rows. I will be using this table for my examples.

How to Define a Read-Only cursor

A read-only cursor is a cursor that cannot be used for updates or deletes. To define and use a read-only cursor I will use the code in Listing 2.

USE tempdb;
GO
SET NOCOUNT ON;
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR READ_ONLY FOR 
  SELECT StateName, StateAbbr
  FROM dbo.State
  ORDER BY StateName;
  
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'The abbreviation for the state of ' + 
         @StateName + ' is ' + @StateAbbr;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;

Listing 2: Declaring and using a read only cursor

When I run the code in Listing 2 the messages in Report1 are displayed on the Messages tab.

The abbreviation for the state of Alaska is AK
The abbreviation for the state of Idaho is ID
The abbreviation for the state of Oregon is OR
The abbreviation for the state of Washington is WA

Report 1: Output from running Listing 2

In the code found in Listing 2, I first declared a couple of variables to hold data read from the cursor. Then I declared a cursor named state_cursor. With the DECLARE CURSOR statement I defined my SELECT statement which identifies the list of columns, and the criteria that will be used to populate the state_cursor. In this example I'm populating my cursor with all the rows in the State table in the order of the StateName. Because I used the READ_ONLY option on my DECLARE CURSOR statement my cursor will be a read-only cursor.

Before I can use the data in the cursor I defined I must first open it. I do this with the OPEN statement. To return rows from my cursor I use the FETCH statement. On the FETCH statement I specified the NEXT option. The first time I use the FETCH NEXT statement after declaring a cursor will fetch the first row of my cursor. After I have fetched the first row all subsequent execution of the FETCH NEXT statement will return the next row in the cursor. On the FETCH statement I also have identify which local variables will be populated for each column in my cursor. In my example I populate @StateName, and @StateAbbr.

After the first FETCH statement I check the status returned by this statement by using the @@FETCH_STATUS function. The @@FETCH_STATUS function returns the status of last cursor FETCH statement. This function may return 0, -1, or -2 values depending on the status of the last FETCH statement. If the return value is 0 this means that the last execution of the cursor FETCH statement successfully returned a row. If the return value is -1 this means the last FETCH statement either failed, or there are no more rows to fetch from the cursor. If the return value is -2 means the row being returned is missing. I use the value of the @@FETCH_STATUS function for the WHILE loop condition, so I can loop through the cursor until all rows have been fetched.

If my last FETCH statement was successful then my code enter the WHILE loop. In that loop I first execute a PRINT statement that displays the @StateName and @StateAbbr column values. The last statement in the WHILE loop reads the next cursor row using the FETCH NEXT statement. The WHILE loop will continues processing rows in the cursor until there are no more rows to fetch. When the FETCH statement in the WHILE loop detects there are no rows left it will set the @@FETCH_STATUS to a -1 value. When this happens the WHILE loop ends.

Scope of Cursor

A cursor can be defined to have one of two different scopes: LOCAL or GLOBAL. GLOBAL cursors are available in different batches created using the same connection. LOCAL cursors are only available in the scope of the batch that defines the cursor. If neither the GLOCAL nor LOCAL scope is provided then the default setting for a cursor is based off database “default cursor” setting. To demonstrate using a LOCAL or GLOBAL scope of a cursor let's look at the code in List 3.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR LOCAL READ_ONLY FOR 
  SELECT StateName, StateAbbr
  FROM dbo.State
  ORDER BY StateName;
GO 
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'The abbreviation for the state of ' + 
         @StateName + ' is ' + @StateAbbr;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;

Listing 3: Defining a LOCAL cursor.

When I run the code in Listing 3 it returns multiple errors in the message tab. These messages are shown in Report 2.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'state_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 18
A cursor with the name 'state_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 31
A cursor with the name 'state_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 32
A cursor with the name 'state_cursor' does not exist.

Report 2: Output from running Listing 2

The code in Listing 2 is the same as Listing 1, except I placed the keyword “LOCAL” on the DECLARE CURSOR statement. By doing this I defined my StateCursor as a local cursor. Additionally I broke the code up into two batches by placing a GO separator right after the local cursor is declared. The GO terminates the batch, so the remaining statements in a different batch then the batch that defined the cursor. By doing this the code in Listing 2 returns 4 different ‘cursor does not exist' messages as shown in Report 2. These messages are produced because the DEFINE CURSOR statement is in a different batch then the other cursor related statements.

In order to make a cursor available in all batches created by a given connection I could use the GLOBAL keyword as I have done in Listing 4.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR GLOBAL READ_ONLY FOR 
  SELECT StateName, StateAbbr
  FROM dbo.State
  ORDER BY StateName;
GO 
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'The abbreviation for the state of ' + 
         @StateName + ' is ' + @StateAbbr;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;

Listing 4: Cursor with GLOBAL scope

The code in Listing 4 is same as the code in Listing 3, except that I defined my state_cursor as GLOBAL. By doing this I now can run this code without errors. I'll leave it up to you to confirm the code in listing 4 runs without errors using a GLOBAL cursor.

Updating Columns Using a Cursor

There might be a time when you want to process through a set of records and based on the valus in record determine whether or not to update or to delete the cursor row. This is where an updateable cursors comes in handy. Suppose I want to process through my StateTable table and update the StateName and StateCode when the StateCode is either a WA, or OR. I could meet this requirement with my updateable cursor example in Listing 5.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR FOR
  SELECT StateName, StateAbbr
  FROM dbo.State
  FOR UPDATE;
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  -- StateAbbr one that needs updating
  IF (@StateAbbr = 'WA') 
UPDATE dbo.State SET StateName = 'California',
                     StateAbbr = 'CA'
WHERE CURRENT OF state_cursor;
  IF (@StateAbbr = 'OR') 
UPDATE dbo.State SET StateName = 'Arizona',
                     StateAbbr = 'AZ' 
WHERE CURRENT OF state_cursor;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;
-- Display Updated State table
SELECT StateName, StateAbbr FROM dbo.State;

Listing 5: Updateable Cursor

When I run the code in listing 5 I get the output in Report 3.

StateName                                          StateAbbr
-------------------------------------------------- ---------
California                                         CA
Arizona                                            AZ
Idaho                                              ID
Alaska                                             AK

Report 3: Output when I run TSQL in Listing 5

The code in Listing 5 creates an updatable cursor. I defined this cursor to be updateable by adding the FOR UPDATE clause at the end of the DECLARE CURSOR statement. Since the FOR UPDATE clause didn't identify any column names I can update any column in the cursor. To update rows, there are two UPDATE statements within the body of the WHILE loop. The first update statement is used to update the StateName to ‘California' and the StateAbbr to ‘CA' when the current cursor row being processed has a @StateAbbr value of ‘WA'. The second update statement is to update the StateName to ‘Arizona' and the StateAbbr to ‘AZ' when the current cursor row being processed has a @StateAbbr value of ‘OR'. By looking at the output in Report 3 you can see that records for the state of Washington and Oregon have now been replaced with California and Arizona.

In order for SQL Server to update a record it needs to be locked first. The code in listing 5 uses optimistic locking. When optimistic locking is used SQL Server needed to validate that the record has not been updated outside the cursor. It does this by comparing the cursor row to the actual table row. If SQL Server finds out that a record has been updated after the record has been read into the cursor it will return the error in Report 4.

Optimistic concurrency check failed. The row was modified outside of this cursor.
Msg 16947, Level 16, State 1, Line 28
No rows were updated or deleted.
The statement has been terminated.

Report 4: Error when trying to update a row in cursor using optimistic locking when row was updated outside of the cursor

If you want to guarantee that all updates in a cursor are successful you need to make sure the records are locked when they are read into the cursor. You can accomplish this by using the SCROLL_LOCKS option when defining the cursor, like I have done in Listing 6.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR SCROLL_LOCKS FOR 
  SELECT StateName, StateAbbr
  FROM dbo.State
  FOR UPDATE;
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
 
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  -- StateAbbr one that needs updating
  IF (@StateAbbr = 'CA') 
UPDATE dbo.State SET StateName = 'Washington',
                     StateAbbr = 'WA'
WHERE CURRENT OF state_cursor;
  IF (@StateAbbr = 'AZ') 
UPDATE dbo.State SET StateName = 'Oregon',
                     StateAbbr = 'OR' 
WHERE CURRENT OF state_cursor;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;
-- Display Updated State table
SELECT StateName, StateAbbr FROM dbo.State;

Listing 6: Updateable Cursor with SCROLL_LOCKS

The only difference between Listing 5 and Listing 6 is that I added the SCROLL_LOCKS option to the DECLARE CURSOR statement and changed my UPDATE statements to change StateName and StateAbbr back to their original Washington and Oregon values. With the SCROLL_LOCKS option I can guarantee that all my UPDATE statements in my state_cursor get work without getting the optimistic concurrency check failure, because the rows where locked for update as they were read into the cursor.

One thing to consider is the performance impact of using the SCROLL_LOCKS option. This option will lock all the rows in the cursor. Keep in mind that doing this might cause a serious blocking issues, with other connections that might be trying to update the same rows that where loaded into the cursor.

Restricting Which Columns can be Updated Using a Cursor

In the prior section I showed you examples of cursor definitions that allowed me to update all columns in my State table. If you want to create a cursor that restricts updates to a subset of columns then you can identify the set of columns that are updatable on the DECLARE CURSOR statement, as I have done is Listing 7.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR FOR
  SELECT StateName, StateAbbr
  FROM dbo.State
  FOR UPDATE OF StateName;
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Get the first state
FETCH NEXT FROM state_cursor 
INTO @StateName, @StateAbbr;
-- Process while state found
WHILE @@FETCH_STATUS = 0
BEGIN
  -- StateAbbr one that needs updating
  IF (@StateAbbr = 'WA') 
UPDATE dbo.State SET StateName = 'California',
                     StateAbbr = 'CA'
WHERE CURRENT OF state_cursor;
  IF (@StateAbbr = 'OR') 
UPDATE dbo.State SET StateName = 'Arizona',
                     StateAbbr = 'AZ' 
WHERE CURRENT OF state_cursor;
  -- Get the next state 
  FETCH NEXT FROM state_cursor 
  INTO @StateName, @StateAbbr;
END 
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;
-- Display Updated State table
SELECT StateName, StateAbbr FROM dbo.State;

Listing 7: Restricting which columns can be updated

When I run the code in Listing 7 I get the error in Report 5.

Msg 16932, Level 16, State 1, Line 25
The cursor has a FOR UPDATE list and the requested column to be updated is not in this list.

Report 5: Error received when updating more columns then allowed by cursor

I got this error because I tried to update column StateAbbr when the column was not identified in the FOR UPDATE clause when I declared my cursor. To resolve this error I need to remove the StateAbbr column from the SET clause on my two UPDATE statements in Listing 7.

How Optimistic Locking Works.

As stated above optimistic locking of cursor rows occurs by default when you define an updatable cursor without the SCROLL_LOCKS option. It also occurs when you use the OPTIMISTIC option on an updatable cursor. But how does optimistic locking work?

Having a cursor with optimistic locking allows others to update rows in the database that are in the cursor while the optimistic cursor is open. But there is a cost associated with optimistic locking. With optimistic locking SQL Server needs to make sure updates made through a cursor do not replace updates that were made by other sessions after a row was loaded into the cursor. To do this SQL Server needs to compare the row being update via the cursor with the row in the database to see if they are different. If they are different then you will get the “Optimistic concurrency failure check” error messages as show in report 4 if your cursor uses optimistic locking.

To compare the rows SQL Server uses one of two methods depending on whether or not the table contains a timestamp column. If the table contains a timestamp column SQL Server is able to compare the timestamp value on the row in the cursor with the timestamp value on the row in the database to determine if the row has been updated since the row was loaded into the cursor. If the table doesn't have a timestamp column SQL Server has to compare the column values in the cursor with the column values in the database to determine if the underlying table row has been updated since it was loaded into the cursor.

Scrolling Around a Cursor

In my cursor examples so far I have just shown how to use a cursor to sequentially scroll through a cursor using the FETCH NEXT option. There are other FETCH options that allow you to scroll through a cursor without necessarily going from the beginning to the end. But in order to use the different scroll options I need to define my cursor so it allows me to use these other FETCH options. The code in Listing 8 shows how to create a cursor that allows me to scroll around and retrieve different cursor rows using different FETCH options.

USE tempdb;
GO
SET NOCOUNT ON;
DECLARE state_cursor CURSOR SCROLL FOR
  SELECT StateName, StateAbbr
  FROM dbo.State
  ORDER BY StateName;
-- Display list of rows in table
SELECT StateName, StateAbbr
FROM dbo.State
ORDER BY StateName;
-- Variables to hold results from cursor
DECLARE @StateName varchar(50);
DECLARE @StateAbbr char(2);
OPEN state_cursor;
  
-- Fetch the last row in the cursor
FETCH LAST FROM state_cursor
INTO @StateName, @StateAbbr;
SELECT @StateName, @StateAbbr;
-- Fetch the row prior to the current row 
FETCH PRIOR FROM state_cursor
INTO @StateName, @StateAbbr;
SELECT @StateName, @StateAbbr;
-- Fetch the second row in the cursor 
FETCH ABSOLUTE 2 FROM state_cursor
INTO @StateName, @StateAbbr;
SELECT @StateName, @StateAbbr;
-- Fetch the row that is 2 rows after the current row 
FETCH RELATIVE 2 FROM state_cursor
INTO @StateName, @StateAbbr;
SELECT @StateName, @StateAbbr;
  
-- Fetch the row that is 3 rows prior to the current row
FETCH RELATIVE -3 FROM state_cursor
INTO @StateName, @StateAbbr;
SELECT @StateName, @StateAbbr;
-- Clean Up
CLOSE state_cursor;
DEALLOCATE state_cursor;

Listing 8: Scrolling around cursor with different FETCH options

When I run the code in Listing 8 I get the output in Report 6.

StateName                                          StateAbbr
-------------------------------------------------- ---------
Alaska                                             AK
Arizona                                            AZ
California                                         CA
Idaho                                              ID
                                                   
-------------------------------------------------- ----
Idaho                                              ID
                                                   
-------------------------------------------------- ----
California                                         CA
                                                   
-------------------------------------------------- ----
Arizona                                            AZ
                                                   
-------------------------------------------------- ----
Idaho                                              ID
                                                   
-------------------------------------------------- ----
Alaska                                             AK

Report 6: Results when running code in listing 8.

If you review the code in Listing 8 you can see I used the SCROLL option when defining my cursor. This allows me to use additional FETCH options for scrolling around my cursor. In the code in Listing 8 I used the following different FETCH options: LAST, PRIOR, ABSOLUTE, and RELATIVE. By looking at the output in Report 6 you can see how I jumped around in my cursor using these different FETCH options to display different StateName and StateAbbr values.

Performance Considerations and Best Practices

A cursor article wouldn't be complete unless it talked about performance and best practices around cursors. If your processing requirements can be accomplished with a set based query, then you should not be using a cursor. Cursors use additional CPU, memory and temporary space and are almost always slower than set based solutions. The actual number of times you have to use a cursor should be very small. If you think you need to use a cursor you might consider asking a more seasoned TSQL coder to review your requirements to see if there is a set based solution for your situation. But if you find you do need to process data using a row by row operation provided by a cursor to satisfy your requirements, you should make sure you consider the following:

• Use READ_ONLY cursors when possible. READ_ONLY cursors only apply shared locks on the underlying SQL Server tables thus causing less lock contention.
• When using a cursor to to update a few rows within a a large record set considering using the OPTIMISTIC option. Doing this option will cause less blocking contention because rows will not be locked for update when they are read into the cursor. Instead they will be locked when they are updated. One drawback to the optimistic locking is you might get an error if another process changes the underlying row before the cursor process performs an update.
• Make sure the record set declared by the cursor is as small as possible by using an appropriate WHERE clause. The smaller the cursor record set, the less resources used.
• When finished with your cursor remember to CLOSE and DEALLOCATE the cursor. By doing this you release the resources held by the cursor, and give them back to the system.

Summary

This article showed you some examples of how to use the DECLARE CURSOR to process through a set of rows. In the scope of this article I was not able to cover all the different options that are available on the DECLARE CURSOR statement, but I did cover those options most widely used. Hopefully next time you need to access rows one at a time you will be able to use the examples in this article as a jumpstart on writing your own cursor driven code.

Question and Answer

In this section you can review how well you have understood using a cursor by answering the following questions.

Question 1:

The DECLARE CURSOR statement is only used to step through a read-only set of records row by row. (TRUE or FALSE)?

1. TRUE
2. FALSE

Question 2:

Which DECLARE CURSOR statement will guarantee that records will be locked for update when they are read into the cursor?

1. DECLARE MyCursor CURSOR FOR …
2. DECLARE MyCursor CURSOR SCROLL_LOCKS FOR …
3. DECLARE MyCursor CURSOR OPTIMISTIC FOR …
4. DECLARE MyCursor CURSOR READ-ONLY FOR …

Question 3:

If you want to make your cursor only available to a single batch which scope should you declare?

1. LOCAL
2. GLOBAL

Answers:

Question 1:

The correct answer is b. While it is true that the DECLARE CURSOR can be used to step through a read-only set of records row by row, it can also be used to update rows.

Question 2:

The correct answer is b. When the SCROLL_LOCKS option is used rows are locked when they are read into that cursor. Using this option guarantees that the row will be locked for update, and will make sure other connections can't change the underlying cursor row while the cursor is open.

Question 3:

The correct answer is a. LOCAL cursors are available only to the batch in which they were defined. A GLOBAL cursor is available in all batches created by the connection that declares the cursor.