Nested cursors in nested loops [message #303615] |
Sat, 01 March 2008 02:52  |
rbetts14
Messages: 2 Registered: March 2008
|
Junior Member |
|
|
Hi, im just learning PL/SQL and and trying to work out a nest cursor loop. This is the code I have
declare
cursor order_cursor is
select *
from orders;
order_row order_cursor%rowtype;
cursor order_line_cursor is
select item_desc
from inventory, item, order_line
where inventory.ITEM_ID = item.ITEM_ID and inventory.INV_ID = order_line.INV_ID
and order_line.O_ID = order_row.O_ID;
order_line_row order_line_cursor%rowtype;
begin
for order_row in order_cursor loop
DBMS_OUTPUT.PUT_LINE('Items for order #'||order_row.O_ID||' on '|| order_row.o_date);
for order_line_row in order_line_cursor loop
DBMS_OUTPUT.PUT_LINE(order_line_row.item_desc);
end loop;
DBMS_OUTPUT.PUT_LINE('');
end loop;
end;
/
basic info about the tables. There are 6 orders, and each order has atleast 1 order line item.
When I run the code, it outputs the the first line
Items for order # <number> on <date>
but the nested loop returns no results.
if i hard code the where condition to O_ID = 1 it works so I know the logic is working, but I want it to show the items for the current order, so hard coding O_ID is not an option. I can get it to work by adding several lines to declaring a variable, assigning it the current value of order_row.O_ID and using that in the where condition.
Why I do not understand is why this version of the code does not work? Can any one explain why or is it just incaplable of doing it this way?
[Updated on: Sat, 01 March 2008 02:54] Report message to a moderator
|
|
|
Re: Nested cursors in nested loops [message #303618 is a reply to message #303615] |
Sat, 01 March 2008 03:25   |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
not exactly what you have posted .but, may you can try this :
BEGIN
FOR rec_c1 IN (SELECT eName,
deptNo
FROM emp)
LOOP
dbms_Output.Put_Line(rec_c1.eName);
FOR rec_c2 IN (SELECT dName
FROM dept
WHERE dept.deptNo = rec_c1.deptNo)
LOOP
dbms_Output.Put_Line(rec_c2.dName);
END LOOP;
dbms_Output.Put_Line('---------------');
END LOOP;
END;
regards,
|
|
|
Re: Nested cursors in nested loops [message #303620 is a reply to message #303615] |
Sat, 01 March 2008 03:43   |
rbetts14
Messages: 2 Registered: March 2008
|
Junior Member |
|
|
Thats for the feedback. I know it can work that way, and have another problem that I did that way. This one specifically asks to declare the cursors before the begin (my professor can be very picky about these things). And I have a working fix for this one.
declare
cursor order_cursor is
select *
from orders;
order_row order_cursor%rowtype;
order_id orders.O_ID%type; -- added this line
cursor order_line_cursor is
select item_desc
from inventory, item, order_line
where inventory.ITEM_ID = item.ITEM_ID and inventory.INV_ID = order_line.INV_ID
and order_line.O_ID = order_id ; -- changed this line
order_line_row order_line_cursor%rowtype;
begin
for order_row in order_cursor loop
DBMS_OUTPUT.PUT_LINE('Items for order #'||order_row.O_ID||' on '|| order_row.o_date);
order_id := order_row.O_ID; -- added this line
for order_line_row in order_line_cursor loop
DBMS_OUTPUT.PUT_LINE(order_line_row.item_desc);
end loop;
DBMS_OUTPUT.PUT_LINE('');
end loop;
end;
The code works just fine with these 3 changes
I am hoping someone can explain why I can simply use a comparison directly against the result set of the upper query. I can output it with order_row.O_ID but for some reason, it cant be used in a comparison if the cursor is declared in the DECLARE and imlicit in the BEGIN block.
I have it working with this code. What I can't seem to find is an explination of why I can't directly compare a field in nested cursor's subquery directly to a field in the parent's subquery without first saving it into another variable.
|
|
|
|
Re: Nested cursors in nested loops [message #303745 is a reply to message #303615] |
Sun, 02 March 2008 11:32  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
your problem is related to the dual specifications of your rowtype based variables. You have defined a variable to hold a specific rowtype, and then defined a for loop that implicitly creates a variable of the same name which means during loop processing, you have two variables with the same name. To understand what is happening and why your code does not work as you expect, read up on SCOPING OF VARIABLES in PL/SQL.
Michel clued you to where to start looking.
Good luck, Kevin
|
|
|