Cursors for Loop
 
In continuation of my previous article about Oracle Cursors, here I will share the use of cursors for loops with an appropriate example.
 
The process of opening, fetching, and closing is handled implicitly by a cursors FOR LOOP. If there is a need to FETCH and PROCESS each and every record from a cursor , the cursor FOR LOOP is helpful for that.
 
Let's learn about the implicit cursor for loop using the following table ORDERS and example:
 
Orders

Supp_id Supp_name Items Customer_id
111 AAA DEO #128
222 BBB Perfume #32
333 CCC Perfume #121
444 DDD DEO #88
555 EEE DEO #199
666 FFF Perfume #02
777 GGG DEO #105

Implicit Cursor for Loop
 
Note: Here, an implicit cursor FOR LOOP statement prints the name of the supplier and supplier id of the entire item named as DEO whose customer has an ID greater than 100.
 
Example

BEGIN

//Beginning of FOR LOOP//

  FOR item IN (

       SELECT ,supp_id,supp_name

    FROM Orders

    WHERE Supp_id LIKE '%DEO%'

    AND Customer_id > 120

    ORDER BY supp_name

  )

  LOOP

    DBMS_OUTPUT.PUT_LINE

      ('Supplier Name = ' || item.supp_name || ', Supplier ID = ' || item.Supp_id);

  END LOOP; 

//End of FOR LOOP// 

END;
 
Result
 

Supp_id Supp_name
Supplier ID = 111 Supplier Name = AAA
Supplier ID = 555 Supplier Name = EEE
Supplier ID = 777 Supplier Name = GGG


Explicit Cursor for Loop

 
Note: In the following example, an explicit cursor FOR LOOP statement prints the name of the supplier and supplier id of the entire item named PERFUME whose customer has an ID lesser than 100.
 
Example

 

DECLARE

 CURSOR C1 IS 

   SELECT ,supp_id,supp_name

   FROM Orders

   WHERE Supp_id LIKE '%PERFUME%'

   AND Customer_id < 100

   ORDER BY supp_name;

BEGIN

 

//Beginning of FOR LOOP//

  FOR item IN C1

  LOOP

    DBMS_OUTPUT.PUT_LINE

     ('Supplier Name = ' || item.supp_name || ', Supplier ID = ' || item.Supp_id);

  END LOOP;

//End of FOR LOOP//

 

END;
 
Result
 

Supp_id Supp_name
Supplier ID = 222 Supplier Name = BBB
Supplier ID = 666 Supplier Name = FFF


Nested Cursor for Loop

Cursors can be nested, in other words a cursor can have another cursor inside it.

If there is one main cursor called a parent cursor and two small/child cursors then each time the main cursor makes a single loop, it will loop through each small cursor once and then begin a second round.
 
Here is an example of a nested cursor with an assumed table customer:
 
Customer
 

Cust_id First_name Last_name Zip Code City State
111 Rahul Tondon 456246 Bareilly Uttar Pradesh
222 Karan Siddhu 455633 Mumbai Maharashtra
333 Sandhiya Rathi 345345 Ahemdabad Gujarat
444 Meenakshi Gautam 574567 Dehradun Uttrakhand
555 Saras Katyal 345335 Dharamshala Himachal Pradesh


Example
 
 
DECLARE

 cur_zip zipcode.zip%TYPE; 

//Cursor one cur_zip// 

 CURSOR a_zip IS 

// variable a_zip initialised//

 SELECT zip, city, state

 FROM zipcode

 WHERE state = 'CT';

 CURSOR c_Customer IS

 SELECT first_name, last_name

 FROM Customer

 WHERE zip = cur_zip;

 BEGIN

 FOR b_zip IN a_zip

 LOOP

 cur_zip := b_zip.zip;

 DBMS_OUTPUT.PUT_LINE (CHR(10));

 DBMS_OUTPUT.PUT_LINE ('Customers living in '||

 b_zip.city);

 FOR b_customer in cur1_customer

 LOOP

 DBMS_OUTPUT.PUT_LINE (b_customer.first_name||

 ' '||b_customer.last_name);

 END LOOP;

 END LOOP;

 END;

Next Recommended Readings
F11 Research & Development, LLC
F11Research & Development, LLC