oracle pl/sql 嵌套循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12830926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl/sql nested loop
提问by user1084561
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.
如何使用项目表显示数据库中的所有记录？我当前的查询显示项目 894 的信息。我尝试使用循环，但没有成功。
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.
我有两个表，inventory 和 itemid。其中，itemid 包含商品编号和描述，库存表包含商品的信息，例如尺寸、颜色、价格和现有数量。
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;
回答by DJPeter
If we for a moment forget about the formatting this could be done much simpler with a cursor for loop.
如果我们暂时忘记格式化，使用游标 for 循环可以更简单地完成。
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;
回答by Sathyajith Bhat
You're using a separate SELECT..INTOto fetch the itemid, but you assign the itemidto a single value and don't change that.
您使用单独的SELECT..INTO来获取itemid，但是您将 分配itemid给单个值并且不更改它。
Looking at your queries, you can move the itemidfetch 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.
查看您的查询，您可以将itemid提取移动到游标中并加入 2 个表。您将项目提取到 中inventory_record，但我在任何地方都没有看到它的定义/声明。
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.
在这里，我声明了一个记录类型变量，其中包含您要获取的内容、打开游标、将详细信息获取到该游标中。由于没有明确的 where 条件，它将在项目和库存表之间执行内部连接并获取与连接条件匹配的所有行。
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;
回答by Nick Krasnov
If it's just a report(it seems to be). Consider using sql*plusreport formatting commands to display the output of a query in a way you want it to be displayed.
如果它只是一份报告（似乎是）。考虑使用sql*plus报表格式化命令以您希望的方式显示查询的输出。
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.
回答by APC
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.
不要使用嵌套查找，使用joins。数据库真的很擅长连接，集合操作的性能比逐行处理好很多。
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.
此外，在大多数情况下，您不需要声明游标和变量。使用游标循环，让 Oracle 为您完成繁重的工作。
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.
请注意，此解决方案假定每个 ITEM 确实具有匹配的 INVENTORY 记录。如果数据模型允许任何其他情况，这将是一个朗姆酒旧仓库应用程序。
