MIS 431

1
MIS 431

• Chapter 12.
• Row-Oriented ProcessingUsing Cursors

2
Types of Processing

• Set oriented
• Use a single command to process the entire result
  set
• More efficient
• Requires more programmer sophistication
• Row-by-row
• Use some logical method to move through the
  result set processing one record at a time
• Less efficient
• Easier to program
• Necessary for complex operations

3
Types of Cursors

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

4
Forward-Only Cursors

• Retrieve results from beginning to end
• Also available FAST_FORWARD

5
Static Cursors

• Complete result set build in TempDB
• Changes (after opening) not reflected in result
  set
• Slower start
• Fast navigation once started

6
Dynamic Cursors

• Result set recreated with each FETCH
• Opens faster
• Slower navigation
• Changes to data will be reflected

7
Keyset-Driven Cursors

• TempDB contains a list of unique values
• Changes to rows not reflected
• Changes to columns are reflected

8
Using Cursors

• DECLARE statement
• Specifies type of cursor
• Query that defines the data
• OPEN statement
• Executes statement to populate (depending on
  type)
• FETCH statement
• Moves to a row and reads data
• CLOSE statement
• Closes cursor but leaves it available
• DEALLOCATE statement
• Drops the cursor

9
Declaring and Opening Cursors

• DECLARE cursor_name CURSORGLOBAL or LOCAL
  FORWARD_ONLY or SCROLLSTATIC, DYNAMIC,
  KEYSET, or FAST_FORWARDREAD_ONLY,
  SCROLL_LOCKS, or OPTIMISTICFOR select_statement
• OPEN cursor_name

10
Fetching Rows

• FETCH FROM cursor_name
• FETCH NEXT FROM cursor_name
• FETCH PRIOR FROM cursor_name
• FETCH FIRST FROM cursor_name
• FETCH LAST FROM cursor_name
• FETCH ABSOLUTE n FROM cursor_name
• FETCH RELATIVE n FROM cursor_name
• _at__at_FETCH_STATUS function to determine success
• FETCH FROM cursor_name INTO _at_variable

11
Cursor Variables

• CURSOR is a data type
• DECLARE _at_variable
• SET _at_variable CURSOR optionsFOR
  select_statement
• OPEN _at_variable
• FETCH NEXT FROM _at_variable
• CLOSE _at_variable
• DEALLOCATE _at_variable