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;

Peter Å · Accepted Answer · 2012-10-12 05:42:20Z

4

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;

edited Oct 12, 2012 at 5:42

answered Oct 11, 2012 at 11:59

Peter Å

1,3191 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sathyajith Bhat Over a year ago

yeah, this would be my preferred way too

Sathyajith Bhat · Accepted Answer · 2012-10-11 04:17:54Z

1

You're using a separate SELECT..INTO to fetch the itemid, but you assign the itemid to a single value and don't change that.

Looking at your queries, you can move the itemid fetch into the cursor & join the 2 tables. You fetch the items into inventory_record, but I don't see the definition/declaration of that anywhere.

Here I declare a record type variable consisting of what you're fetching, open the cursor, fetch the details into that cursor. Since there's no explicit where condition, it will perform an inner join between item & inventory tables & fetch all rows matching the join condition.

set serveroutput ON

DECLARE
    TYPE inventory_rec IS RECORD (
      itemid item.itemid%TYPE,
      itemdesc item.itemdesc%TYPE,
      itemsize inventory.itemsize%TYPE,
      color inventory.color%TYPE,
      curr_price inventory.curr_price%TYPE,
      qoh inventory.qoh %TYPE,
      total inventory.curr_price%TYPE); -- record type to hold what cursor will be fetching

    inventory_record INVENTORY_REC;
    current_item     NUMBER(8);
    totalvalue       NUMBER(8, 2);
    description      VARCHAR2(50);
    item_id          NUMBER(3);

    CURSOR inventory_info IS
      SELECT itemid,
             itemdesc,
             itemsize,
             color,
             curr_price,
             qoh,
             curr_price * qoh AS Total
      FROM   inventory
             join item USING (itemid);
-- join item & inventory, so that it shows listings for all items present in inventory

BEGIN

    OPEN inventory_info;

    LOOP
        FETCH inventory_info INTO inventory_record;
        EXIT WHEN inventory_info%NOTFOUND;
        dbms_output.Put_line('Current item: '
                             || inventory_record.itemdesc);
        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;

answered Oct 11, 2012 at 4:17

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

2 Comments

user1084561 Over a year ago

This leads me towards a good direction. I wanted to display the item id and description, and all the inventory information associated with that item id to be displayed followed by the total value of the items. The query I posted does this, except that I have to enter an id. I don't want to have to enter an id, I just want it to show all the data upon execution. Do you think it's possible to have one cursor point to the row of the other on the WHERE clause?

Sathyajith Bhat Over a year ago

@user1084561 you don't have to enter an id with my query. It fetches all itemids from the item table. You can set where clause of a cursor based on another value using cursor parameters

APC · Accepted Answer · 2012-10-11 15:27:30Z

Don't use nested look-ups, use joins. Databases are really good at joins, and the performance of set operations is a lot a better than row-by-row processing.

Also, you don't need to declare cursors and variables in most situations. Use a cursor loop and let Oracle do the heavy lifting for you.

set serveroutput on  
DECLARE     
    totalvalue      number(8,2);
BEGIN      
    totalvAlue:=0;
    FOR inv_itm_rec IN (       
        SELECT    itm.itemid
            , itm.itemdesc
            , inv.itemsize         
            , inv.color         
            ,inv.curr_price         
            ,inv.qoh         
            ,inv.curr_price*inv.qoh as Total     
        FROM inventory  inv   
        JOIN item itm
            ON itm.itemid=inv.itemid
       ) 
     LOOP   
    DBMS_OUTPUT.PUT_LINE('ItemId: ' || inv_itm_rec.itemid);
    DBMS_OUTPUT.PUT_LINE('Description: ' || inv_itm_rec.itemdesc);
    DBMS_OUTPUT.PUT_LINE('Size: ' || inv_itm_rec.itemsize);
    DBMS_OUTPUT.PUT_LINE('Color: ' || inv_itm_rec.color);
    DBMS_OUTPUT.PUT_LINE('Price: ' || inv_itm_rec.curr_price);
    DBMS_OUTPUT.PUT_LINE('QOH: ' || inv_itm_rec.qoh);
    DBMS_OUTPUT.PUT_LINE('Value: ' || inv_itm_rec.total);
    TotalValue:=TotalValue + inv_itm_rec.total;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('TOTAL VALUE: ' || TotalValue);
END;

Note, this solution assumes that evry ITEM does have a matching INVENTORY record. It would be a rum old warehouse application if the data model allowed anything else to be the case.

Nick Krasnov · Accepted Answer · 2012-10-12 06:26:47Z

If it's just a report(it seems to be). Consider using sql*plus report formatting commands to display the output of a query in a way you want it to be displayed.

Here is an example:

SQL> create table Items(
  2    i_num number,
  3    i_descr varchar2(101),
  4    i_size varchar2(3),
  5    i_price number,
  6    i_qoh number
  7  );

Table created

SQL> create table Inventories(
  2    id number,
  3    i_num number
  4  );

Table created

SQL> insert into items(i_num,i_size,i_price,i_qoh,i_descr)
  2    select 1, 'S', 123, 25, 'Item_1' from dual union all
  3    select 2, 'L', 424, 12, 'Item_1' from dual union all
  4    select 4, 'S', 45,  54, 'Item_4' from dual union all
  5    select 5, 'S', 78,  54, 'Item_4' from dual union all
  6    select 6, 'S', 123, 22, 'Item_5' from dual union all
  7    select 7, 'S', 127, 65, 'Item_5' from dual
  8  ;

6 rows inserted

SQL> commit;

Commit complete

SQL> insert into inventories
  2    select i_num, i_num
  3      from items;

6 rows inserted

SQL> commit;

Commit complete

And now our report:

SQL> column i_descr format a10
SQL> column i_size  format a3
SQL> column i_price format 99999
SQL> column i_qoh   format 99999
SQL> column value   format 99999
SQL> break on i_descr skip 2
SQL> compute sum label 'Total: ' of value on i_descr;

SQL> select itm.i_descr
  2       , itm.i_size
  3       , itm.i_price
  4       , itm.i_qoh
  5       , sum(i_price*i_qoh) Value
  6   from inventories inv
  7   join items itm on (inv.i_num = itm.i_num)
  8   group by itm.i_num
  9          , itm.i_descr
 10          , itm.i_size
 11          , itm.i_price
 12          , itm.i_qoh
 13   order by i_descr, i_price;


I_DESCR    I_S    I_PRICE  I_QOH  VALUE
---------- --- ---------- ------ ------
Item_1     S          123     25   3075
           L          424     12   5088
**********                       ------
Total:                             8163


Item_4     L           45     54   2430
           S           78     54   4212
**********                       ------
Total:                             6642


Item_5     S          123     22   2706
           L          127     65   8255
**********                       ------
Total:                            10961



6 rows selected.

Collectives™ on Stack Overflow

pl/sql nested loop

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related