Oracle继承类型查询：如何获取关联External_Company的Component_Code

已定义的对象类型与子类型 #

Create or replace type Component_ty as object (
   Code VARCHAR(10),
   Component_Description VARCHAR(100),
   Component_Type VARCHAR(10))
   NOT FINAL;

Create or replace type External_Component_ty under Component_ty(
   External_Company REF External_Company_ty);

External_Company_ty 定义 #

Create or replace type External_Company_ty as object (
   VAT VARCHAR(15),
   Company_Name VARCHAR(20),
   Component_code VARCHAR(5),
   Address External_Company_address_ty)
   NOT FINAL;

对应的表结构 #

create table Component of component_ty(
   Code Primary Key, 
   Component_Description NOT NULL,
   Component_Type NOT NULL);

create table External_Company of external_company_ty (
   VAT Primary Key,
   Company_Name NOT NULL,
   Component_Code NOT NULL,
   Address NOT NULL);

原查询语句 #

select c.*, treat(value(c) as external_component_ty).external_company as external_company FROM component c

修改后的查询语句 #

要新增一列显示关联外部公司的Component_Code，需要解析REF类型的External_Company属性，具体修改如下：

select 
    c.*,
    treat(value(c) as external_component_ty).external_company as external_company,
    deref(treat(value(c) as external_component_ty).external_company).component_code as company_component_code
from component c

关键说明 #

• TREAT(value(c) as external_component_ty)：将component表中的行转换为External_Component_ty子类型，从而访问其特有的External_Company属性。
• DEREF(...)：解析REF类型的对象引用，获取对应的External_Company_ty实体对象。
• .component_code：从解析后的实体对象中提取目标字段，作为新增列返回。

内容的提问来源于stack exchange，提问作者mikerug88