执行方法时SQL3错误数据类型不一致

Question

我有这个DB电影管理在电影院:电影(ID_FILM，标题，DIRECTOR_NAME，制作预算，发行日期)放映(ID_SCREEN，ID_FILM*，SCREEN_DATE，房间，城市)看(ID_SEEN，SPECTATOR_NAME，ID_SCREEN*，票价)。

我需要使用MySpectators方法来完成电影类型，返回它的观众的全部(没有重复的)。这就是我为这个方法的签名和正文所写的：

create type tset_spectator as table of varchar(100);
/

alter type tfilm add member function MySpectators return tset_spectator cascade;

create or replace type body tfilm as member function MySpectators return tset_spectator
is
spectatorsfilms tset_spectator;
Begin
select CAST(MULTISET(
        select deref(deref(value(p)).screening_seen)
        from film f, table(f.film_screening) p
        where f.ID_FILM=self.ID_FILM) as tset_spectator)
        into spectatorsfilms
from dual;
return spectatorsfilms;
end;
End;
/

我得到了无法修复的错误：PL / SQL: ORA-00932:不一致的数据类型；预期: REF；DBACINEMA.TSET_REF_SEEN

注:我之前已经创建了类型、表和嵌套表。

谢谢你的帮助。

Answer 1

您还没有显示您的DBACINEMA.TSET_REF_SEEN，所以我只能猜测这是一个集合，您需要再使用一个table()

create or replace type body tfilm as member function MySpectators return tset_spectator
is
spectatorsfilms tset_spectator;
Begin
select CAST(MULTISET(
        select deref(value(scr_seen))
        from film f, table(f.film_screening) p
            ,table(deref(value(p)).screening_seen) scr_seen
        where f.ID_FILM=self.ID_FILM
        ) as tset_spectator)
        into spectatorsfilms
from dual;
return spectatorsfilms;
end;
End;
/