In the cursor examples presented so far, the programming logic required to process the result set of a cursor includes a statement to open the cursor, a loop construct to retrieve each row of the result set, a test for the end of the result set, and a statement to close the cursor. The cursor FOR loop is a loop construct that eliminates the need to individually code the statements just listed.
The cursor FOR loop opens a previously declared cursor, fetches all rows in the cursor result set, and then closes the cursor.
The syntax for creating a cursor FOR loop is as follows:
FOR record IN cursor
LOOP
statements
END LOOP;
record is an identifier assigned to an implicitly declared record with definition cursor%ROWTYPE. cursor is the name of a previously declared cursor. statements are one or more SPL statements. At least one statement must exist.
The following example shows the example from %NOTFOUND that is modified to use a cursor FOR loop:
CREATE OR REPLACE PROCEDURE cursor_example
IS
CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
FOR v_emp_rec IN emp_cur_1 LOOP
DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
END LOOP;
END;
The same results are achieved as shown in the following output:
EXEC cursor_example;
EMPNO ENAME
----- -------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER