Oracle always requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In SQL Server, a SELECT statement that is not disclosed on a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.
SQL Server provides two interfaces for cursor functions. When using cursors in Transact-SQL batches or stored procedures, ANSI-standard SQL syntax has been added for declaring, opening, and fetching from cursors as well as positioned updates and deletes. When using cursors from a DB-Library or ODBC program, the SQL Server client libraries transparently call built-in server functions to handle cursors more efficiently.
When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to do the same function in Transact-SQL. If the cursor is used just to return a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to a return default result set. If the cursor is used to load data a row at a time into local procedure variables, then you must use cursors in Transact-SQL.
The following table shows the syntax for using cursors.
| Operation | PL/SQL | Transact-SQL |
| Declaring a cursor | CURSOR cursor_name [(cursor_parameter(s))] IS select_statement; |
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_list]}] |
| Opening a cursor | OPEN cursor_name [(cursor_parameter(s))]; | OPEN cursor_name |
| Fetching from cursor | FETCH cursor_name INTO variable(s) | FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM] cursor_name [INTO @variable(s)] |
| Operation | PL/SQL | Transact-SQL |
| Update fetched row | UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name; |
UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name |
| Delete fetched row | DELETE FROM table_name WHERE CURRENT OF cursor_name; |
DELETE FROM table_name WHERE CURRENT OF cursor_name |
| Closing cursor | CLOSE cursor_name; | CLOSE cursor_name |
| Remove cursor data structures | N/A | DEALLOCATE cursor_name |
The Transact-SQL DECLARE CURSOR statement is more robust than the PL/SQL statement, and has many additional capabilities. Although it does not support the use of cursor arguments, it can include local variables. The values of these local variables are used in the cursor at open time.
The INSENSITIVE option is used to define a cursor that makes a temporary copy of the data to be used by the cursor. All of the requests to the cursor are answered from this temporary table. Consequently, modifications made to base tables are not reflected in the data returned by fetches made to this cursor. Data accessed by this type of cursor cannot be modified.
The cursor automatically becomes an insensitive cursor if any of the following conditions exist:
The SCROLL option allows backward, absolute, and relative fetches in addition to forward fetches. A scroll cursor uses a keyset cursor model in which committed deletes and updates made to the underlying tables by any user are reflected in subsequent fetches. This is only true if the cursor is not declared with the INSENSITIVE option.
If the READ ONLY option is chosen, updates are prevented from occurring against any row within the cursor. This option overrides the default capability of a cursor to be updated.
The UPDATE [OF column_list] statement is used to define updatable columns within the cursor. If [OF column_list] is supplied, only the columns listed will allow modifications. If no list is supplied, all of the columns can be updated unless the cursor has been defined as READ ONLY.
It is important to note that the name scope for a SQL Server cursor is the connection itself. This is different from the name scope of a local variable. A second cursor with the same name as an existing cursor on the same user connection cannot be declared until the first cursor is deallocated.
Transact-SQL does not support the passing of arguments to a cursor when it is opened, unlike PL/SQL. When a Transact-SQL cursor is opened, the result set membership and ordering are fixed. Updates and deletes that have been committed against the base tables of the cursor by other users are reflected in fetches made against all cursors defined without the INSENSITIVE option. In the case of an INSENSITIVE cursor, a temporary table is generated.
Oracle cursors can only move in a forward direction. There is no backward or relative scrolling capability. SQL Server cursors can scroll forward and backwards using the fetch options shown in the following table. These fetch options can be used only when the cursor is declared using the SCROLL option.
| Scroll option | Description |
| NEXT | Returns the first row of the result set if this is the first fetch against the cursor; otherwise, it moves the cursor one row within the result set. NEXT is the primary method used to move through a result set. NEXT is the default cursor fetch. |
| PRIOR | Returns the previous row within the result set. |
| FIRST | Moves the cursor to the first row within the result set and returns the first row. |
| LAST | Moves the cursor to the last row within the result set and returns the last row. |
| ABSOLUTE n | Returns the nth row within the result set. If n is a negative value, the returned row is the nth row counting backward from the last row of the result set. |
| RELATIVE n | Returns the nth row after the currently fetched row. If n is a negative value, the returned row is the nth row counting backward from the relative position of the cursor. |
The Transact-SQL FETCH statement does not require the INTO clause. If return variables are not specified, the row is automatically returned to the client as a single-row result set. However, if your procedure just needs to get the rows to the client, a noncursor SELECT statement is much more efficient.
The INTO option fetches cursor rows into local procedure variables for further processing. If this option is used, each of the variables must match the returned column data type. Errors occur when the data types are incompatible. Unlike Oracle, implicit data type conversions are not provided here.
The @@fetch_status global variable is updated following each FETCH. It is similar in use to the CURSOR_NAME%FOUND and CURSOR_NAME%NOTFOUND variables used with PL/SQL. The @@fetch_status variable is set to the value of 0 following a successful FETCH. If the FETCH tries to read beyond the end of the cursor, a value of –1 is returned. If the requested row has been deleted from the table after the cursor was opened, the @@fetch_status variable returns –2. The value of –2 usually only occurs in a cursor declared with the SCROLL option. This variable must be checked following each fetch to ensure the validity of the data.
SQL Server does not support Oracle's cursor FOR loop syntax.
The syntax for updates and deletes using the CURRENT OF clause is the same in both PL/SQL and Transact-SQL. This clause works the same way regardless of DBMS. A positioned UPDATE or DELETE is performed against the current row within the specified cursor.
The Transact-SQL CLOSE CURSOR statement closes the cursor but leaves the data structures accessible for reopening. The PL/SQL CLOSE CURSOR statement closes and releases all data structures.
Transact-SQL requires the use of the DEALLOCATE CURSOR statement to remove the cursor data structures. The DEALLOCATE CURSOR statement is different from CLOSE CURSOR in that a closed cursor can be reopened. The DEALLOCATE CURSOR statement releases all data structures associated with the cursor and removes the definition of the cursor.
The example compares the cursor statements required to achieve the same program result in both PL/SQL and Transact-SQL:
| Oracle | SQL Server |
| DECLARE VSSN CHAR(9); VFNAME VARCHAR(12); VLNAME VARCHAR(20); CURSOR CUR1 IS SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY LNAME; BEGIN OPEN CUR1; FETCH CUR1 INTO VSSN, VFNAME, VLNAME; WHILE (CUR1%FOUND) LOOP FETCH CUR1 INTO VSSN, VFNAME, VLNAME; END LOOP; CLOSE CUR1; END; |
DECLARE @VSSN CHAR(9), @VFNAME VARCHAR(12), @VLNAME VARCHAR(20) DECLARE curl CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN OPEN CUR1 FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME WHILE (@@FETCH_STATUS <> -1) BEGIN FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME END CLOSE CUR1 DEALLOCATE CUR1 |