Home » SQL & PL/SQL » SQL & PL/SQL » Nested cursors in nested loops
Nested cursors in nested loops [message #303615] Sat, 01 March 2008 02:52 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #303623 is a reply to message #303620] Sat, 01 March 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you study cursor declaration you see you can give a parameter to a cursor.
PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Managing Cursors in PL/SQL
Subsection Explicit Cursors

By the way, there is a "Homework, Training" forum for these questions.

Regards
Michel
Re: Nested cursors in nested loops [message #303745 is a reply to message #303615] Sun, 02 March 2008 11:32 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Code
Next Topic: One time Only procedures
Goto Forum:
  


Current Time: Fri Dec 09 15:28:44 CST 2016

Total time taken to generate the page: 0.16540 seconds