oracle – 是否有也许得到变量的最大也许长度

感谢各人

create or replace type t42 as object (
  id number
) not final;
/

create or replace type t42_sub under t42 (
  value varchar2(8),constructor function t42_sub(p_value in varchar2) return self as result,member function get_value return varchar2
);
/

create or replace type body t42_sub as 
  constructor function t42_sub(p_value in varchar2) return self as result is
  begin
    value := p_value;
    return;
  end t42_sub;

  member function get_value return varchar2 is
    l_attr_len number;
  begin
    select length into l_attr_len
    from user_type_attrs
    where type_name = 'T42_SUB'
    and attr_name = 'VALUE';

    return case when self.value is null then lpad(' ',l_attr_len,' ')
      else rpad(self.value,' ') end;
  end get_value;
end;
/

然后行使该范例给出：

with t as (
  select t42_sub('AA').get_value() as val from dual
  union all select t42_sub(null).get_value() as val from dual
)
select val,'<'|| val ||'>',length(val)
from t;

VAL             '<'||VAL||'>'   LENGTH(VAL)
--------------- --------------- -----------
AA              <AA      >                8 
                <        >                8

显然,您可以编写一个函数来获取范例/ attr_name的长度,而不是在每个成员函数中一再select.

我猜疑它会相等昂贵,除非你能想出一个缓存机制.假如工具是长命的,你可以在我以为的结构函数中举办查找：

create or replace type t42_sub under t42 (
  value varchar2(8),max_value_len number,member function get_value return varchar2
);
/

create or replace type body t42_sub as
  constructor function t42_sub(p_value in varchar2) return self as result is
  begin
    value := p_value;

    select length into max_value_len
    from user_type_attrs
    where type_name = 'T42_SUB'
    and attr_name = 'VALUE';
    return;
  end t42_sub;

  member function get_value return varchar2 is
  begin
    return case when self.value is null then lpad(' ',max_value_len,' ') end;
  end get_value;
end;
/

但它好像如故是你应该在源代码节制而不是在运行时处理赏罚的对象,要么在范例声明中明晰配置max_value_len：= 8(在值的旁边,以是你但愿它们都必要留意它们都必要变动),可能行使替代变量的建设剧本.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!