Reputation: 115
pl/sql nested loop
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;
Upvotes: 3
Views: 20211
Answers (4)
Reputation: 27251
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.
Upvotes: 1
Reputation: 1319
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;
Upvotes: 4
Reputation: 146209
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.
Upvotes: 1
Reputation: 21851
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;
Upvotes: 1