PL/SQL Cursor FOR Loop

Using ‘Cursor FOR loops’ we are eliminating explicit cursor life cycle, when we are using cursor for loops in PL/SQL block, we are not allowed to use OPEN, FETCH CLOSE statements, this method is also called as shortcut method of cursor, Below is the syntax

FOR INDEXVariableName IN CursorName

LOOP

Executable Statements;

END LOOP;

This loop is used in executable section of the PL/SQL block whenever we are using this loop internally oracle server only open the cursor, fetched data from the cursor and close the cursor automatically, Here ‘INDEXVariable’ Internally behaves %ROWTYPE in cursor for loop ‘INDEXVariable’ internally behaves like a record type variable (%ROWTYPE)

DECLARE

CURSOR C1 IS select * from emp;

BEGIN

FOR I IN C1

LOOP        

Dbms_output.put_line(i.ename||’ ‘||i.sal);

END LOOP;

END;

Always in ‘FOR LOOP’ index variable internally behaves like integer datatype variable, whereas in ‘Cursor FOR Loops’ index Variable Internally behaves ‘Record type Variable’ (%ROWTYPE), we can also eliminate declare section of the cursor using cursor for loop, in this case we are specifying ‘Select Statement’ in place of CursorName with in Cursor for Loop, below is the syntax

FOR IndexVariableName IN (Select Statement)

LOOP

Executable Statements;

END LOOP;

BEGIN

FOR I IN (select * from emp)

LOOP

Dbms_output.put_line(i.ename||’ ‘||i.sal);

END LOOP;

END;

Write a program to display total salary from emp table using shortcut method of the cursor without using SUM function.

DECLARE

CURSOR C1 is select * from emp;

A number(10) :=0;

BEGIN

FOR I IN C1

LOOP

A := A+NVAL(I.SAL,0);

Dbms_output.put_line(A);

END LOOP;

END;