pl/sql nested loop

Question

How would I display all records in the database using the items table? My current query displays the information for item 894. I attempted using a loop, but no luck.

I have two tables, inventory and itemid. Where itemid has the item number and the description, and the inventory table has the items' information, such as size, color, price, and quantity on hand.

 set serveroutput on
 DECLARE
    current_item    number(8);      
    totalvalue      number(8,2);
    description     varchar2(50);
    item_id         number(3);


    CURSOR Inventory_Info IS       
      SELECT 
         itemsize
        ,color
        ,curr_price
        ,qoh
        ,curr_price*qoh as Total
    FROM inventory
     WHERE itemid=Current_item;



BEGIN

    current_item:=894;
    totalvAlue:=0;  


    SELECT 
     itemdesc, itemid         
       INTO description, item_id
    FROM item
      WHERE itemid=current_item;

    DBMS_OUTPUT.PUT_LINE('Item ID: ' || TO_CHAR(item_id) || ' Item Description: ' || description);

    OPEN Inventory_Info;
    LOOP
            Fetch Inventory_Info INTO Inventory_rocord;
            EXIT WHEN Inventory_Info%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE('Size: ' || Inventory_record.itemsize);
     DBMS_OUTPUT.PUT_LINE('Color: ' || Inventory_record.color);
     DBMS_OUTPUT.PUT_LINE('Price: ' || Inventory_record.curr_price);
     DBMS_OUTPUT.PUT_LINE('QOH: ' || Inventory_record.qoh);
     DBMS_OUTPUT.PUT_LINE('Value: ' || Inventory_record.total);

     TotalValue:=TotalValue + Inventory_record.total;

     End Loop;

     DBMS_OUTPUT.PUT_LINE('TOTAL VALUE: ' || TotalValue);
 Close Inventory_Info;

    EXCEPTION
       WHEN NO_DATA_FOUND THEN                      
       DBMS_OUTPUT.PUT_LINE('No inventory for Item No. '|| current_item);
    WHEN OTHERS THEN                             
       DBMS_OUTPUT.PUT_LINE('Error Message: '|| SQLERRM);

 END;

Answer 1

If we for a moment forget about the formatting this could be done much simpler with a cursor for loop.

set serveroutput ON
DECLARE
BEGIN
  FOR item_rec IN (SELECT itemdesc, itemid         
                     FROM item
                  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Item ID: ' || TO_CHAR(item_rec.itemid) 
                             || ' Item Description: ' || item_rec.itemdesc);                      

    FOR Inventory_record IN (SELECT itemsize
                                  , color
                                  , curr_price
                                  , qoh 
                                  , curr_price*qoh AS Total
                               FROM inventory
                              WHERE itemid = item_rec.itemid
                            ) LOOP

      DBMS_OUTPUT.PUT_LINE('Size: ' || Inventory_record.itemsize);
      DBMS_OUTPUT.PUT_LINE('Color: ' || Inventory_record.color);
      DBMS_OUTPUT.PUT_LINE('Price: ' || Inventory_record.curr_price);
      DBMS_OUTPUT.PUT_LINE('QOH: ' || Inventory_record.qoh);
      DBMS_OUTPUT.PUT_LINE('Value: ' || Inventory_record.total);

      TotalValue:= TotalValue + Inventory_record.total;

    END LOOP;      
  END LOOP;
END;