首页 > 代码库 > 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 自定义比较函数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。