[mw_shl_code=sql,true]
——————————————————————–
–ip转换为数字函数
——————————————————————–
create or replace function fun_ipconvernum(v_ip in varchar2)
return number
as
v_tmp varchar2(2);
v_all varchar2(8);
v_tow varchar2(3);
v_chkn number;
v_chkt number;
begin
v_tmp:=”;v_chkn:=0;v_chkt:=1;v_all:=”;
for i in 1..3 loop
v_chkn:=instr(v_ip,’.’,v_chkn+1);
v_tow:=substr(v_ip,v_chkt,v_chkn-v_chkt);
v_tmp:=trim(to_char(v_tow,’xx’));
if length(v_tmp)=1 then
v_all:=v_all || lpad(v_tmp,2,’0′);
else
v_all:=v_all || v_tmp;
end if;
v_chkt:=v_chkn+1;
if i=3 then
v_tow:=substr(v_ip,v_chkt,length(v_ip)-v_chkn);
v_tmp:=trim(to_char(v_tow,’xx’));
if length(v_tmp)=1 then
v_all:=v_all || lpad(v_tmp,2,’0′);
else
v_all:=v_all || v_tmp;
end if;
end if;
end loop;
–dbms_output.put_line(v_all);
return to_number(v_all,’xxxxxxxxxx’);
end;
————————-
set serveroutput on
exec sp_ipconvernum(‘192.168.8.4’)
select fun_ipconvernum(‘255.255.255.255′) from dual;

——————————————————————–
–数字转换为ip函数
——————————————————————–
create or replace function fun_numconverip(v_nip number)
return varchar2
as
v_ip varchar2(32);
v_iphex varchar2(8);
begin
v_ip:=”;
v_iphex:=trim(to_char(v_nip,’xxxxxxxxxx’));
for i in 1..4 loop
if i=4 then
v_ip:=v_ip||to_number(substr(v_iphex,(i-1)*2+1,2),’xxx’);
else
v_ip:=v_ip||to_number(substr(v_iphex,(i-1)*2+1,2),’xxx’)||’.’;
end if;
end loop;
return v_ip;
end;

—————————————————————–
select fun_numconverip(3232237572) from dual;
—————————————————————–
declare
v_tmp varchar2(8);
v_all varchar2(32);
begin
v_all:=”;
v_tmp:=trim(to_char(3232237572,’xxxxxxxxxx’));
dbms_output.put_line(v_tmp);
for i in 1..4 loop
v_all:=v_all||to_number(substr(v_tmp,(i-1)*2+1,2),’xxx’)||’.’;
end loop;
dbms_output.put_line(v_all);
end;

select length(to_number(‘c0′,’xxx’)) from dual;
[/mw_shl_code]