oracle - 使用 refs 时转换为 Oracle SQL 中的子类型

Question

我有一个 lecturer_typ 类型，它有 2 个子类型 module_leader_typ 和 external_typ。

我在一个名为 lecturer_tbl 的表中插入了所有这 3 种类型的对象。

我想在另一个表中插入对 module_leader_typ 的引用，但它告诉我:

SQL Error: ORA-00932: inconsistent datatypes: expected REF 21010175.MODULE_LEADER_TYP got REF 21010175.LECTURER_TYP'

这是我的代码:

CREATE TYPE lecturer_typ AS OBJECT
(
  teacher_id      NUMBER,
  first_name      VARCHAR2(70),
  second_name     VARCHAR2(70)
) NOT FINAL;

CREATE TYPE module_leader_typ UNDER lecturer_typ
(
  subject_knowledge VARCHAR2(255),
  contract_type     VARCHAR(100),
  years_experience  NUMBER(3)
);

CREATE TYPE external_lecturer_typ UNDER lecturer_typ
(
  institution VARCHAR2(255),
  is_examiner CHAR(1) -- CONSTRAIN to 1 or 0
);

create table lecturer_tbl of lecturer_typ;

INSERT INTO lecturer_tbl VALUES(  lecturer_typ(1,'jack','parrow')  );
INSERT INTO lecturer_tbl VALUES(  lecturer_typ(2,'liz','taylor')  );

INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(51,'noddy','friend','cambridge','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(52,'big','ears','oxford','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(53,'mike','strutter','hull','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(54,'john','ross','brunel','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(55,'special','friend','berks','1') );

INSERT INTO lecturer_tbl VALUES ( module_leader_typ(31,'bollin','ceake','networking','full-time',30) );
INSERT INTO lecturer_tbl VALUES ( module_leader_typ(32,'biz','silovoxi','databases','full-time',15)  );
INSERT INTO lecturer_tbl VALUES ( module_leader_typ(33,'tony','blair','Web Technologies','part-time',3) );

Create table module_leaders_tbl
(
   the_leader   REF module_leader_typ
);

insert into module_leaders_tbl VALUES 
(
     (SELECT REF(l) FROM lecturer_tbl l where l.teacher_id = 31)
);

Answer 1

您需要将您的 lector_typ 转换为 module_leader_typ:

http://psoug.org/definition/TREAT.htm