时间戳

首页 > 解决方案 > 无法在 Oracle 11g 上将对象类型插入表中

database - 无法在 Oracle 11g 上将对象类型插入表中

问题描述

  CREATE OR REPLACE TYPE CURSOR AS OBJECT(
    code varchar2(20),
    className varchar2(30),
    teach1 Teacher,
    maximumPupils number,
    maximumHours number,

    CONSTRUCTOR FUNCTION cursor(code varchar2, className varchar2, passport Teacher, maximumPupils number, maximumHours number) RETURN SELF AS RESULT


 )NOT FINAL;



CREATE OR REPLACE TYPE BODY CURSOR
AS
CONSTRUCTOR FUNCTION cursor(code varchar2, className varchar2, passport Teacher, maximumPupils number, maximumHours number) RETURN SELF AS RESULT
IS
BEGIN 
    SELF.code := code;
    SELF.className := className;
    SELF.passport:=passport;
    SELF.maximumPupils := maximumPupils;
    SELF.maximumHours := maximumHours;


END;


------------------------

CREATE TABLE SubjectClass OF Cursor;


-----------------------------------------


BEGIN
    insert into SubjectClass values(1,'Class 1','43345678F',30,55);


END;

我已经尝试了以下所有方法（包括使用完整的构造函数引用），仅使用我愿意插入或引用存在的另一个表的教师对象的属性 .passport（此护照号以前存在于另一个表中）称为教师的对象类型表）：

插入 SubjectClass VALUES('1','Class 1', Teacher.passport('12345678A'),20,25);

插入 SubjectClass 值（1，'Class 1'，'43345678F'，30,55）；

请帮忙，我马上要考试了。

标签： databaseoracleobjecttypesoracle11g

解决方案

假设您有以下类型：

create or replace type teacher as object (
  passport  varchar2( 32 )
, something varchar2( 64 )
, another   varchar2( 64 )
)
/

create or replace type cursor_ as object (
  code varchar2(20)
, className varchar2(30)
, teacher_ teacher
, maximumPupils number
, maximumHours number
, constructor function cursor_ (
    code varchar2
  , className varchar2
  , teacher_ teacher
  , maximumPupils number
  , maximumHours number
  ) return self as result
) not final 
/

cursor_ 的 TYPE BODY 的代码可能类似于 ...

create or replace type body cursor_
as
  constructor function cursor_ (
    code varchar2
  , className varchar2
  , teacher_ teacher
  , maximumPupils number
  , maximumHours number
  ) return self as result
  as
  begin
    self.code := code ;
    self.className := className ;
    self.teacher_ := teacher_ ;
    self.maximumPupils := maximumPupils ;
    self.maximumHours := maximumHours ; 
    return ;
  end; 
end;
/

请注意，TYPE BODY 有 2 个“end;” 线。由于缺少一个“结束”这一事实，您的原始类型正文代码编译时出错。

现在您可以为 cursor_ 对象创建一个表，并插入一些测试数据：

create table subjectclass of cursor_ ;

begin
  insert into subjectclass values (
    new cursor_ ( 1, 'class1', new teacher( 'AA1100BB', 'something1', 'another1' ), 100, 16 ) 
  );
  insert into subjectclass values (
    new cursor_ ( 2, 'class2', new teacher( 'BB2211CC', 'something2', 'another2' ), 120, 25 ) 
  );
end ;
/

subjectclass 表的示例查询：

 select 
  S.code
, S.classname
, S.teacher_.passport  as T_PP
, S.teacher_.something as T_ST
, S.teacher_.another   as T_AO
, S.maximumpupils as max_p
, S.maximumhours  as max_h
from subjectclass S ;

-- result
CODE  CLASSNAME  T_PP      T_ST        T_AO      MAX_P  MAX_H  
1     class1     AA1100BB  something1  another1  100    16     
2     class2     BB2211CC  something2  another2  120    25

请参阅此处的dbfiddle 。

推荐阅读