Cursor with For loops.

Cursor with For loops:

• PL/SQL provides a simple type of loop, which implicitly handles the cursor processing.
• Within the loop each record in the Active set is retrieved and used.
• Each loop iteration advances the cursor pointer by one record in the Active set.
• The loop works on the range oriented operational logic.
• The loop is very useful when travelling the entire data in the database table.
• It is more Dynamic in operation than the simple loop.

Syntax:
For <variable> IN <cursor name>
LOOP
    <Ex Stmt>;
END LOOP;

Advantages:
No need to

• Declare INDEX variable explicitly.
• OPEN a cursor.
• FETCH the rows from cursor.
• Terminate the loop explicitly.
• CLOSE the cursor.

SQL>DECLARE
         Cursor cpf is
         Select empno,ename,sal basic,sal*0.45 hra,sal*0.35 da,sal*0.15 pf,deptno from emp
         where sal>4000;
         vgross number;
         cnt number;
         BEGIN
         for i in cpf
         loop
             vgross:=i.basic+i.hra+i.da-i.pf;
             display(i.empno||’ ‘||rpad(i.ename,8)||’ ‘||rpad(i.basic,6)|| ‘ ‘||rpad(i.hra,5)||’ ‘||rpad(i.da,5)||’ ‘||rpad(i.pf,5)||’ ‘||rpad(vgross,5)||’ ‘||i.deptno);
             cnt:=cpf%rowcount;
         End loop;
         display(‘No of emps eligible for pf is’||cnt);
         end;