oracle_ip地址的分开

xuzhou2013 发布于 2013/11/19 09:51
阅读 117
收藏 1

有一个表,放的是ip地址的范围,

现在想要拆分ip,

比如: 第一行 可以拆分成很多个ip地址.(192.168.200.1,192.168.200.2,...,192.168.200.255)

请教:  可以使用oracle来获得吗?

(java 能处理,但是如果太多的话,java处理就比较慢)

加载中
0
remielxf
remielxf

select id,REGEXP_SUBSTR(begin_ip, '[^,]+', 1, l) begin_ip from ip_range ,(SELECT LEVEL l
                                          FROM DUAL
                                        CONNECT BY LEVEL <= 30)
                     WHERE l <= LENGTH(begin_ip) -
                                       LENGTH(REPLACE(begin_ip, ',') ) + 1

0
canghailan
canghailan

create or replace function ip_to_number(ip in varchar2) return number is
  Result number;
begin
  Result := to_number(regexp_substr(ip, '[0-9]+', 1, 1));
  Result := Result * 256 + to_number(regexp_substr(ip, '[0-9]+', 1, 2));
  Result := Result * 256 + to_number(regexp_substr(ip, '[0-9]+', 1, 3));
  Result := Result * 256 + to_number(regexp_substr(ip, '[0-9]+', 1, 4));
  return(Result);
end ip_to_number;
create or replace function number_to_ip(num in number) return varchar2 is
  re    number;
  part1 number;
  part2 number;
  part3 number;
  part4 number;
begin
  re    := num;
  part4 := mod(re, 256);
  re    := (re - part4) / 256;
  part3 := mod(re, 256);
  re    := (re - part3) / 256;
  part2 := mod(re, 256);
  re    := (re - part2) / 256;
  part1 := re;
  return to_char(part1) || '.' || to_char(part2) || '.' || to_char(part3) || '.' || to_char(part4);
end number_to_ip;
select id, number_to_ip(rownum + ip_to_number(begin_ip) - 1)
  from (select * from ip_range where id = 'ip001')
connect by rownum <= ip_to_number(end_ip) - ip_to_number(begin_ip) + 1


0
canghailan
canghailan
这种纯数值运算Java也不慢啊
0
IdleMan
IdleMan
java拆分可以节省网络带宽
返回顶部
顶部