Cursor

Summary

The video is a database systems lecture introducing SQL cursors, their structure and fetch options, and examples of using them in SQL Server.

What a Cursor Does

• A cursor is a database object that lets a program access and work with rows returned by a query one row at a time.
• The query output is a result set containing rows that meet the query’s criteria, such as a WHERE condition.
• The cursor acts like a pointer into the result set. Fetching moves its position from one row to another.
• A cursor can be used to display or modify selected rows, though the lecture recommends avoiding cursors when other approaches will work.

Cursor Lifecycle

The lecture emphasizes this sequence:

1. Declare the cursor and specify the query whose result set it will process.
2. Open the cursor.
3. Fetch a row.
4. Process the row as needed—for example, display it or update it.
5. Repeat fetching and processing until there are no more rows.
6. Close the cursor.
7. Deallocate it to release it.

In the looping example, FETCH_STATUS = 0 indicates that a fetch succeeded. The loop continues fetching and processing rows while that condition holds, then exits when no more rows can be fetched.

Fetching and Cursor Movement

• A forward-only cursor moves through rows in the forward direction, typically using FETCH NEXT.
• A scrollable cursor can move to other positions in the result set. The lecture demonstrates:
  • NEXT: Move to the next row.
  • FIRST: Move to the first row.
  • LAST: Move to the last row.
  • PRIOR: Move to the previous row.
  • ABSOLUTE n: Move directly to row n in the result set.
  • RELATIVE n: Move n rows from the cursor’s current position; a negative value moves backward.

The instructor demonstrates that backward or arbitrary-position fetches with a forward-only cursor produce an error. A scrollable cursor is needed for those movements.

Scrollability and Sensitivity

The lecture describes two cursor properties:

• Scrollability determines which directions and positions can be reached. The choices discussed are forward-only and scrollable.
• Sensitivity determines whether changes to the underlying table are reflected in the result set while it is being processed.
  • An insensitive cursor is described as not showing a later table change in its result set.
  • A sensitive cursor is described as showing the change.

The instructor demonstrates this distinction by changing a product name after opening a cursor, then checking whether the changed name appears when fetching rows.

Exact cursor behavior can depend on cursor options, but these are the distinctions presented in the lecture.

Examples and Practical Advice

• The first example selects product IDs and names from the products table where categoryID = 2, producing 12 rows for the demonstration.
• The instructor first fetches through the result set with a forward-only cursor, then demonstrates navigation with a scrollable cursor.
• Later examples demonstrate insensitive and sensitive cursor behavior when a product record is updated.
• Students are encouraged to run the examples themselves and practice writing variations.

Performance Guidance

The instructor advises using cursors sparingly because they can be costly and may reduce performance. Alternatives mentioned include loops, temporary tables, subqueries, and other query approaches.

When a cursor is necessary, the recommendations are to:

• Close and deallocate it after use.
• Limit the rows it processes with an appropriate WHERE clause.
• Select only the columns needed rather than using SELECT *.
• Prefer a read-only cursor when updates are unnecessary.
• Avoid insensitive cursors where possible, as advised in the lecture.

Speakers and Sources

• One instructor/lecturer, addressing students in the D3TI and D4 RPL study programs and demonstrating SQL Server cursor examples.
• No other speakers or external sources are featured in the subtitles.