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

king 系统安装 2023-02-27 677浏览 0

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;
/