oracle 循环遍历oracle中的嵌套表并将对象列表返回给java
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2069418/
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
Looping through nested tables in oracle and returning a list of objects to java
提问by neverMind
I have this table created as follow:
我创建了这个表，如下所示：
CREATE TABLE FORNECPRODS
( SUPPLIER FORNEC_OBJ ,
PRODUCTS PRODTABLE
)NESTED TABLE "PRODUCTS" STORE AS "PRODUCTSTABLE";
/
create or replace
type PRODTABLE as table of PROD_OBJ;
create or replace
TYPE PROD_OBJ AS OBJECT (
ID_PROD NUMBER(6,0),
NOME_PROD VARCHAR2(100),
PREC_COMPRA_PROD NUMBER(10,2),
PREC_VENDA_PROD NUMBER(10,2),
QTD_STOCK_PROD NUMBER(10),
QTD_STOCK_MIN_PROD NUMBER(10),
IVA_PROD NUMBER(6,2)
);
/
create or replace
type PRODTABLE as table of PROD_OBJ;
/
create or replace type FORNEC_OBJ as object (
ID_FORNECEDOR NUMBER(6) ,
NOME_FORNECEDOR VARCHAR2(100) ,
MORADA VARCHAR2(300),
ARMAZEM VARCHAR2(300),
EMAIL VARCHAR2(30),
TLF NUMBER(30) ,
TLM NUMBER(30),
FAX NUMBER(30)
);
/
The problem is that I'm trying to get a list of products for each supplier, from table FORNECPRODS, but I can't (my idea was to return a struct like an hash with :list_of_products). To try that, I used this code only to print products from each supplier:
问题是我试图从表 FORNECPRODS 中获取每个供应商的产品列表，但我不能（我的想法是返回一个结构，如带有 :list_of_products 的散列）。为了尝试这样做，我仅使用此代码来打印每个供应商的产品：
declare
v_products prodtable;
TYPE t_supplier is TABLE OF FORNEC_OBJ;
v_supplier t_supplier;
begin
select supplier bulk collect into v_supplier from fornecprods;
for j in v_supplier.first.. v_supplier.last
loop
select products into v_products
from fornecprods where supplier = v_supplier(j);
dbms_output.put_line('-----------------------');
dbms_output.put_line('Products list of ' || v_supplier(j).NOME_FORNECEDOR);
dbms_output.put_line('-----------------------');
for i in v_products.first .. v_products.last
loop
dbms_output.put(v_products(i).NOME_PROD);
end loop;
end loop;
end;
but it returned no data found for the first select.
但它没有返回第一次选择的数据。
so, could someone please help me find a way to retrieve a list (prodtable) from oracle to java? I already have the class to map a supplier and a product, I even passed an array of each of them from java to oracle, so they're good, I only need my j-tree to look like this:
那么，有人可以帮我找到一种方法来检索从 oracle 到 java 的列表（prodtable）吗？我已经有了映射供应商和产品的类，我什至将它们每个的数组从 java 传递到 oracle，所以它们很好，我只需要我的 j-tree 看起来像这样：
SUPPLIERS
->SUPPLIER1
-prod1
-prod2
- ...
->SUPPLIER2
-prod1
-prod2
- ....
供应商
->
SUPPLIER1 -prod1
-prod2
- ...
->
SUPPLIER2
-prod1 -prod2
- ....
Is it possible to retrieve all that info like an hash of supplier:list_of_productswith my current table and types?
是否可以supplier:list_of_products使用我当前的表和类型检索所有信息，例如 的散列？
回答by Vincent Malgrat
First I would suggest against permanently storing nested tables in the database. While objects types can be really useful for variables and temporary results, you will find that nested tables tend to complicate SQL queries, add overhead and in generalproduce code that is less maintainable than regular relational normalized designs.
首先，我建议不要在数据库中永久存储嵌套表。虽然对象类型对于变量和临时结果非常有用，但您会发现嵌套表往往会使 SQL 查询复杂化，增加开销，并且通常生成的代码比常规关系规范化设计的可维护性更差。
Now for your problem, first let's populate your table:
现在对于您的问题，首先让我们填充您的表：
SQL> DECLARE
2 l_prodtable prodtable := prodtable();
3 BEGIN
4 l_prodtable.extend(2);
5 l_prodtable(1) := prod_obj(1, 'Prod A', '', '', '', '', '');
6 l_prodtable(2) := prod_obj(2, 'Prod B', '', '', '', '', '');
7 FOR i IN 1 .. 2 LOOP
8 INSERT INTO fornecprods VALUES (fornec_obj(i, 'Forn '||i, '',
9 '', '', '', '', ''),
10 l_prodtable);
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed
You would then loop through the elements rather simply:
然后，您可以相当简单地循环遍历元素：
SQL> BEGIN
2 FOR cc IN (SELECT supplier, products FROM fornecprods) LOOP
3 dbms_output.put_line('-----------------------');
4 dbms_output.put_line('Products list of '
5 || cc.supplier.NOME_FORNECEDOR);
6 dbms_output.put_line('-----------------------');
7 FOR i IN 1 .. cc.products.count LOOP
8 dbms_output.put_line('-' || cc.products(i).NOME_PROD);
9 END LOOP;
10 END LOOP;
11 END;
12 /
-----------------------
Products list of Forn 1
-----------------------
-Prod A
-Prod B
-----------------------
Products list of Forn 2
-----------------------
-Prod A
-Prod B
PL/SQL procedure successfully completed
回答by neverMind
@Vincent Malgrat: Thanks, your tip really worked for me! I rearranged my code and came up with this function: create or replace
@Vincent Malgrat：谢谢，你的小费真的对我有用！我重新排列了我的代码并想出了这个功能：创建或替换
function getAllSuppliersObjects RETURN FORNECTABLE AS
suppliersList FORNECTABLE := FORNECTABLE();
BEGIN
FOR res IN (SELECT supplier, products FROM fornecprods) LOOP
/*dbms_output.put_line('-----------------------');
dbms_output.put_line('Existent suppliers: '
|| res.supplier.NOME_FORNECEDOR);*/
suppliersList.extend;
suppliersList(suppliersList.last):= res.supplier;
END LOOP;
return suppliersList;
END;
