Cursors

Owned by Computer Science Department
Last updated: Aug 05, 2023
2 min read

A cursor in SQL is a database object used to retrieve rows from a result set one at a time, instead of the whole set at once. It allows you to control and manage rows (records) from a database that has been output from a query, acting somewhat like a pointer to this data.

Cursors are primarily used when you need to update records in a database table in a singleton manner or in a row by row manner, in other words, when you need to manipulate rows individually.

The process to use a cursor typically follows these steps:

  1. Declare: A cursor is declared by defining the SQL statement.

  2. Open: Once the cursor is declared, it can be opened to hold the rows returned by the SQL statement. Opening a cursor executes the SQL statement and identifies the result set, but doesn't retrieve any rows.

  3. Fetch: Once the cursor is opened, rows can be fetched one by one or in a block to do data manipulation. The rows are retrieved in the order defined by the SQL query.

  4. Close: After all the necessary rows are fetched, the cursor is closed.

Here is an example in SQL:

DECLARE @MyVariable INT DECLARE MyCursor CURSOR FOR SELECT MyColumn FROM MyTable OPEN MyCursor FETCH NEXT FROM MyCursor INTO @MyVariable WHILE @@FETCH_STATUS = 0 BEGIN PRINT @MyVariable FETCH NEXT FROM MyCursor INTO @MyVariable END CLOSE MyCursor DEALLOCATE MyCursor

This script declares a cursor named MyCursor that retrieves the values from MyColumn in MyTable. The FETCH NEXT command gets the next value from MyCursor and puts it into the @MyVariable variable. If the fetch is successful, @@FETCH_STATUS will be 0, and the value of @MyVariable is printed. This process is repeated until all rows have been retrieved. After all the rows are fetched, the cursor is closed and deallocated.

Although cursors can be useful, they can also be resource-intensive and can decrease performance due to their set-by-step nature, as opposed to the set-based nature of SQL. Therefore, it's generally advised to use cursors sparingly and to look for set-based alternatives when possible.