PL/SQL - Cursor FOR loop

In the last post we have seen how to loop through cursor records by using its attributes to manually check for the different stages of cursor OPEN, FETCH and CLOSE. In this post we will write more compact code with CURSOR FOR loop statement. A FOR loop opening a cursor directly is known as a CURSOR FOR loop.

Following program will print the Salary for each Employee in EMP table.

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE
/*Declare an explicit cursor to select employee information*/
 CURSOR C_EMP IS
 SELECT EMP_NAME, SAL
 FROM EMP;
BEGIN
/*FOR Loop uses the cursor C_EMP*/
FOR EMP IN C_EMP
LOOP

 /*Display message*/
 DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME||' Salary = '||EMP.SAL);

END LOOP;
END;
/

You will get the output similar to this:

Nasir Naveed Salary = 30000
Aurangzaib Khan Salary = 40000
Muhammad Anwar Salary = 50000
...
PL/SQL procedure successfully completed.

Note that, with the help of CURSOR FOR loop we have:

• No need to declare the block variables for holding the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type.
• No need to explicitly open or close the cursor in the PL/SQL program.

Related Post(s):

• PL/SQL Cursor - Overview
• PL/SQL - Cursor Attributes and Loop