最近在开发的时候,有这样一种需求:
有一个字段名:AREAID, 是18位的整形。
即: AREAID NUMBER(18)
有如下的值:
AREAID
---------------------------------------
100938053200000000
100938053300000000
100938053400000000
100938053500000000
100938053600000000
100938060100000000
100938060200000000
100938060300000000
100938031100000000
要用以上的值,构造一个Where 条件:
如:AREAID = 100938053200000000
要生成: AREAID >=100938053200000000 AND AREAID <= 100938053299999999
也就是要把后面连续的‘0’换成‘9’
实现方法如下:
create or replace function fun_get_area_where(Aareaid in number)
return varchar2 is
Result varchar2(256);
m_areaid varchar2(18);
begin
if Aareaid is null or to_number(Aareaid) = 0 then
return '';
else
m_areaid := to_char(Aareaid);
end if;
m_areaid := trim('0' from m_areaid);
Result := ' areaid >= ' || Aareaid || ' and areaid <=' ||
rpad(m_areaid, 18, '9');
return(Result);
end fun_get_area_where;