首页 > 代码库 > oracle 自定义比较函数

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;

 

oracle 自定义比较函数