当嵌套表在记录类型内时，如何将数据填充到 Oracle 中的嵌套表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18017154/
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
How to populate data into a nested table in Oracle when the nested table is within a record type
提问by one23
I need to be able to return from a procedure a list of values in the form of a cursor variable. But within the list some fields can have multiple values
我需要能够从过程中以游标变量的形式返回值列表。但在列表中，某些字段可以有多个值
e.g. a product can have multiple description lines in the description field (obtained from a different table).
例如，一个产品在描述字段中可以有多个描述行（从不同的表中获得）。
I was thinking in line of creating a nested table within a record type and associate this to a cursor.
我正在考虑在记录类型中创建一个嵌套表并将其关联到一个游标。
TYPE N_TYPE IS TABLE OF VARCHAR2(350);
TYPE TYPE1 IS RECORD ( FIELD_1 VARCHAR2(100)
, FIELD_2 VARCHAR2(30)
, FIELD_3 N_TYPE);
TYPE T_CUR IS REF CURSOR RETURN TYPE1;
Procedure p_proc (p_1 IN VARCHAR2, p_2 OUT t_cur) is
-- processing input parameter and passing out a cursor to host application
end p_proc;
Here within the procedure I will need to pass p_1into a table and using a explicit cursor to retrieve the data into Field_1and Field_2.
在此过程中，我需要传入p_1一个表并使用显式游标将数据检索到Field_1和 中Field_2。
Then from another table I will need to assign multiple records into Field_3.
然后从另一个表中，我需要将多条记录分配到Field_3.
Can anyone show me how to populate data into a nested table when the table is part of a datatype within a record? And how do I check once it has been populated. And how to assign this back to a cursor variable for the out parameter?
当表是记录中数据类型的一部分时，谁能告诉我如何将数据填充到嵌套表中？以及如何在填充后进行检查。以及如何将其分配回 out 参数的游标变量？
回答by krokodilko
This document: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CIHIEBJC
describes how to work with collection types in PL/SQL:
Basic example:
本文档：http: //docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CIHIEBJC
描述了如何在 PL/SQL 中使用集合类型：
基本示例：
DECLARE
TYPE N_TYPE IS TABLE OF VARCHAR2(350);
TYPE TYPE1 IS RECORD ( FIELD_1 VARCHAR2(100)
, FIELD_2 VARCHAR2(30)
, FIELD_3 N_TYPE);
v_n n_type;
v_type1 type1;
BEGIN
v_n := n_type(); -- initialize an empty collection
v_n.extend( 3 ); -- add 3 elements to the table
v_n( 1 ) := 'First string ';
v_n( 2 ) := 'Second string ';
v_n( 3 ) := 'Third string ';
v_n.extend; -- add 1 element at the end of the table
v_n( v_n.last ) := 'Next string';
--assign the table to the field_3 of the record
v_type1.field_3 := v_n;
-- check values
FOR i in v_type1.field_3.first .. v_type1.field_3.last LOOP
DBMS_OUTPUT.PUT_LINE( v_type1.field_3( i ) );
END LOOP;
END;
/
--- DBMS_OUTPUT -------
First string
Second string
Third string
Next string
