Oracle - 将方法(过程)添加到嵌套表

Question

我有这个作业，我必须将过程添加到嵌套表中，这是结构

create type associe_t as Object(noAs int, nomAs varchar(50), capAs int)
/
create type lesAssocies_t as TABLE OF associe_t
/
create type promoteur_t as Object(matP int, nomP varchar(50), adrP varchar(50), lesAssocies lesAssocies_t)
/
create table promoteur of promoteur_t (
    constraint pk_promoteur primary key(matP));
create table promoteur of promoteur_t (
    constraint pk_promoteur primary key(matP),
    constraint limCapAs check (capAs <= 1 000 000)
) tableSpace TBS3202A2012
NESTED TABLE lesAssocies STORE AS Table_lesAssocies ;

我想要一个类型 associe_t 的方法，它可以根据传入参数的数字更新记录中的值。

所以我尝试将方法签名添加到我的类型中：

alter type associe_t add member procedure augmenteCapAs(P IN number) cascade ;

我试图在以下之后添加该过程的主体：

CREATE or replace TYPE BODY associe_t AS
MEMBER procedure augmenteCapAs(P in number ) AS 
  BEGIN 
    update Table_lesAssocies t set t.capAs = P/100 where t.noAs = self.noAs;
  END;
END;
/

如果我使用表名Table_lesAssocies它会给我以下错误：

ORA-22812: cannot reference nested table column's storage table

如果我使用表名lesAssocies_t它会告诉我该表不存在。

我做错了什么？我很确定我不应该使用该表的物理名称（Table_lesAssocies）

我应该使用什么表名？

Answer 1

如错误消息所述，您无法直接访问（检索或存储）嵌套表的列 - 只能通过外部表。因此，您可以按如下方式重写代码：

SQL> create type associe_t as Object(noAs int, nomAs varchar(50), capAs int)
  2  /
Type created

SQL> create type lesAssocies_t as TABLE OF associe_t
  2  /
Type created

SQL> create type promoteur_t as Object(matP int, nomP varchar(50), adrP varchar(50), lesAssocies lesAssocies_t)
  2  /
Type created

SQL> create table tb_promoteur of promoteur_t (
  2      constraint pk_promoteur primary key(matP)
  3  )tablespace users
  4  NESTED TABLE lesAssocies STORE AS Table_lesAssocies;
Table created
SQL> alter table table_lesassocies add constraint chk_capas check (capas < 1000000);
Table altered
SQL> alter type associe_t add member procedure augmenteCapAs(P IN number) cascade ;
Type altered

SQL> CREATE OR REPLACE TYPE BODY associe_t AS
  2  MEMBER procedure augmenteCapAs(P in number ) AS
  3    BEGIN
  4      update table(select lesAssocies
  5                     from tb_promoteur) t
  6         set t.capAs = P/100
  7       where t.noAs = self.noAs;
  8    END;
  9  END;
 10  /
Type body created