Cursor Example

The example below shows equivalent cursor statements in PL/SQL and Transact-SQL.

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