sql - 具有对象类型主键的PL/SQL

Question

我有如下创建对象关系类型。

CREATE OR REPLACE TYPE familycar_t AS OBJECT (
make VARCHAR (10),
model VARCHAR(10),
year NUMBER (4),
fuel_type VARCHAR (10));
/

CREATE OR REPLACE TYPE sedan_t AS OBJECT (
familycar ref familycar_t,
cylinder_Size  number(10));
/

CREATE TABLE familycar OF familycar_t (PRIMARY KEY (make,model));

现在，我想将“ Sedan”表设置为主。

CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar_t.make,familycar_t.model));

但是发生错误，


从第4行开始时出错：命令-CREATE TABLE sedan OF sedan_t
（主键（familycar_t.make，familycar_t.model））错误报告-SQL
错误：ORA-00904：“ FAMILYCAR_T”。“ MAKE”：无效的标识符
00904. 00000-“％s：无效的标识符”
*原因：
*行动：


有什么解决方案？

Answer 1

使用引用的另一种方法是使用继承：

CREATE OR REPLACE TYPE familycar_t AS OBJECT (
  make VARCHAR (10),
  model VARCHAR(10),
  year NUMBER (4),
  fuel_type VARCHAR (10)
) NOT FINAL;
/

CREATE OR REPLACE TYPE sedan_t UNDER familycar_t (
  cylinder_Size  number(10)
);
/

CREATE TABLE familycar OF familycar_t (PRIMARY KEY (make,model));

CREATE TABLE sedan OF sedan_t (PRIMARY KEY (make,model));

但是，在这种情况下，您实际上并不需要 sedan表：

INSERT INTO familycar
SELECT sedan_t( 'Ford', 'Model-T', 1908, 'Petrol', 4 ) FROM DUAL UNION ALL
SELECT familycar_t( 'Ford', 'Model-A', 1903, 'Petrol' ) FROM DUAL;

SELECT f.*,
       TREAT( VALUE(f) AS sedan_t ).cylinder_size AS cylinder_size
FROM   familycar f;

输出：

MAKE       MODEL            YEAR FUEL_TYPE  CYLINDER_SIZE
---------- ---------- ---------- ---------- -------------
Ford       Model-T          1908 Petrol                 4 
Ford       Model-A          1903 Petrol            (null)