Pages

Men

rh

10/18/2013

Cursors in SQL Server

Cursors:
 
Cursor is  a database object used by applications to manipulate data in a set on a row-by-row basis.

Syntax:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
     FOR select_statement
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]


Example:

DECLARE CUR_ERRORDETAILS CURSOR FOR
      SELECT JOBNO,NOOFITEMS,QTY FROM  dbo.ERRDETAILS
OPEN CUR_ERRORDETAILS

FETCH NEXT FROM  CUR_ERRORDETAILS INTO  @JOBNO,@NOOFITEMS,@QTY

WHILE @@FETCH_STATUS =0
      BEGIN
       
             PRINT @JOBNO
             PRINT @NOOFITEMS
             PRINT @QTY
             PRINT '..........................'
      FETCH NEXT FROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY
      END  
CLOSE CUR_ERRORDETAILS
DEALLOCATE CUR_ERRORDETAILS


Cursor Types


  • Static cursors
  • Dynamic cursors
  • Forward-only cursors
  • Keyset-driven cursors

 

Static Cursors:

The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened.

The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set.

A static cursor does not display new rows inserted in the database after the cursor was opened.No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.

Microsoft® SQL Server™ static cursors are always read-only.

Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.

Dynamic Cursors

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause.

Forward – Only Cursors:
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor, except for the case where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index.

Keyset-driven Cursors

The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened. Inserts made through the cursor using an API function such as the ODBC SQLSetPos function are visible at the end of the cursor. @@FETCH_STATUS returns a "row missing" status when an attempt is made to fetch a row deleted after the cursor was opened. 

An update to a key column operates like a delete of the old key value followed by an insert of the new key value. The new key value is not visible if the update was not made through the cursor; it is visible at the end of the cursor if the update was made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause and the SELECT statement did not contain a JOIN condition in the FROM clause.

CREATE PROCEDURE UDP_UPDATE_ORDERMASTER
       
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
      DECLARE @JOBNO INT
      DECLARE @NOOFITEMS INT
      DECLARE @QTY BIGINT

      DECLARE CUR_ERRORDETAILS CURSOR FOR
            SELECT JOBNO,NOOFITEMS,QTY FROM  dbo.ERRDETAILS
      OPEN CUR_ERRORDETAILS

      FETCH NEXT FROM  CUR_ERRORDETAILS INTO  @JOBNO,@NOOFITEMS,@QTY

      WHILE @@FETCH_STATUS =0
      BEGIN
       --PRINT @JOBNO
       --PRINT @NOOFITEMS
       --PRINT @QTY
       --PRINT '..........................'
       UPDATE  dbo.SERVICEMASTER SET AMOUNT = 0 WHERE JOBNO = @JOBNO
       UPDATE  dbo.SERVICEMASTER
      SET
AMOUNT = (@NOOFITEMS *@QTY/100)+12
            WHERE JOBNO= @JOBNO

FETCH NEXT FROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY

END  
      CLOSE CUR_ERRORDETAILS
      DEALLOCATE CUR_ERRORDETAILS
     
      SELECT * FROM SERVICEMASTER

 
END
GO
EXEC UDP_UPDATE_ORDERMASTER


Advantages:
  • Cursors allow row-by-row prcessing of the resultsets.

Disadvantages of cursors:

  • Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors


No comments :

Post a Comment