Oracle中的SubType参考

Question

请帮帮忙，问题在最下面。

CREATE OR REPLACE TYPE NameType AS OBJECT 
(firstName VARCHAR(20), middleName VARCHAR(20), lastName VARCHAR(20))
INSTANTIABLE
FINAL;

CREATE OR REPLACE TYPE ServicerType AS OBJECT 
(servicerID INTEGER, servicerType VARCHAR(10))
INSTANTIABLE
FINAL;

CREATE OR REPLACE TYPE PersonType AS OBJECT 
(personID INTEGER, name NameType, gender CHAR(1))
INSTANTIABLE
NOT FINAL;

CREATE OR REPLACE TYPE EmployeeType UNDER PersonType 
(servicerRef REF ServicerType)
NOT INSTANTIABLE
NOT FINAL;

CREATE OR REPLACE TYPE FullTimeEmployeeType UNDER EmployeeType 
(yearlySalary NUMBER(19,4))
INSTANTIABLE
FINAL;

CREATE TABLE Servicer OF ServicerType;
CREATE TABLE Person OF PersonType;

INSERT ALL
INTO Servicer values (ServicerType(100, 'Company')
SELECT * FROM dual

INSERT ALL
INTO Person VALUES (FullTimeEmployeeType(200, 
    NameType('Bob', 'T', 'Builder'), 'M', 
    (select ref(s) from servicer s where servicerid = 100), 56700))
SELECT * FROM dual

现在我想运行这个查询：

select p.gender, p.name.firstName, p.name.lastName, p.yearlySalary from person p

我收到一个错误。我知道yearlySalary在FullTimeEmployee中，它是person的一个子类型，但我只是不知道如何引用它。

Answer 1

您需要TREAT函数

select p.gender, p.name.firstName, p.name.lastName,
        treat(value(p) as FullTimeEmployeeType).yearlySalary
from person p
/