oracle 是否可以在对象类型表中执行选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30305161/
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
Is it possible to perform a select into table of object type?
提问by Mladen Or?oli?
So i have a type
所以我有一个类型
create or replace type body T_Some_type is object
(...fields)
and i have a table type made of rows of type T_Some_type
我有一个由 T_Some_type 类型的行组成的表类型
create or replace TYPE T_Some_Table IS TABLE OF T_Some_type;
and i would like to select rows from some view to this T_Some_Table
我想从某个视图中选择行到这个 T_Some_Table
select *
into T_Some_Table
from V_Some_View
Is this even possible, and are there any cons for doing it this way (if possible at all).
Do columns in T_Some_Typehave to be in same order as in V_Some_Viewor will plsql do binding here if names of columns are the same?
这甚至可能吗，这样做是否有任何缺点（如果可能的话）。列中的列是否T_Some_Type必须与列的顺序相同，V_Some_View或者如果列的名称相同，plsql 会在此处进行绑定吗？
If not possible, what would be alternative?
如果不可能，有什么替代方案？
Edit : Having bodies defined is assumed, they are not here as they are not relevant to the question.
编辑：假设定义了主体，它们不在这里，因为它们与问题无关。
回答by Allan
Yes, it is. You need to wrap your columns in the object's constructor and use the BULK COLLECToption in the SELECTstatement:
是的。您需要将列包装在对象的构造函数中并使用语句中的BULK COLLECT选项SELECT：
CREATE OR REPLACE TYPE t_some_type AS OBJECT(f varchar2(10))
/
CREATE OR REPLACE TYPE t_some_table IS TABLE OF t_some_type
/
DECLARE
v_some_table t_some_table;
BEGIN
SELECT t_some_type (dummy)
BULK COLLECT INTO v_some_table
FROM DUAL;
END;
As an aside, you also need to make sure that you create the object specification, not just the body (as in your example).
顺便说一句，您还需要确保创建对象规范，而不仅仅是主体（如您的示例中所示）。
Columns in the SELECTmust be in the same order as they're found in the object's constructor. If you have not explicitly defined a constructor, one explicitly exists with each column in the order declared in the specification.
中的列的SELECT顺序必须与它们在对象的构造函数中的顺序相同。如果您没有明确定义一个构造函数，则一个构造函数按照规范中声明的顺序与每一列显式存在。
The only downside to using this functionality is that a large number of rows will result in heavy memory usage. If you expect to use this to process a large number of rows, you should use a loop with the LIMITclause.
使用此功能的唯一缺点是大量行会导致大量内存使用。如果您希望使用它来处理大量行，您应该使用带有LIMIT子句的循环。
It is possible to specify an explicit constructor, in addition to the column list found in the specification. The constructor can have whatever input you define, so, obviously, when you use an explicit constructor, you have to follow it's argument list. Here's an example:
除了规范中的列列表之外，还可以指定显式构造函数。构造函数可以具有您定义的任何输入，因此，很明显，当您使用显式构造函数时，您必须遵循它的参数列表。下面是一个例子：
CREATE OR REPLACE TYPE t_some_type AS OBJECT
(
f1 VARCHAR2 (10),
CONSTRUCTOR FUNCTION t_some_type (p_length NUMBER, p_value VARCHAR2)
RETURN SELF AS RESULT
);
/
CREATE OR REPLACE TYPE BODY t_some_type AS
CONSTRUCTOR FUNCTION t_some_type (p_length NUMBER, p_value VARCHAR2)
RETURN SELF AS RESULT IS
BEGIN
self.f1 := LPAD (p_value, p_length, p_value);
RETURN;
END t_some_type;
END;
/
CREATE OR REPLACE TYPE t_some_table IS TABLE OF t_some_type
/
DECLARE
v_some_table t_some_table;
BEGIN
--Explicit Constructor
SELECT t_some_type (10, dummy)
BULK COLLECT INTO v_some_table
FROM DUAL;
DBMS_OUTPUT.put_line (v_some_table (1).f1);
--Implicit Constructor
SELECT t_some_type (dummy)
BULK COLLECT INTO v_some_table
FROM DUAL;
DBMS_OUTPUT.put_line (v_some_table (1).f1);
END;
回答by moilejter
It is possible, more or less how the OP wanted to do it:
有可能，或多或少 OP 想要这样做：
SELECT *
BULK COLLECT INTO v_some_table
FROM <some_source_table>;
For more detail, see: https://blogs.oracle.com/oraclemagazine/on-bulk-collect- worked for me in Oracle 12.1.
有关更多详细信息，请参阅：https: //blogs.oracle.com/oraclemagazine/on-bulk-collect- 在 Oracle 12.1 中对我来说有效。
回答by kapoc
you can set fields from select by example
您可以通过示例从选择中设置字段
CREATE OR REPLACE TYPE t_some_type AS OBJECT(f varchar2(10), y varchar2(10))
/
CREATE OR REPLACE TYPE t_some_table IS TABLE OF t_some_type
/
DECLARE
v_some_table t_some_table;
v_counter int;
BEGIN
SELECT t_some_type (column_1, column_2)
BULK COLLECT INTO v_some_table
FROM some_table;
-- show the count data fetched
dbms_output.put_line( 'counter value: ' || to_char(v_counter));
END;
