SQL SERVER 2008 – Cursor Process

Transact-SQL Cursors follows a general process that is used with all SQL Server cursors:

  1. Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
  2. Execute the Transact-SQL statement to populate the cursor.
  3. Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
  4. Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
  5. Close the Cursor

The Cursor Process has the following steps involved in it:

  • Declare a Cursor
  • Open a Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • De-allocate the Cursor

Declaring a Cursor:

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.

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 ] ] ]

LOCAL: Specifies that the scope of the cursor is local to the program in which the cursor was created.

GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any program by the connection. The cursor is only implicitly deallocated at disconnect.

If neither GLOBAL nor LOCAL is specified, the default is taken as GLOBAL.

FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL.

SCROLL: Specifies that the cursor can scroll from first to the last row as well as last to first row also. It Supports 6 fetch methods like FETCH NEXT, FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE n and FETCH RELATIVE n.

STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor or committed by other users, are visible as we scroll around the cursor. Inserts made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row.

DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch.

FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

READ_ONLY: Prevents updates made through this cursor. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated in the table since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

FOR UPDATE [OF column name [,n]]: Defines updatable columns within the cursor. If OF column_name [,n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Opening a Cursor: Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR.

Syntax: OPEN <cursor_name>

Fetching data from the Cursor: Retrieves a specific row from a Transact-SQL server cursor into specified variables.

Syntax:

FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]

FROM <cursor_name> INTO @variable_name [ ,…n ]

NEXT: Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.

PRIOR: Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.

FIRST: Returns the first row in the cursor and makes it the current row.

LAST: Returns the last row in the cursor and makes it the current row.

ABSOLUTE  n: If n is positive, it returns the specified nth row from the front of the cursor. If n is negative, it returns the specified nth row from the back of the cursor.

RELATIVE  n: If n is positive, returns the row n rows beyond the current row. If n is negative, returns the row n rows prior to the current row.

If any of the used fetch statement is successful it returns the status of it which will be stored in a implicit variable @@FETCH_STATUS (this does not requires to be declared) which can be any of the following values:

0         –           The FETCH statement was successful

-1         –           The FETCH statement failed or the row was beyond the result set

-2         –           The row fetched is missing

Closing a Cursor:

Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE is not allowed on cursors that have only been declared or are already closed.

Syntax: Close <cursor_name>

Deallocating a Cursor: Removes a cursor reference. When the last cursor reference is deallocated, SQL Server releases the data structures comprising the cursor.

Syntax: Deallocate <cursor_name>

Using a Simple Cursor:

DECLARE EMPCUR CURSOR FOR SELECT ENAME, SAL FROM EMP

DECLARE @ENAME VARCHAR(50), @SAL MONEY

OPEN EMPCUR

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL

WHILE @@FETCH_STATUS=0

BEGIN

PRINT ‘SALARY OF ‘ + @ENAME + ‘ IS ‘ + CAST(@SAL AS VARCHAR)

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL

END

CLOSE EMPCUR

DEALLOCATE EMPCUR

Using a Cursor to Update all the rows of the Table:

This program will explain you how we can update all the rows of the table basing on some conditions, similar to the program we have written before discussing cursors but there only a single row has been modified.

DECLARE EMPCUR CURSOR FOR SELECT EMPNO, JOB FROM EMP

DECLARE @EMPNO INT, @JOB VARCHAR(50)

OPEN EMPCUR

FETCH NEXT FROM EMPCUR INTO @EMPNO, @JOB

WHILE @@FETCH_STATUS=0

BEGIN

IF @JOB=’PRESIDENT’

UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE EMPNO=@EMPNO

ELSE IF @JOB=’MANAGER’

UPDATE EMP SET SAL = SAL + SAL * 0.08 WHERE EMPNO=@EMPNO

ELSE IF @JOB=’ANALYST’

UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE EMPNO=@EMPNO

ELSE

UPDATE EMP SET SAL = SAL + SAL * 0.05 WHERE EMPNO=@EMPNO

FETCH NEXT FROM EMPCUR INTO @EMPNO, @JOB

END

CLOSE EMPCUR

