在Oracle 12c中链接来自不同对象类型的实体的值

让我们假设我们有以下类型:

CREATE TYPE customer_rec_tp AS OBJECT
    ( customer_id   NUMBER(10)
    , first_name    VARCHAR2(20)
    , last_name     VARCHAR2(20)
    , store         VARCHAR2(50)
    , err_msg       VARCHAR2(15)
    ) ;
/

CREATE TYPE student_rec_tp AS OBJECT
    ( student_ID    NUMBER(8)
    , first_name    VARCHAR2(20)
    , last_name     VARCHAR2(20)
    , school        VARCHAR2(20)
    , grade         VARCHAR2(10)
    , phone_number  VARCHAR2(15)
    , email         VARCHAR2(30)
    , err_msg       VARCHAR2(15)
    ) ;
/

现在，我们根据上述类型声明两个变量:

v_cstmr customer_rec_tp;
v_stdnt student_rec_tp;

问题是我们是否可以以某种方式将

v_cstmr.err_msg

到

v_stdnt.err_msg

？

The actual problem that I have come across is a little more complex than the scenario explained above. I have created overloading procedure which can take different inputs [object types] as IN OUT parameter. It processes all objects, which all have an entity named err_msg. The procedure appends a text message到err_msg if it comes across an error and sends the object back. What I hope到find is a way that any changes in the err_msg entity in an objects updates the others as well automatically. I understand that I can use a global variable到do this, but I would like到avoid it as much as possible and directly link the object entities from different types together.

I understand that it is an object oriented concept which is not the strongest feature of PL/SQL, but still there might be a way到use object type inheritance from a parent type到pass err_msg. Any ideas？

你有单独的变量。如果要将错误消息从一个复制到另一个，则需要自己分配。类型继承不会解决这个问题:

create or replace type super_t as object (
  err_msg varchar2(10)
) not final;
/

create or replace type sub_t under super_t (
  sub int
) ;
/

declare
  vsuper super_t := super_t(null);
  vsub   sub_t   := sub_t(null, 1);
begin
  vsub.err_msg := 'message';
  dbms_output.put_line('SUB: ' || vsub.err_msg);
  dbms_output.put_line('SUPER: ' || vsuper.err_msg);
  
  vsuper.err_msg := vsub.err_msg;
  
  dbms_output.put_line('SUB: ' || vsub.err_msg);
  dbms_output.put_line('SUPER: ' || vsuper.err_msg);
end;
/

SUB: message
SUPER: 
SUB: message
SUPER: message

不过，我觉得你的做法是错误的。如果你有一个全局的属性，你应该使用一个全局变量。

如果要 “链接” 类型的实例，则可以:

-从类型中删除err_msg
-创建具有错误消息和其他对象作为属性的记录或对象类型

这允许您创建一个保存所有数据的单个变量。例如:

CREATE TYPE customer_rec_tp AS OBJECT
    ( customer_id   NUMBER(10)
    , first_name    VARCHAR2(20)
    , last_name     VARCHAR2(20)
    , store         VARCHAR2(50)
    ) ;
/

CREATE TYPE student_rec_tp AS OBJECT
    ( student_ID    NUMBER(8)
    , first_name    VARCHAR2(20)
    , last_name     VARCHAR2(20)
    , school        VARCHAR2(20)
    , grade         VARCHAR2(10)
    , phone_number  VARCHAR2(15)
    , email         VARCHAR2(30)
    ) ;
/

declare
  type trec is record (
    err_msg  varchar2(15),
    customer customer_rec_tp,
    student  student_rec_tp 
  );
  
  rec1 trec ;
  rec2 trec ;
begin
  rec1.err_msg  := 'error';
  
  dbms_output.put_line('REC1: ' || rec1.err_msg);
  dbms_output.put_line('REC2: ' || rec2.err_msg);
end;
/

REC1: error
REC2: 

但是如图所示，您仍然可以有 “master” 变量的单独实例...