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;