oracle不像MySQL有两个内置的函数inet_aton IP字符串转换为整型 inet_ntoa整型转换为字符串,今天导数据的时候要用到,但是Oralce没有这样的内置函数,没办法只能自己动手写了,不废话了,直接贴代码了。
(1) IP字符串转换为整型
create or replace function inet_aton(strip in String) return integer
as
ip1 varchar2(10);
ip2 varchar2(10);
ip3 varchar2(10);
ip4 varchar2(10);
temp varchar2(20);
result integer;
begin
temp:=strip;
ip1:=SUBSTR(temp, 1,INSTR (temp, '.', 1,1)-1);
ip2:= substr(temp,INSTR (temp, '.', 1,1)+1,INSTR (temp, '.', 1,2)-length(ip1)-2 ) ;
ip3:= substr(temp,INSTR (temp, '.', 1,2)+1,INSTR (temp, '.', 1,3)-length(ip1)-length(ip2)-3 ) ;
ip4:= substr(temp,INSTR (temp, '.', 1,3)+1,length(temp)-length(ip1)-length(ip2)-4 ) ;
result:=to_number(ip1)*16777216+to_number(ip2)*65536+to_number(ip3)*256+to_number(ip4);
return(result);
end inet_aton;
(2)整型转换为字符串
create or replace function inet_ntoa(i_ip in integer) return varchar2
as
anNewIP integer:=i_ip;
InTmp integer;
lResult varchar2(30);
nIndex integer;
begin
-- Test statements here
for nIndex in reverse 0..3
loop
InTmp:=floor(anNewIP/power(256,nIndex));
lResult:=lResult||to_char(InTmp)||'.';
dbms_output.put_line(InTmp);
anNewIP:=anNewIP-(InTmp*power(256,nIndex));
end loop;
lResult:=substr(lResult,0,length(lResult)-1);
return lResult;
end inet_ntoa;