Cursors For Loop in Oracle

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:

 

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.

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.

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:

 

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

     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;