oracle 自定义比较函数

最新推荐文章于 2022-09-15 17:40:34 发布

转载最新推荐文章于 2022-09-15 17:40:34 发布 · 182 阅读

文章标签：

#数据库

本文介绍了一个自定义的比较函数，该函数能够处理各种比较条件（如大于、小于、等于及范围），输入均为字符串形式，并返回布尔结果。适用于需要灵活比较逻辑的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1>自定义比较函数，targetVal的值为字符串，例如：“>=90”,"2~8"等范围格式，dataVal值为字符串。

create or replace function compare1(targetVal in varchar2,
                                    dataVal   in varchar2) return integer is
  v_Result      integer;
  dataVal_int   number;
  targetVal_int number;
  v_targetVal   varchar2(200);
  v_dataVal     varchar2(200);
  v_flag        boolean;
  v_index       integer;
begin
  v_flag      := true;
  v_targetVal := replace(targetVal, ' ');
  v_dataVal   := replace(dataVal, ' ');
  dataVal_int := to_number(v_dataVal);
  if instr(v_targetVal, '>=') > 0 then
    v_targetVal   := replace(v_targetVal, '>=');
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int >= targetVal_int;
  elsif instr(v_targetVal, '>') > 0 then
    v_targetVal   := replace(v_targetVal, '>');
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int > targetVal_int;
  elsif instr(v_targetVal, '<=') > 0 then
    v_targetVal   := replace(v_targetVal, '<=');
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int <= targetVal_int;
  elsif instr(v_targetVal, '<') > 0 then
    v_targetVal   := replace(v_targetVal, '<');
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int < targetVal_int;
  elsif instr(v_targetVal, '~') > 0 then
    v_index := instr(v_targetVal, '~');
    v_flag  := dataVal_int <= to_number(substr(v_targetVal, v_index + 1)) and
               dataVal_int >=
               to_number(substr(v_targetVal, 1, v_index - 1));
  elsif instr(v_targetVal, '=') > 0 then
    v_targetVal   := replace(v_targetVal, '=');
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int = targetVal_int;
  elsif instr(v_targetVal, '=') = 0 then
    targetVal_int := to_number(v_targetVal);
    v_flag        := dataVal_int = targetVal_int;  
  end if;
  if v_flag then
    v_Result := 1;
  else
    v_Result := 0;
  end if;
  return(v_Result);
exception
  when others then
    return 1;
end compare1;