sql_mode=ORACLE: Explicit cursor FOR LOOP

Add support for explicit cursor FOR loops when running in sql_mode=ORACLE:

FOR rec IN cur

LOOP

  -- statements

END LOOP;

The benefits of this syntax is:

• No declaration of the FOR variable rec is needed. The cursor FOR statement implicitly declares a record variable (i.e. a ROW type variable}} that has the same structure with the data returned by the cursor.
• No needs to do OPEN or CLOSE for the cursor. It's automatically opened before the loop, and is closed after the loop.
• No needs to do CLOSE from inside the FOR loop if one uses a GOTO statement from inside the loop. The cursor is closed automatically.
• No needs to do an explicit FETCH. The next row is automatically fetched in the beginning of every iteration.
• No needs to do an explicit EXIT to leave the loop when FETCH returned no data.

Example:

DECLARE

  CURSOR c1 IS

    SELECT last_name, job_id FROM employees

    WHERE job_id LIKE '%CLERK%' AND manager_id > 120

    ORDER BY last_name;

BEGIN

  FOR item IN c1

  LOOP

    DBMS_OUTPUT.PUT_LINE

      ('Name = ' || item.last_name || ', Job = ' || item.job_id);

  END LOOP;

END;

/

The fields of the implicitly declared record variable must be updatable:

DECLARE

  CURSOR cur IS

    SELECT 'Black' AS last_name, 10 AS job_id FROM DUAL UNION

    SELECT 'White' AS last_name, 20 AS job_id FROM DUAL;

BEGIN

  FOR rec IN cur

  LOOP

    rec.job_id:= rec.job_id + 100;

    DBMS_OUTPUT.PUT_LINE ('Name=' || rec.last_name || ', Job=' || rec.job_id);

  END LOOP;

END;

/

Internally, the explicit cursor FOR LOOP will be transparently translated to:

• OPEN
• Declaration of a %ROWTYPE variable
• Simple LOOP with EXIT
• CLOSE

So the block in the previous example will be effectively equal to:

DECLARE

  CURSOR cur IS

    SELECT 'Black' AS last_name, 10 AS job_id FROM DUAL UNION

    SELECT 'White' AS last_name, 20 AS job_id FROM DUAL;

BEGIN

  OPEN cur;

  DECLARE

    rec cur%ROWTYPE;

  BEGIN

    LOOP

      FETCH cur INTO rec;

      EXIT WHEN cur%NOTFOUND;

      rec.job_id:= rec.job_id + 100;

      DBMS_OUTPUT.PUT_LINE ('Name=' || rec.last_name || ', Job=' || rec.job_id);

    END LOOP;

    CLOSE cur;

  END;

END;

/