Cursor FOR Loop v11
In the cursor examples presented so far, the programming logic required to process the result set of a cursor included 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 finally 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. There must be at least one statement.
The following example shows the example from %NOTFOUND, 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 output below.
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