CREATE OR REPLACE FUNCTION gettwhealthins
(
P_SALARY IN NUMBER,
P_PSNNUM IN NUMBER,
P_PSNTYPE IN NUMBER,
P_MTYPE IN NUMBER,
P_PK_ORG IN CHAR
)
RETURN VARCHAR2 IS result NUMBER(31,8);
BEGIN
result := 0.00;
if(P_SALARY > 0.00) then
select nvl(tempnum,0.00) into result from (
SELECT decode(P_MTYPE,
0, hr_twhealthins.monthins,
1, decode(P_PSNNUM,
0, hr_twhealthins.family0,
1, hr_twhealthins.family1,
2, hr_twhealthins.family2,
3, hr_twhealthins.family3,
4, hr_twhealthins.family4,
5, hr_twhealthins.family5),
2, hr_twhealthins.subsidy,
3, hr_twhealthins.amountins) tempnum
from hr_twhealthins
where hr_twhealthins.type = P_PSNTYPE
and hr_twhealthins.pk_org = (select pk_org from org_orgs where code = P_PK_ORG and nvl(dr,0)=0 and rownum = 1)
and nvl(hr_twhealthins.dr, 0) = 0
-- 按最小绝对值方式
--order by abs(monthins-P_SALARY) asc, monthins desc) temp where rownum = 1;
-- 按大于等于薪资的最小值方式
and monthins >= P_SALARY
order by monthins asc) temp where rownum = 1;
else
result := 0.00;
end if;
RETURN result;
END;
(
P_SALARY IN NUMBER,
P_PSNNUM IN NUMBER,
P_PSNTYPE IN NUMBER,
P_MTYPE IN NUMBER,
P_PK_ORG IN CHAR
)
RETURN VARCHAR2 IS result NUMBER(31,8);
BEGIN
result := 0.00;
if(P_SALARY > 0.00) then
select nvl(tempnum,0.00) into result from (
SELECT decode(P_MTYPE,
0, hr_twhealthins.monthins,
1, decode(P_PSNNUM,
0, hr_twhealthins.family0,
1, hr_twhealthins.family1,
2, hr_twhealthins.family2,
3, hr_twhealthins.family3,
4, hr_twhealthins.family4,
5, hr_twhealthins.family5),
2, hr_twhealthins.subsidy,
3, hr_twhealthins.amountins) tempnum
from hr_twhealthins
where hr_twhealthins.type = P_PSNTYPE
and hr_twhealthins.pk_org = (select pk_org from org_orgs where code = P_PK_ORG and nvl(dr,0)=0 and rownum = 1)
and nvl(hr_twhealthins.dr, 0) = 0
-- 按最小绝对值方式
--order by abs(monthins-P_SALARY) asc, monthins desc) temp where rownum = 1;
-- 按大于等于薪资的最小值方式
and monthins >= P_SALARY
order by monthins asc) temp where rownum = 1;
else
result := 0.00;
end if;
RETURN result;
END;