Cursor FOR Loops

The cursor FOR loop provides an elegant, simple syntax to to iterate over a result set. To underscore the advantages of cursor FOR loops, consider the following PL/SQL block which uses a basic loop.

 SET SERVEROUTPUT ON
DECLARE
  -- EMP_CURSOR will retrieve all columns and all rows from the EMP table
  CURSOR emp_cursor IS
    SELECT *
      FROM emp;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    --Advance the pointer in the result set, assign row values to EMP_RECORD
    FETCH emp_cursor INTO emp_record;
    --Test to see if no more results  
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' [' ||emp_record.empno||']');
  END LOOP;
  CLOSE emp_cursor;
END;
/

Now examine the same query using a cursor FOR loop:

DECLARE
  -- EMP_CURSOR will retrieve all columns and all rows from the EMP table
  CURSOR emp_cursor IS
    SELECT *
      FROM emp;
BEGIN
  FOR emp_record IN emp_cursor LOOP     
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
  END LOOP;
END;
/

You can use a cursor for loop without a declared cursor by including a query in the FOR statement.  This can enable very compact code.

BEGIN
  FOR emp_record IN (SELECT * FROM emp) LOOP     
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ['||emp_record.empno||']');
  END LOOP;
END;
/

While you can use EXIT statement within a FOR cursor loop, you should not use a cursor FOR loop if you may need to exit the LOOP prematurely.  Use a basic or WHILE loop instead.