oracle 自定义比较函数

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;

程序员的基础教程：菜鸟程序员