oracle中ip带转数字,【PL/SQL】IP与数字互转

--查看过程、函数视图

dba_source user_source all_source

--删除存储过程

drop procedure SP_TMP;

drop function fun_tmp;

---函数

to_char：转换数据这字符串，也可以把10进制转换为16进制(注意xx的个数)。SQL> select to_char(100,'xx') from dual;

TO_

---

64

to_number：字符串转为数字，也可以16进制转为10进制。SQL> select to_number('1f','xx') from dual;

TO_NUMBER('1F','XX')

--------------------

31

substr：从字符串截取字符。可以从0或1开始，结果一样。SQL> select substr('abcdefg',1,2) from dual;

SU

--

ab

SQL> select substr('abcdefg',0,2) from dual;

SU

--

ab

--------------------------------------------------------------------

--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;

-----------------------------------------------------------------