oracle – 是否有可能获得变量的最大可能长度

副标题#e#

我想知道在plsql中是否存在给出变量最大长度的函数.

例如,如果我声明

DECLARE

varia VARCHAR2(7)
BEGIN
   call of a function that would return 7
END

即使varia为null,我也可以得到varchar的长度为7.

—例如

create or replace 
TYPE ENREG_320_03 UNDER ENREG_320_BASE(
date_creation VARCHAR2(8),raison_sociale_emetteur VARCHAR2(35),adresse_emetteur_1 VARCHAR2(35),adresse_emetteur_2 VARCHAR2(35),adresse_emetteur_3 VARCHAR2(35),num_siret VARCHAR2(14),ref_remise VARCHAR2(16),code_bic_emetteur VARCHAR2(11),type_ident_compte_debit VARCHAR2(1),ident_compte_debit VARCHAR2(34),code_devise_compte_debit VARCHAR2(3),ident_client VARCHAR2(16),type_ident_compte_frais VARCHAR2(1),ident_compte_frais VARCHAR2(34),code_devise_compte_frais VARCHAR2(3),zone_reserve VARCHAR2(16),indice_type_debit_remise VARCHAR2(1),indice_type_remise  VARCHAR2(1),date_execution_souhait VARCHAR2(8),devise_transfert VARCHAR2(3),MEMBER FUNCTION get_date_creation RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.date_creation IS NULL THEN lpad(' ',8,' ') ELSE   rpad(SELF.date_creation,' ') END;
END get_date_creation;

MEMBER FUNCTION get_raison_sociale_emetteur RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.raison_sociale_emetteur IS NULL THEN lpad(' ',35,' ') ELSE rpad(SELF.raison_sociale_emetteur,' ') END;
END get_raison_sociale_emetteur;

MEMBER FUNCTION get_adresse_emetteur_1 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_1 IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.adresse_emetteur_1,' ') END;
END get_adresse_emetteur_1;

MEMBER FUNCTION get_adresse_emetteur_2 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_2 IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.adresse_emetteur_2,' ') END;
END get_adresse_emetteur_2;

MEMBER FUNCTION get_adresse_emetteur_3 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_3 IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.adresse_emetteur_3,' ') END;
END get_adresse_emetteur_3;

MEMBER FUNCTION get_num_siret RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.num_siret IS NULL THEN lpad(' ',14,' ') ELSE rpad(SELF.num_siret,' ') END;
END get_num_siret;

MEMBER FUNCTION get_ref_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ref_remise IS NULL THEN lpad(' ',16,' ') ELSE rpad(SELF.ref_remise,' ') END;
END get_ref_remise;

MEMBER FUNCTION get_code_bic_emetteur RETURN VARCHAR2 AS
BEGIN
   RETURN CASE WHEN SELF.code_bic_emetteur IS NULL THEN lpad(' ',11,' ') ELSE rpad(SELF.code_bic_emetteur,' ') END;
END get_code_bic_emetteur;

MEMBER FUNCTION get_type_ident_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.type_ident_compte_debit IS NULL THEN lpad(' ',1,' ') ELSE rpad(SELF.type_ident_compte_debit,' ') END;
END get_type_ident_compte_debit;

MEMBER FUNCTION get_ident_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_compte_debit IS NULL THEN lpad(' ',34,' ') ELSE rpad(SELF.ident_compte_debit,' ') END;
END get_ident_compte_debit;

MEMBER FUNCTION get_code_devise_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.code_devise_compte_debit IS NULL THEN lpad(' ',3,' ') ELSE rpad(SELF.code_devise_compte_debit,' ') END;
END get_code_devise_compte_debit;

MEMBER FUNCTION get_ident_client RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_client IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.ident_client,' ') END;
END get_ident_client;

MEMBER FUNCTION get_type_ident_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.type_ident_compte_frais IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.type_ident_compte_frais,' ') END;
END get_type_ident_compte_frais;

MEMBER FUNCTION get_ident_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_compte_frais IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.ident_compte_frais,' ') END;
END get_ident_compte_frais;

MEMBER FUNCTION get_code_devise_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.code_devise_compte_frais IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.code_devise_compte_frais,' ') END;
END get_code_devise_compte_frais;

MEMBER FUNCTION get_zone_reserve RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.zone_reserve IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.zone_reserve,' ') END;
END get_zone_reserve;

MEMBER FUNCTION get_indice_type_debit_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.indice_type_debit_remise IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.indice_type_debit_remise,' ') END;
END get_indice_type_debit_remise;

MEMBER FUNCTION get_indice_type_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.indice_type_remise IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.indice_type_remise,' ') END;
END get_indice_type_remise;

MEMBER FUNCTION get_date_execution_souhait RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.date_execution_souhait IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.date_execution_souhait,' ') END;
END get_date_execution_souhait;

MEMBER FUNCTION get_devise_transfert RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.devise_transfert IS NULL THEN lpad(' ',' ') ELSE rpad(SELF.devise_transfert,' ') END;
END get_devise_transfert;

所以我想知道是否存在一个简单的函数来获得字段的大小所以我不必在getter中使用数字：如果字段的大小改变了,就没有必要更改getter,它会工作无论varchar的大小

但我明白这是不可能的

#p#副标题#e##p#分页标题#e#

谢谢大家

在您的特定用例中,由于您是在类型中而不是在匿名块或存储过程中执行此操作,因此您可以从user_type_attrs视图中获取信息：

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(在值的旁边,所以你希望它们都需要注意它们都需要更改),或者使用替换变量的创建脚本.