oracle函数ip地址转换,求一个函数：将IP地址/掩码 转换成 起始IP 终止IP的，谢谢。...

现写一个凑合用了。

select prefix2segment('58.207.64.10',18) from dual

PREFIX2SEGMENT('58.207.64.10',18)

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

58.207.64.1,58.207.127.254

CREATE OR REPLACE FUNCTION prefix2segment (ip_address VARCHAR2 ,prefix VARCHAR2) RETURN VARCHAR2 is

/**

名称 prefix2segment

输入 58.207.64.10  18

输出 58.207.64.1  58.207.127.255

测试 select prefix2segment('58.207.64.10',18) from dual;

BITAND(x,y)

BITOR(x,y) = (x + y) - BITAND(x, y);

BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;

start_ip

end_ip

**/

v_return  VARCHAR2(4000);

v_comma1  INTEGER;

v_comma2  INTEGER;

v_comma3  INTEGER;

v_length  INTEGER;

v_ipaddr1 number(3);

v_ipaddr2 number(3);

v_ipaddr3 number(3);

v_ipaddr4 number(3);

v_prefix_tmp    VARCHAR2(32);

v_prefix_tmp1    VARCHAR2(32);

v_prefix_tmp2    VARCHAR2(32);

v_prefix_b1 VARCHAR2(18);

v_prefix_b2 VARCHAR2(18);

v_prefix_b3 VARCHAR2(18);

v_prefix_b4 VARCHAR2(18);

v_prefix1 number(3);

v_prefix2 number(3);

v_prefix3 number(3);

v_prefix4 number(3);

v_netmask1 number(3);

v_netmask2 number(3);

v_netmask3 number(3);

v_netmask4 number(3);

v_broadcast1 number(3);

v_broadcast2 number(3);

v_broadcast3 number(3);

v_broadcast4 number(3);

v_startip VARCHAR2(15);

v_endip   VARCHAR2(15);

begin

v_prefix_tmp:='00000000000000000000000000000000' ;

v_length:=LENGTH(ip_address);

v_comma1:=INSTR(ip_address,'.',1,1);

v_comma2:=INSTR(ip_address,'.',1,2);

v_comma3:=INSTR(ip_address,'.',1,3);

v_ipaddr1:=TO_NUMBER(SUBSTR(ip_address,1,v_comma1-1));

v_ipaddr2:=TO_NUMBER(SUBSTR(ip_address,v_comma1+1,v_comma2-v_comma1-1));

v_ipaddr3:=TO_NUMBER(SUBSTR(ip_address,v_comma2+1,v_comma3-v_comma2-1));

v_ipaddr4:=TO_NUMBER(SUBSTR(ip_address,v_comma3+1,v_length-v_comma3));

v_prefix_tmp1:=substr(v_prefix_tmp,1,prefix);

v_prefix_tmp2:=substr(v_prefix_tmp,prefix+1,32-length(v_prefix_tmp1));

v_prefix_tmp :=replace(v_prefix_tmp1,0,1)||v_prefix_tmp2;

v_prefix_b1:=substr(v_prefix_tmp,1,8);

v_prefix_b2:=substr(v_prefix_tmp,9,8);

v_prefix_b3:=substr(v_prefix_tmp,17,8);

v_prefix_b4:=substr(v_prefix_tmp,25,8);

v_prefix1:=F_BIN_TO_DEC(v_prefix_b1);

v_prefix2:=F_BIN_TO_DEC(v_prefix_b2);

v_prefix3:=F_BIN_TO_DEC(v_prefix_b3);

v_prefix4:=F_BIN_TO_DEC(v_prefix_b4);

v_netmask1:=bitand(v_ipaddr1,v_prefix1);

v_netmask2:=bitand(v_ipaddr2,v_prefix2);

v_netmask3:=bitand(v_ipaddr3,v_prefix3);

v_netmask4:=bitand(v_ipaddr4,v_prefix4);

--BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;

v_broadcast1:=255-((v_netmask1+v_prefix1)-2*bitand(v_netmask1,v_prefix1));

v_broadcast2:=255-((v_netmask2+v_prefix2)-2*bitand(v_netmask2,v_prefix2));

v_broadcast3:=255-((v_netmask3+v_prefix3)-2*bitand(v_netmask3,v_prefix3));

v_broadcast4:=255-((v_netmask4+v_prefix4)-2*bitand(v_netmask4,v_prefix4));

v_startip:=to_char(v_netmask1)||'.'||to_char(v_netmask2)||'.'||to_char(v_netmask3)||'.'||to_char(v_netmask4+1);

v_endip  :=to_char(v_broadcast1)||'.'||to_char(v_broadcast2)||'.'||to_char(v_broadcast3)||'.'||to_char(v_broadcast4-1);

/**

v_return:=v_ipaddr1||','||v_ipaddr2||','||v_ipaddr3||','||v_ipaddr4||'***'||

+v_prefix1||','||v_prefix2||','||v_prefix3||','||v_prefix4||'***'||

+v_netmask1||','||v_netmask2||','||v_netmask3||','||v_netmask4||'***'||

+v_broadcast1||','||v_broadcast2||','||v_broadcast3||','||v_broadcast4||'***'||v_startip||','||v_endip;

**/

v_return:=v_startip||','||v_endip;

RETURN v_return;

END;

/

转载：

create FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS

v_return  VARCHAR2(4000);

BEGIN

SELECT SUM(data1) INTO v_return

FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1

FROM dual

CONNECT BY rownum <= length(p_str));

RETURN v_return;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END f_bin_to_dec;