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.
  1. BEGIN  
  2. //Beginning of FOR LOOP//  
  3.   FOR item IN (  
  4.        SELECT ,supp_id,supp_name  
  5.     FROM Orders  
  6.     WHERE Supp_id LIKE '%DEO%'  
  7.     AND Customer_id > 120  
  8.     ORDER BY supp_name  
  9.   )  
  10.   LOOP  
  11.     DBMS_OUTPUT.PUT_LINE  
  12.       ('Supplier Name = ' || item.supp_name || ', Supplier ID = ' || item.Supp_id);  
  13.   END LOOP;   
  14. //End of FOR LOOP//   
  15. 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.
  1. DECLARE  
  2.  CURSOR C1 IS   
  3.    SELECT ,supp_id,supp_name  
  4.    FROM Orders  
  5.    WHERE Supp_id LIKE '%PERFUME%'  
  6.    AND Customer_id < 100  
  7.    ORDER BY supp_name;  
  8. BEGIN  
  9.   
  10. //Beginning of FOR LOOP//  
  11.   FOR item IN C1  
  12.   LOOP  
  13.     DBMS_OUTPUT.PUT_LINE  
  14.      ('Supplier Name = ' || item.supp_name || ', Supplier ID = ' || item.Supp_id);  
  15.   END LOOP;  
  16. //End of FOR LOOP//  
  17.   
  18. 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

  1. DECLARE
      
  2.  cur_zip zipcode.zip%TYPE;   
  3. //Cursor one cur_zip//   
  4.  CURSOR a_zip IS   
  5. // variable a_zip initialised//  
  6.  SELECT zip, city, state  
  7.  FROM zipcode  
  8.  WHERE state = 'CT';  
  9.  CURSOR c_Customer IS  
  10.  SELECT first_name, last_name  
  11.  FROM Customer  
  12.  WHERE zip = cur_zip;  
  13.  BEGIN  
  14.  FOR b_zip IN a_zip  
  15.  LOOP  
  16.  cur_zip := b_zip.zip;  
  17.  DBMS_OUTPUT.PUT_LINE (CHR(10));  
  18.  DBMS_OUTPUT.PUT_LINE ('Customers living in '||  
  19.  b_zip.city);  
  20.  FOR b_customer in cur1_customer  
  21.  LOOP  
  22.  DBMS_OUTPUT.PUT_LINE (b_customer.first_name||  
  23.  ' '||b_customer.last_name);  
  24.  END LOOP;  
  25.  END LOOP;  
  26.  END;  


Similar Articles