Tuesday, 13 March 2012

SQl Cursor

What is a Cursor?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor, we need to perform some steps in the following order:
  • Declare cursor
  • Open cursor
  • Fetch row from the cursor
  • Process fetched row
  • Close cursor
  • Deallocate cursor

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
@getAccountID CURSOR
@getAccountID = CURSOR FOR
@getAccountIDDEALLOCATE @getAccountID

Cursor Process
The process of creating, using and releasing cursors follow this pattern:
DECLARE: associates the cursor with a record set and defines the cursor’s parameters
OPEN: executes the SELECT statement to populate the cursor
FETCH: retrieves a row from the cursor
UPDATE/DELETE/etc.: manipulates/processes the data
CLOSE: closes the cursor, but leaves the underlying structure in place to be re-opened if needed
DEALLOCATE: releases database and system resources and any locks on records
Cursor Declarations
Cursor declarations are complex statements with multiple arguments defining, not only the data contained in the cursor, but also how it can be accessed, if it can be changed, and how it will behave.
DECLARE cursor_name CURSOR
           [LOCAL | GLOBAL]
                       [FORWARD_ONLY | SCROLL]
                                   [STATIC | DYNAMIC | FAST_FORWARD | KEYSET]
                                               [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
                                                                       FOR SELECT parameters
                                                                                   [FOR UPDATE [OF column_name [,…n]]]
DECLAR cursor_name: creates and names the cursor
CURSOR: dedicated data type

No comments:

Post a Comment