PLSQL通用父类型问题：能否定义超类复用子类型的构造与成员过程？

核心结论 #

PLSQL 对象类型的继承规则不允许子类型重定义超类型已声明的属性（包括修改属性的长度、数据类型等），所以你无法直接通过定义超类型 OT_SUPER 并让子类型覆盖 RAW 和 INTERNAL 的长度来实现需求。不过可以通过以下两种方案实现公共逻辑的复用：

方案一：公共逻辑封装到 PL/SQL 包，子类型独立定义属性 #

将构造函数和成员过程的通用逻辑抽离到独立包中，各个子类型仅定义自身的属性长度，然后调用包内的逻辑实现方法。这种方案能在编译期严格控制属性长度，逻辑复用性强。

步骤 1：创建封装通用逻辑的包 #

create or replace package PKG_CONVERSION_LOGIC is
  -- 通用转换：INTERNAL -> RAW
  procedure convert_to_raw(p_internal in varchar2, p_raw out varchar2);
  -- 通用转换：RAW -> INTERNAL
  procedure convert_to_internal(p_raw in varchar2, p_internal out varchar2);
end PKG_CONVERSION_LOGIC;
/

create or replace package body PKG_CONVERSION_LOGIC is
  procedure convert_to_raw(p_internal in varchar2, p_raw out varchar2) is
  begin
    -- 替换为你的实际转换逻辑
    p_raw := substr(p_internal, 1, length(p_internal));
  end;
  
  procedure convert_to_internal(p_raw in varchar2, p_internal out varchar2) is
  begin
    -- 替换为你的实际转换逻辑
    p_internal := substr(p_raw, 1, length(p_raw));
  end;
end PKG_CONVERSION_LOGIC;
/

步骤 2：定义子类型（以 OT_001 为例） #

create or replace type OT_001 force as object (
   RAW         varchar2(3),
   INTERNAL    varchar2(3),

   constructor function OT_001 (
      RAW       in  varchar2
   ) return self as result,
   constructor function OT_001 (
      INTERNAL  in  varchar2
   ) return self as result,
   member procedure convert_to_raw_value,
   member procedure convert_to_internal_value
);
/

create or replace type body OT_001 is
  constructor function OT_001 (RAW in varchar2) return self as result is
  begin
    self.RAW := RAW;
    PKG_CONVERSION_LOGIC.convert_to_internal(RAW, self.INTERNAL);
    return;
  end;
  
  constructor function OT_001 (INTERNAL in varchar2) return self as result is
  begin
    self.INTERNAL := INTERNAL;
    PKG_CONVERSION_LOGIC.convert_to_raw(INTERNAL, self.RAW);
    return;
  end;
  
  member procedure convert_to_raw_value is
  begin
    PKG_CONVERSION_LOGIC.convert_to_raw(self.INTERNAL, self.RAW);
  end;
  
  member procedure convert_to_internal_value is
  begin
    PKG_CONVERSION_LOGIC.convert_to_internal(self.RAW, self.INTERNAL);
  end;
end;
/

步骤 3：定义其他子类型（如 OT_002） #

只需修改属性的长度，其余代码完全复用包逻辑：

create or replace type OT_002 force as object (
   RAW         varchar2(5),
   INTERNAL    varchar2(5),

   constructor function OT_002 (
      RAW       in  varchar2
   ) return self as result,
   constructor function OT_002 (
      INTERNAL  in  varchar2
   ) return self as result,
   member procedure convert_to_raw_value,
   member procedure convert_to_internal_value
);
/

create or replace type body OT_002 is
  constructor function OT_002 (RAW in varchar2) return self as result is
  begin
    self.RAW := RAW;
    PKG_CONVERSION_LOGIC.convert_to_internal(RAW, self.INTERNAL);
    return;
  end;
  
  constructor function OT_002 (INTERNAL in varchar2) return self as result is
  begin
    self.INTERNAL := INTERNAL;
    PKG_CONVERSION_LOGIC.convert_to_raw(INTERNAL, self.RAW);
    return;
  end;
  
  member procedure convert_to_raw_value is
  begin
    PKG_CONVERSION_LOGIC.convert_to_raw(self.INTERNAL, self.RAW);
  end;
  
  member procedure convert_to_internal_value is
  begin
    PKG_CONVERSION_LOGIC.convert_to_internal(self.RAW, self.INTERNAL);
  end;
end;
/

方案二：抽象超类型 + 运行时长度校验 #

定义抽象超类型封装所有公共逻辑，属性使用 varchar2(4000)（PLSQL 最大长度），子类型通过实现抽象方法完成长度校验。这种方案代码重复度最低，但只能在运行期控制属性长度。

步骤 1：创建抽象超类型 #

create or replace type OT_SUPER abstract as object (
   RAW         varchar2(4000),
   INTERNAL    varchar2(4000),

   constructor function OT_SUPER (
      RAW       in  varchar2
   ) return self as result,
   constructor function OT_SUPER (
      INTERNAL  in  varchar2
   ) return self as result,
   member procedure convert_to_raw_value,
   member procedure convert_to_internal_value,
   -- 抽象方法：子类型实现长度校验
   member function validate_length return boolean abstract
) not final;
/

create or replace type body OT_SUPER is
  constructor function OT_SUPER (RAW in varchar2) return self as result is
  begin
    self.RAW := RAW;
    -- 通用转换逻辑
    self.INTERNAL := substr(RAW, 1, length(RAW));
    -- 校验长度
    if not self.validate_length then
      raise_application_error(-20001, '属性长度不符合子类型要求');
    end if;
    return;
  end;
  
  constructor function OT_SUPER (INTERNAL in varchar2) return self as result is
  begin
    self.INTERNAL := INTERNAL;
    -- 通用转换逻辑
    self.RAW := substr(INTERNAL, 1, length(INTERNAL));
    -- 校验长度
    if not self.validate_length then
      raise_application_error(-20001, '属性长度不符合子类型要求');
    end if;
    return;
  end;
  
  member procedure convert_to_raw_value is
  begin
    -- 通用转换逻辑
    self.RAW := substr(self.INTERNAL, 1, length(self.INTERNAL));
    -- 校验长度
    if not self.validate_length then
      raise_application_error(-20001, '转换后属性长度不符合要求');
    end if;
  end;
  
  member procedure convert_to_internal_value is
  begin
    -- 通用转换逻辑
    self.INTERNAL := substr(self.RAW, 1, length(self.RAW));
    -- 校验长度
    if not self.validate_length then
      raise_application_error(-20001, '转换后属性长度不符合要求');
    end if;
  end;
end;
/

步骤 2：定义子类型（以 OT_001 为例） #

只需实现长度校验的抽象方法：

create or replace type OT_001 under OT_SUPER (
  overriding member function validate_length return boolean
);
/

create or replace type body OT_001 is
  overriding member function validate_length return boolean is
  begin
    return length(self.RAW) <= 3 and length(self.INTERNAL) <= 3;
  end;
end;
/

步骤 3：定义其他子类型（如 OT_002） #

create or replace type OT_002 under OT_SUPER (
  overriding member function validate_length return boolean
);
/

create or replace type body OT_002 is
  overriding member function validate_length return boolean is
  begin
    return length(self.RAW) <= 5 and length(self.INTERNAL) <= 5;
  end;
end;
/

方案对比 #

总结 #

如果需要严格的编译期长度约束，优先选择方案一；如果追求最低的代码重复度，且能接受运行期校验的开销，可以选择方案二。

内容的提问来源于stack exchange，提问作者dps_kane