oracle if else 判断

CREATE OR REPLACE FUNCTION fn_GetClassifyBymxmjazfs (v_azfs varchar2,v_mx varchar2,v_mj varchar2)
return varchar2
is
v_Classify varchar2(2000);
v_unit lyxxb.lyid%type;
SQL_GetUnit varchar2(2000);

begin

SQL_GetUnit:='SELECT lyid FROM lyxxb';

execute immediate SQL_GetUnit into v_unit;--执行oracle 方法

-- AZFS - '01'
   -- 判断墓型或者面积不属于双0.5  10
-- azfs = '骨灰林  20
-- azfs = 骨灰墙  骨灰郎 骨灰庭  30
-- azfs = '01'  且墓型或面积属于双0.5   41
-- azfs = '20'   42 
--SELECT * FROM AZFS
-- 太子峪
 if  v_unit='TZY' then  
    begin
       if v_azfs='01' AND v_mx <>'福荣墓' then v_Classify:='10'; return v_Classify;
        elsif v_azfs='04'  then v_Classify:='20'; return v_Classify;
        elsif v_azfs='02' OR v_azfs='03' then v_Classify:='30'; return v_Classify;
        elsif v_azfs='01'  AND v_mx='福荣墓' then v_Classify:='41'; return v_Classify;        
        elsif v_azfs='20'  then v_Classify:='42'; return v_Classify;
       end if;
    end;
  --金山
 elsif v_unit='JS' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
   --温泉
   elsif v_unit='WQMY' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;    
 --八达岭
 elsif v_unit='BDL' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
   --朝阳
  elsif v_unit='CYLY' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
    --宝云岭
   elsif v_unit='BYL' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
    --天慈
  elsif v_unit='TC' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
    --通惠
   elsif v_unit='THLY' then
  begin
        v_Classify:='10';
        return v_Classify;
    end;
  end if;
--return v_Classify;

exception
  when others then dbms_output.put_line('error');
  return -1;
end fn_GetClassifyBymxmjazfs;
/

SELECT fn_GetClassifyBymxmjazfs('01','福荣墓1','0.48') FROM dual;

 

转载于:https://www.cnblogs.com/siyunianhua/p/6290949.html