是否可以对对象类型的表进行选择? [英] Is it possible to perform a select into table of object type?

问题描述

所以我有一个类型

create or replace type body T_Some_type is object
  (...fields)

我有一个由T_Some_type类型的行组成的表类型

and i have a table type made of rows of type T_Some_type

create or replace TYPE T_Some_Table IS TABLE OF T_Some_type;

，我想从某些视图中选择行到此T_Some_Table

and i would like to select rows from some view to this T_Some_Table

select * 
        into T_Some_Table
        from V_Some_View

这是否有可能，以这种方式进行操作是否有任何弊端(如果可能的话). T_Some_Type中的列是否必须与V_Some_View中的列顺序相同，或者如果列名相同，则plsql将在此处进行绑定吗?

Is this even possible, and are there any cons for doing it this way (if possible at all). Do columns in T_Some_Type have to be in same order as in V_Some_View or will plsql do binding here if names of columns are the same?

如果不可能的话，还有什么选择?

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.

推荐答案

是的.您需要将列包装在对象的构造函数中，并在SELECT语句中使用BULK COLLECT选项:

Yes, it is. You need to wrap your columns in the object's constructor and use the BULK COLLECT option in the SELECT statement:

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).

SELECT中的列必须与在对象的构造函数中找到的顺序相同.如果尚未显式定义构造函数，则按规范中声明的顺序在每一列中显式地存在一个构造器.

Columns in the SELECT must 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.

使用此功能的唯一弊端是大量行将导致大量内存使用.如果希望使用它来处理大量行，则应在LIMIT子句中使用循环.

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 LIMIT clause.

除了在规范中找到的列列表之外，还可以指定一个显式构造函数.构造函数可以使用您定义的任何输入，因此，显然，当您使用显式构造函数时，必须遵循它的参数列表.这是一个示例:

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;

这篇关于是否可以对对象类型的表进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！