oracle ip字符与ip数值之间的相互转换

字符ip向数值转换：

1 create or replace function f_ip2number (v_ip in varchar2) return number is
2 i number;
3 begin
4   i:=regexp_substr(v_ip,'(\d{1,3})',1,1)*16777216 
5      +regexp_substr(v_ip,'(\d{1,3})',1,2)*65536
6      +regexp_substr(v_ip,'(\d{1,3})',1,3)*256
7      +regexp_substr(v_ip,'(\d{1,3})',1,4);
8    return i;
9 end;

效果如下：

select f_ip2number('122.231.24.7') from dual;

2061965319

 

数值ip向字符转换：

 1 create or replace function f_number2ip(num number)  
 2 return varchar2 is  
 3   ip_num_hex varchar2(8);  
 4 begin  
 5   ip_num_hex := lpad(trim(to_char(num, 'XXXXXXXX')), 8, '0');  
 6   return to_number(substr(ip_num_hex, 1, 2), 'XX') || '.' ||  
 7              to_number(substr(ip_num_hex, 3, 2), 'XX') || '.' ||  
 8              to_number(substr(ip_num_hex, 5, 2), 'XX') || '.' ||  
 9              to_number(substr(ip_num_hex, 7, 2), 'XX');  
10 exception  
11 when others then  
12   dbms_output.put_line(sqlerrm);  
13   return null;  
14 end;

效果如下：

select f_number2ip(2061965319) from dual;

122.231.24.7

posted @ 2015-08-07 23:07  三杯茶  阅读(744)  评论(0)    收藏  举报