Oracle requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In Microsoft® 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, SQL-92 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 only 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, you must use cursors in Transact-SQL.
The following table shows the syntax for using cursors.
| Operation | Oracle | SQL Server |
|---|---|---|
| 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)] |
| 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 |