печать информации о подтипе во вложенной таблице

Question

Вот схема:

      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);
   
CREATE OR REPLACE TYPE component_ty_nt AS TABLE OF REF component_ty;

CREATE TABLE Component OF component_ty(
Code PRIMARY KEY, 
Component_Description NOT NULL,
Component_Type NOT NULL,
CONSTRAINT component_type__chk
CHECK (component_type IN ('body', 'Body', 'engine', 'Engine', 'equipment', 'Equipment'))
);

CREATE TABLE MODEL (
model_name VARCHAR(20),
model_year NUMBER,
consumption VARCHAR(6) NOT NULL,
Component component_ty_nt,
Distributor distributor_ty_nt,
Car car_ty_nt,
CONSTRAINT t_pk PRIMARY KEY (Model_Name, Model_Year))
NESTED TABLE Component STORE AS component_ty_nt_TAB
NESTED TABLE Distributor STORE AS distributor_ty_nt_TAB
NESTED TABLE Car STORE AS car_ty_nt_TAB

как я могу получить информацию о моделях, для которых значение external_company в external_comComponent равно нулю?

Answer 1

Вы можете проверить, что значение во вложенной таблице имеет правильный тип и имеетNULLзначение с использованием:

      SELECT m.model_name,
       m.model_year,
       c.code
FROM   model m
       CROSS JOIN LATERAL (
         SELECT DEREF(c.COLUMN_VALUE).code AS code
         FROM   TABLE(m.component) c
         WHERE  DEREF(c.COLUMN_VALUE) IS OF (External_Component_ty)
         AND    TREAT(DEREF(c.COLUMN_VALUE) AS External_Component_ty).External_Company IS NULL
       ) c

Что для минимального примера:

      CREATE OR REPLACE TYPE Component_ty AS OBJECT (
  Code VARCHAR(10)
) NOT FINAL;

CREATE OR REPLACE TYPE External_Component_ty UNDER Component_ty(
  External_Company NUMBER
);

CREATE OR REPLACE TYPE component_ty_nt AS TABLE OF REF component_ty;

CREATE TABLE Component OF component_ty;

CREATE TABLE MODEL (
  model_name VARCHAR(20),
  model_year NUMBER,
  Component component_ty_nt,
  CONSTRAINT t_pk PRIMARY KEY (Model_Name, Model_Year)
)
NESTED TABLE Component STORE AS component_ty_nt_TAB

INSERT INTO component
SELECT external_component_ty('A', 1) FROM DUAL UNION ALL
SELECT external_component_ty('B', NULL) FROM DUAL UNION ALL
SELECT component_ty('C') FROM DUAL;

INSERT INTO model
SELECT 'A', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'A')) FROM DUAL UNION ALL
SELECT 'B', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'B')) FROM DUAL UNION ALL
SELECT 'C', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'C')) FROM DUAL UNION ALL
SELECT 'D', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'E', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'F', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL;

Выходы:

скрипка