To work with a cursor, you take the following steps:
- Declare the cursor using the DECLARE statement. You choose a cursor based on how sensitive you want it to be to the changes of others and on the level of scrolling you want. Then you specify the SELECT statement that will produce the cursor's result set. Often, the rows in the result set will be explicitly sorted using the ORDER BY clause. Most database systems that provide cursors allow you to move only forward through the result set. SQL Server's scrollable cursors allow you to reposition the cursor to any row. They are also fully updatable.
- Open the cursor. You can think of this step as executing the DECLARE statement from step 1.
- Fetch rows in the cursor. The cursor position moves within the result set to get the next row or the previous row, to go back five rows, to get the last row, and so on. Typically, the FETCH statement is executed many timesat least once for every row in the result set. (The other cursor control statements tend to be executed only once per cursor.) In SQL Server, you typically check the system function @@FETCH_STATUS after each fetch. A 0 value indicates that the fetch was successful. A _1 value indicates that there are no more rowsthat is, the fetch would move the cursor beyond the result set, either past the last row or before the first row. A value of _2 indicates that the row no longer exists in the cursorit was deleted from the base table after the cursor was opened or it was updated in a way that no longer meets the criteria of the SELECT statement that generated the result set.
- Update or delete the row of the table in which the cursor is positioned. This step is optional. Here's the syntax:
UPDATE table SET column = value WHERE CURRENT OF cursor_name DELETE table WHERE CURRENT OF cursor_name
After the cursor is positioned at a specific row, that row is usually updated or deleted. Instead of supplying a WHERE clause that identifies a row in the table using values such as the primary key for the UPDATE or DELETE statement, you identify the row to be operated on by specifying CURRENT, which is the row the cursor points to. This type of operation is known as a positioned update (or a positioned delete ).
- Close the cursor. This ends the active cursor operation. The cursor is still declared, so you can reopen it without having to declare it again.
- Deallocate the cursor to remove a cursor reference. When the last cursor reference is deallocated, SQL Server releases the data structures comprising the cursor. (We'll see later in this chapter that you can have multiple references to the same cursor by using cursor variables .) Because internal data structures consume memory in the procedure cache, you should use the DEALLOCATE statement to clean up after you're done with the cursor. Think of DEALLOCATE as essentially "UNDECLARE"the opposite of DECLARE, just as CLOSE is the opposite of OPEN. Below is an example of a simple cursor that fetches forward through the authors table and retrieves rows one by one. (For now, note that this is partly pseudocodeand highly inefficientbecause we don't want to get immersed in the details quite yet.)
DECLARE au_cursor CURSOR FOR SELECT * FROM authors ORDER by au_lname OPEN au_cursor WHILE (more rows) FETCH NEXT FROM au_cursor -- Do until end CLOSE au_cursor DEALLOCATE au_cursor
Of course, to get all the rows from the authors table, it would be a lot easier to issue the SELECT statement directly, which would return all the rows in one set. Using a cursor to return a simple result set is inappropriate, and this example served only to illustrate the basic flow of cursor operations. We'll talk about uses for cursors next.