握草

CREATE OR REPLACE FUNCTION fn_GetClassifyBymjazfs (v_azfs varchar2,v_mx varchar2,v_mj varchar2)
return varchar2
is
v_Classify varchar2(2000);
v_unit lyxxb.lyid%type;
v_ysmx varchar2(50);
SQL_GetUnit varchar2(2000);
SQL_Getmx varchar2(2000);
begin
--参数V_MX在有艺术墓时输入墓型，没有时输入-
-- ≤0.5㎡    50
--  0.5＜且≤1㎡   60
--  1＜且≤2㎡   70
--  2＜且≤3㎡   80
--  双0.5   90
--  艺术墓  100
--  节地生态    110  双0.5 +无墓碑生态+其它生态安葬方式
--  立体安葬    120   骨灰墙廊亭 骨灰深埋
--SELECT * FROM AZFS
SQL_GetUnit:='SELECT lyid FROM lyxxb';
SQL_Getmx:='SELECT count(*) FROM spmldm WHERE  spmc='''||v_mx||''' ';
execute immediate SQL_GetUnit into v_unit;--执行oracle 方法
execute immediate SQL_Getmx into v_ysmx;--执行oracle 方法
-- 太子峪
 if  v_unit='TZY' then  
    begin
       if v_azfs='01' AND v_mj<=0.5  AND v_mx<>'福荣墓'and v_mx <>'福和墓'  then v_Classify:='50'; return v_Classify;
        elsif v_azfs='01' AND v_mj>0.5 AND v_mj<=1   then v_Classify:='60'; return v_Classify;
        elsif v_azfs='01' AND v_mj>1 AND v_mj<=2  then v_Classify:='70'; return v_Classify;
        elsif v_azfs='01' AND v_mj>2 AND v_mj<=3 then v_Classify:='80'; return v_Classify;        
        elsif v_azfs='01'  AND (v_mx='福荣墓' or v_mx ='福和墓')  AND v_mj<=0.5  then v_Classify:='90'; return v_Classify;
        elsif v_ysmx<>1  then v_Classify:='100'; return v_Classify;
        elsif  (v_azfs='20' and v_mx='人生之树')  then v_Classify:='110'; return v_Classify;
        elsif v_azfs='02' OR v_azfs='03' then v_Classify:='120'; 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_GetClassifyBymjazfs;
/

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