Cursor FOR Loop
The Cursor FOR Loop implicitly declares its loop index as a record of
type %ROWTYPE, opens a cursor, repeatedly fetches rows of the values
from the active set into fields in the record, then closes the cursor when
all rows have been processed or when the EXIT command is
encountered.
The syntax for the FOR Loop is:
FOR <variable name> IN <cursor_name> LOOP
<statements>;
END LOOP;
It is a machine defined loop exit i.e. when all the values in the FOR
construct are exhausted looping stops.
A cursor for loop automatically does the following:
>> Implicitly declares its loop index or variable_name as a %rowtype
record.
>> Opens a cursor.
>> Fetches a row from the cursor for each loop iteration.
>> Closes the cursor when all rows have been processed.
Example:Consider a PL/SQL code to display the employee number, employee name, job of employees of Department ‘Database Security’ with CURSOR FOR Loop
Solution:
DECLARE
CURSOR C1 IS SELECT EMP_NO, EMP_NAME, EMP_JOB FROM EMP
WHERE EMP_DEPT=’DataBase Security’;
/*EMP_REC is a row type variable for cursor c1 record,containing empno, empname and job*/
EMP_REC C1%ROWTYPE;
BEGIN
FOR REC IN C1
LOOP
FETCH C1 INTO EMP_REC;
EXIT WHEN C1%NOTFOUND;
–Printing Employee Number
DBMS_OUTPUT.PUT_LINE(‘Employee Number ‘||REC.EMP_NO);
–Printing Employee Name
DBMS_OUTPUT.PUT_LINE(‘Employee Name ‘||REC.EMP_NAME);
–Printing Employee Job
DBMS_OUTPUT.PUT_LINE(‘JOB ‘||REC.EMP_JOB);
END LOOP;
END;