create or replace function ip2number(ip varchar2) return number is ip_num_hex varchar2(80); begin if (regexp_like(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$')) then ip_num_hex := lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\1'), 'XX')),2,'0') || lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\2'), 'XX')),2,'0') || lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\3'), 'XX')),2,'0') || lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\4'), 'XX')),2,'0');
return to_number(ip_num_hex, 'XXXXXXXX'); else return -1; end if; exception when others then return -99999999999; end;select ip2number('169.254.55.6') from dual;IP2NUMBER('169.254.55.6') ------------------------- 2852009734先来一个
create or replace function number2ip(num number) return varchar2 is ip_num_hex varchar2(8); begin ip_num_hex := lpad(trim(to_char(num, 'XXXXXXXX')), 8, '0'); return to_number(substr(ip_num_hex, 1, 2), 'XX') || '.' || to_number(substr(ip_num_hex, 3, 2), 'XX') || '.' || to_number(substr(ip_num_hex, 5, 2), 'XX') || '.' || to_number(substr(ip_num_hex, 7, 2), 'XX'); exception when others then dbms_output.put_line(sqlerrm); return null; end;select number2ip(2852009734) from dual;NUMBER2IP(2852009734) -------------------------------------------------------------------------------- 169.254.55.6
0xA9 对应 169
0xFE 对应 254
0x37 对应 55
0x6 对应 06
create or replace function ip2number(ip varchar2)
return number
is
ip_num_hex varchar2(80);
begin
if (regexp_like(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$')) then
ip_num_hex := lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\1'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\2'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\3'), 'XX')),2,'0') ||
lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\4'), 'XX')),2,'0');
return to_number(ip_num_hex, 'XXXXXXXX');
else
return -1;
end if;
exception
when others then
return -99999999999;
end;select ip2number('169.254.55.6') from dual;IP2NUMBER('169.254.55.6')
-------------------------
2852009734先来一个
create or replace function number2ip(num number)
return varchar2 is
ip_num_hex varchar2(8);
begin
ip_num_hex := lpad(trim(to_char(num, 'XXXXXXXX')), 8, '0');
return to_number(substr(ip_num_hex, 1, 2), 'XX') || '.' ||
to_number(substr(ip_num_hex, 3, 2), 'XX') || '.' ||
to_number(substr(ip_num_hex, 5, 2), 'XX') || '.' ||
to_number(substr(ip_num_hex, 7, 2), 'XX');
exception
when others then
dbms_output.put_line(sqlerrm);
return null;
end;select number2ip(2852009734) from dual;NUMBER2IP(2852009734)
--------------------------------------------------------------------------------
169.254.55.6