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;