SQL Cursor

Cursor was created for processing one record on a time.

Before you use a CURSOR, you’ll need to declare it.  The basic form looks like:

DECLARE cursorName CURSOR for
 SELECT field1, field2 FROM myTable
 WHERE whereClause

Once we have the cursor declared, we need to OPEN it to cause it to execute:

OPEN cursorName

The rest is going to look very similar to what we did back when we were using ODBC.  To move to the first record, we have to FETCH the row:

FETCH NEXT FROM cursorName
 INTO @field1, @field2

There is a special variable in SQL called @@FETCH_STATUS that will be zero (0) as long as the fetch was able to retrieve a row, so you’ll want to set up a while loop after your first fetch that checks the status and then processes the data.  Right before the end of the while loop you will issue another FETCH.

(SQL Server) FETCH NEXT FROM cursorName
 INTO @field1, @field2
 While @@FETCH_STATUS = 0
 Begin
  /* do something with the data here */
  FETCH NEXT FROM cursorName
   INTO @field1, @field2
 End 

Syntax DB2:

DECLARE cursor-name INSENSITIVE (DYNAMIC)SCROLL CURSOR WITH HOLD FOR Select Statement FOR UPDATE OF FOR READ ONLY 
Insensitive - Once a cursor is opened it does not have any sensitivity to inserts, updates or adds performed by this or any other activation group.
Scroll - The cursor is scrollable, you can get rows in reverse order and reposition to where your next read will occur.
Dynamic Scroll - The cursor is update-able.
With Hold - Prevents the cursor from being closed as a consequence of a commit operation. 
 (when join multiple tables, it will be read only by default.)

FETCH Next FROM cursor-name (single fetch) INTO Host Variable/Structure
      Prior (multiple row fetch) FOR Host Variable or Integer ROWS INTO Host Structure Array 
 First 
 Last
 Before
 After
 Current
 Relative - host variable or integer
Advertisements

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