Oracle中SQL语句转化IP地址到数字

CREATE OR REPLACE FUNCTION ip_num(ipaddress IN VARCHAR2) RETURN NUMBER AS

  ipnum NUMBER := 0;

  pos1  NUMBER := 0;

  pos2  NUMBER := 0;

BEGIN

  FOR i IN 1 .. 3 LOOP

    pos2  := to_number(instr(ipaddress, '.', 1, i));

    ipnum := ipnum + to_number(substr(ipaddress, pos1 + 1, pos2 - pos1 - 1)) *

             power(2, 8 * (4 - i));

    pos1  := pos2;

  END LOOP;

  ipnum := ipnum + to_number(substr(ipaddress, pos1 + 1));

  RETURN ipnum;

exception

  when others then

    return null;

END;

/