strFieldByName

CREATE OR REPLACE FUNCTION strFieldByName(sRow        VARCHAR2,
                                          sFieldName  VARCHAR2,
                                          sFieldMask   VARCHAR2,
                                          sValueMask  VARCHAR)
  RETURN VARCHAR2 IS
  pvar_i            INT;
  pvar_S            VARCHAR2(4000);
  pvar_Result       VARCHAR2(4000);
  pvar_count        int;
  pvar_LenFieldName integer;
  pvar_sub          varchar2(4000);
  pvar_sRow         varchar2(4000);
  pvar_stmp         varchar2(4000);
  pvar_iCopyCount   int;
BEGIN
  pvar_i    := 0;
  pvar_sRow := sRow;
  pvar_sub  := substr(pvar_sRow, 0, 1);
  if pvar_sub <> nvl(sFieldMask, ';') then
    pvar_sRow := nvl(sFieldMask, ';') || sRow;
  end if;
 
  pvar_sub := substr(pvar_sRow, Length(sRow), 1);
  if pvar_sub <> nvl(sFieldMask, ';') then
    pvar_sRow := pvar_sRow || nvl(sFieldMask, ';');
  end if;
 
  pvar_stmp         := nvl(sFieldMask, ';') || sFieldName ||
                       nvl(sValueMask, '=');
  pvar_LenFieldName := instr(lower(pvar_sRow), lower(pvar_stmp)); --fieldname不分大小写
  if pvar_LenFieldName > 0 then
    pvar_iCopyCount := Instr(pvar_sRow,
                             nvl(sFieldMask, ';'),
                             pvar_LenFieldName + Length(pvar_stmp));
   
    --找到这个field的最后一个;号
    pvar_i := Instr(pvar_sRow, nvl(sValueMask, '='), pvar_iCopyCount + 1);
    while (Instr(pvar_sRow, nvl(sFieldMask, ';'), pvar_iCopyCount + 1) between 1 and
          pvar_i - 1) loop
      pvar_iCopyCount := Instr(pvar_sRow,
                               nvl(sFieldMask, ';'),
                               pvar_iCopyCount + 1);
    end loop;
   
    pvar_Result := substr(pvar_sRow,
                          pvar_LenFieldName + Length(pvar_stmp),
                          pvar_iCopyCount - Length(pvar_stmp) -
                          pvar_LenFieldName);
   
    goto break;
  end if;
  <<break>>
  RETURN pvar_result;
END strfieldbyname;