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
