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;