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;
转载于:https://www.cnblogs.com/siyunianhua/p/6294184.html
相关资源：握奇读卡器 X64驱动
