CREATE OR REPLACE FUNCTION -526互联

CREATE OR REPLACE FUNCTION "F_GETRANGE" (inpar_sex peis_item.forsex%Type,inpar_itemid peis_item.itemid%Type,inpar_hosnum peis_item.hosnum%Type)
return varchar2
is outpar_range varchar2(20);
temp_mminval number;
temp_mmaxval number;
temp_wminval number;
temp_wmaxval number;
begin
select i.mminval,i.mmaxval,i.wminval,i.wmaxval into temp_mminval,temp_mmaxval,temp_wminval,temp_wmaxval
from peis_item i where i.hosnum = inpar_hosnum and i.itemid = inpar_itemid;

--性别男
if inpar_sex = '1' then
if (temp_mminval is not null and temp_mmaxval is not null) then -- 上下限不为空
outpar_range := (rtrim(TO_CHAR(temp_mminval,'fm99990.9999'),'.')) || '-' || (rtrim(TO_CHAR(temp_mmaxval,'fm99990.9999'),'.'));
end if;
if (temp_mminval is null and temp_mmaxval is not null) then --下限为空，上限不为空
outpar_range := '<' || (rtrim(TO_CHAR(temp_mmaxval,'fm99990.9999'),'.'));
end if;
if (temp_mminval is not null and temp_mmaxval is null) then --上限为空，下限不为空
outpar_range := '>' || (rtrim(TO_CHAR(temp_mminval,'fm99990.9999'),'.'));
end if;
end if;
--性别女
if inpar_sex = '2' then
if (temp_wminval is not null and temp_wmaxval is not null) then -- 上下限不为空
outpar_range := (rtrim(TO_CHAR(temp_wminval,'fm99990.9999'),'.')) || '-' || (rtrim(TO_CHAR(temp_wmaxval,'fm99990.9999'),'.'));
end if;
if (temp_wminval is null and temp_wmaxval is not null) then --下限为空，上限不为空
outpar_range := '<' || (rtrim(TO_CHAR(temp_wmaxval,'fm99990.9999'),'.'));
end if;
if (temp_wminval is not null and temp_wmaxval is null) then --上限为空，下限不为空
outpar_range := '>' || (rtrim(TO_CHAR(temp_wminval,'fm99990.9999'),'.'));
end if;
end if;

return outpar_range;
end;