Oracle

Sql Server

The PL/SQL CLOSE CURSOR statement closes and releases all data structures.

The Transact-SQL CLOSE CURSOR statement closes the cursor but leaves the data structures accessible for reopening.

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.

Oracle cursors can move in a forward direction only–there is no backward or relative scrolling capability.

SQL Server cursors can scroll forward and backward with the fetch options shown in the following table. These fetch options can be used only when the cursor is declared with the SCROLL option.

Oracle always requires that cursors be used with SELECT statements, regardless of the number of rows requested from the database.

In Microsoft SQL Server, a SELECT statement that is not enclosed within a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.

Oracle supports the passing of arguments to a cursor when it is opened.

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

Microsoft 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