数据库动态添加大字段设计key：value

针对灵活的业务配置需求，固定的字段无法满足多变的业务类型变化。因此需要通过大字段数据库设计，通过键值方式处理。

字段存储格式：{B01:100,B02:2.45}

=================读取字段键值对应数据方法：f_getvalue=============================

create or replace function f_getvalue(fld IN VARCHAR2,ipath IN VARCHAR2) RETURN VARCHAR2 is
       res VARCHAR2(200);
       findChar varchar2(50);
       findIndex integer;
begin
   if fld is null then
         res := '';
   else
      if instr(fld,ipath) = 0 then
         res := '';
      else
         findChar := ',';
         findIndex := instr(fld, ',',instr(fld, ipath || ':'));
         if findIndex = 0 then
            findChar := '}';
         end if;
         res := substr(fld,instr(fld, ipath || ':')
             + length(ipath || ':'),instr(fld,findChar,instr(fld, ipath || ':'))
             - (instr(fld, ipath || ':')
             + length(ipath || ':')));
       end if;
    end if;
return(res);
end f_getvalue;

==============存储数据方法：f_setvalue===========================

create or replace function f_setvalue(fld IN VARCHAR2,ipath IN VARCHAR2,value in varchar2) RETURN VARCHAR2 is
res VARCHAR2(4000);
val number(20,8);
begin
  val := to_number(value);
if fld is null then
    if val=0 then
      res :='';
     else
      res := '{' || ipath || ':' || value || '}';
    end if ;
else
  if instr(fld,ipath) <> 0 then
     if instr(fld, ',',instr(fld, ipath || ':')) <> 0 then
       if val=0 then
         res := substr(fld,0,instr(fld,ipath) -1 )
               || substr(fld,
                  instr(fld,',',instr(fld, ipath|| ':') ) + 1
               );
        else
           res := substr(fld,0,instr(fld,ipath) + length(ipath))
               || value || substr(fld,
                  instr(fld,',',instr(fld, ipath|| ':'))
               );
         end if;
     else
       if val=0 then
           res :=substr(fld,0,instr(fld,ipath) - 2 );
           if length(res) > 2 then
              res:= res  || '}';
            end if;
        else
           res := substr(fld,0,instr(fld,ipath) + length(ipath))|| value || '}';
        end if ;

     end if;
  else
    if val =0 then
        res := fld;
      else
       res := substr(fld,0,length(fld) - 1) || ',' || ipath || ':' || value || '}';
      end if ;
  end if;
end if;
return(res);