DEALLOCATE EMPCUR

Using a Global Cursor:

Program 1:

DECLARE EMPCUR CURSOR GLOBAL

FOR SELECT ENAME, SAL, COMM FROM EMP

DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY, @TOTSAL MONEY

OPEN EMPCUR

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM

WHILE @@FETCH_STATUS=0

BEGIN

SET @TOTSAL=@SAL + ISNULL(@COMM, 0)

PRINT @ENAME + ‘ EARNS ‘ + CAST(@TOTSAL AS VARCHAR) + ‘ EVERY MONTH’

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM

END

CLOSE EMPCUR

-In the above case because it was a Global cursor we are not using any Deallocate Cursor statement, now we use the same cursor in other programs with of declaring it as following:

Program 2:

DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY, @ANNSAL MONEY

OPEN EMPCUR

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM

WHILE @@FETCH_STATUS=0

BEGIN

SET @ANNSAL=(@SAL + ISNULL(@COMM, 0)) * 12

PRINT @ENAME + ‘ EARNS ‘ + CAST(@ANNSAL AS VARCHAR) + ‘ EVERY YEAR’

FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM

END

CLOSE EMPCUR

-We don’t require to Deallocate the Cursor any where it gets deallocated when we close the connection.

Using a Static Cursor:

DECLARE EMPCUR CURSOR STATIC

FOR SELECT SAL FROM EMP WHERE EMPNO=1005

DECLARE @SAL MONEY

OPEN EMPCUR

UPDATE EMP SET SAL=6000 WHERE EMPNO=1005

FETCH NEXT FROM EMPCUR INTO @SAL

PRINT @SAL

CLOSE EMPCUR

DEALLOCATE EMPCUR

-In this case after opening the cursor we have performed an update of Sal on the EMP table but still the cursor contains the old value but not the new value, so it prints the old Salary value only.

Using a Dynamic Cursor:

DECLARE EMPCUR CURSOR DYNAMIC

FOR SELECT SAL FROM EMP WHERE EMPNO=1005

DECLARE @SAL MONEY

OPEN EMPCUR

UPDATE EMP SET SAL=4000 WHERE EMPNO=1005

FETCH NEXT FROM EMPCUR INTO @SAL

PRINT @SAL

CLOSE EMPCUR

DEALLOCATE EMPCUR

-In this case after opening the cursor we have performed an update of Sal on the EMP table but the cursor contains the new value but not the new value, so it prints the new Salary value only.

Using Scroll Cursor:

DECLARE EMPCUR CURSOR

SCROLL

FOR SELECT EMPNO FROM EMP

DECLARE @EMPNO INT

OPEN EMPCUR

FETCH NEXT FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH LAST FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH PRIOR FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH FIRST FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH ABSOLUTE 12 FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH ABSOLUTE -10 FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH RELATIVE 3 FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

FETCH RELATIVE -5 FROM EMPCUR INTO @EMPNO

PRINT @EMPNO

CLOSE EMPCUR

DEALLOCATE EMPCUR

-As we have declared the cursor as scroll all the Fetch methods can be used on it.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

11 thoughts on “SQL SERVER 2008 – Cursor Process

  1. Hi Dilip,

    Can you please explain me the following statement.

    In KEYSET topic, “committed by other users, are visible as we scroll around the cursor. Inserts made by other users are not visible. “.

    Regards
    Siva Kumar J

  2. Hi Jena Babu,
    This is very usefull references thay you have posted. i am a PL/SQL developer but working in SQL server for first time. i only knew the cursor in SQL server as general. But for depth knowledge in SQL Server , i was searching to know more about Cursors in SQL Server. This will help me a lot.

    Thanks,
    Manas

  3. hi Dilip,
    your post is excellent for us.if you have some example related to followling cursor–

    1-using Forward-only with keyset
    2-using forward_only with read-only
    3-using forward_only with SCROLL_LOCKS
    4-using forward_only with OPTIMISTIC

    1-using scroll with keyset
    2-using scroll with read-only
    3-using scroll with SCROLL_LOCKS
    4-using scroll with OPTIMISTIC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s