IP-адрес хранится в десятичном формате - PL / SQL для отображения в виде четырехугольника с точками

Asked
Viewd10558

4

У нас есть база данных Oracle, содержащая IP-адреса, хранящиеся в виде десятичных целых чисел - это невероятно болезненно при манипулировании данными вручную, а не через веб-интерфейс, но ручные манипуляции действительно удобны, поскольку сетевые специалисты постоянно просят нас сделать странные то, чего не ожидали авторы веб-интерфейса.

Может ли кто-нибудь предоставить мне PL / SQL или другой метод для отображения этих десятичных IP-адресов в виде десятичных чисел с точками, т.е. в формате 123.123.123.123?

Т.е. Я хотел бы иметь возможность выполнять такой запрос, как:

 select hostname, inttoip(ip_address) from host;
 

и пусть процедура inttoip() отображает ip_address как 203.30.237.2 вместо 3407801602.

В идеале мне нужна процедура, которая также предоставляет обратную функцию, например

 insert into host (hostname,ip_address) values ('some-hostname', iptoint('203.30.237.2'));
 

У меня есть perl для этого, но моих знаний PL / SQL / Oracle недостаточно, чтобы перенести его на PL / SQL.


Альтернативно способ запустить Perl как процедурный язык в контексте оракула, аналогичный следующему в postgres:

 CREATE FUNCTION perl_func (integer) RETURNS integer AS $$
 <some perl>
$$ LANGUAGE plperl;
 

Было бы здорово - если возможно - возможно, даже лучше, если бы я мог выполнять множество процедурных вещей в Oracle на языке, с которым я знаком.

  • Внутри Oracle есть JVM. Существует ли версия perl, которая может работать внутри JVM? В таком случае вы можете использовать perl. Например, можно запустить Jython с Oracle JVM. Читайте здесь: http://forums.oracle.com/forums/thread.jspa?threadID=492882&tstart=15

    tuinstoel05 июля 2009, 17:11

3 ответов

0
     -- INET ATON en INET NTOA and helper function GET TOKEN

        CREATE OR REPLACE function inet_ntoa (ip integer) return varchar2
    is
       ip1 integer;
       ip2 integer;
       ip3 integer;
       ip4 integer;
       ipi integer := ip;
    begin
       ip1 := floor(ipi/power(2,24));
       ipi := ipi - (ip1*power(2,24));
       ip2 := floor(ipi/power(2,16));
       ipi := ipi - (ip2*power(2,16));
       ip3 := floor(ipi/power(2,8));
       ipi := ipi - (ip3*power(2,8));
       ip4 := ipi;
       return ip1||'.'||ip2||'.'||ip3||'.'||ip4;

    end;
    /    

CREATE OR REPLACE FUNCTION get_token (the_list VARCHAR2,the_index NUMBER, delim VARCHAR2 := '.') RETURN VARCHAR2
        IS
           start_pos   INTEGER;
           end_pos     INTEGER;
        BEGIN
           IF the_index = 1 THEN
              start_pos := 1;
           ELSE
              start_pos := INSTR (the_list, delim, 1, the_index - 1);
              IF start_pos = 0 THEN
                 RETURN NULL;
              ELSE
                 start_pos := start_pos + LENGTH (delim);
              END IF;
           END IF;
           end_pos := INSTR (the_list, delim, start_pos, 1);
           IF end_pos = 0 THEN
              RETURN SUBSTR (the_list, start_pos);
           ELSE
              RETURN SUBSTR (the_list, start_pos, end_pos - start_pos);
           END IF;
        END get_token;
        /




    CREATE OR REPLACE function inet_aton (ip varchar2) return integer
    is
       invalid_ip_adres exception;
       pragma exception_init(invalid_ip_adres,-6502);
       ipi integer;
    begin
       ipi := get_token(ip,4)
            +(get_token(ip,3)*power(2,8))
                    +(get_token(ip,2)*power(2,16))
                    +(get_token(ip,1)*power(2,24));
       return ipi;
    exception
       when invalid_ip_adres
       then 
          return null;
    end;
    /
 
2
 CREATE OR REPLACE
FUNCTION inttoip(ip_address IN INTEGER) RETURN VARCHAR2 IS
  v8 VARCHAR2(8);
BEGIN
  -- 1. convert the integer into hexadecimal representation
  v8 := TO_CHAR(ip_address, 'FMXXXXXXXX');
  -- 2. convert each XX portion back into decimal
  RETURN to_number(substr(v8,1,2),'XX')
       || '.' || to_number(substr(v8,3,2),'XX')
       || '.' || to_number(substr(v8,5,2),'XX')
       || '.' || to_number(substr(v8,7,2),'XX');
END inttoip;

CREATE OR REPLACE
FUNCTION iptoint(ip_string IN VARCHAR2) RETURN INTEGER IS
  d1 INTEGER;
  d2 INTEGER;
  d3 INTEGER;
  q1 VARCHAR2(3);
  q2 VARCHAR2(3);
  q3 VARCHAR2(3);
  q4 VARCHAR2(3);
  v8 VARCHAR2(8);
BEGIN
  -- 1. parse the input, e.g. '203.30.237.2'
  d1 := INSTR(ip_string,'.');     -- first dot
  d2 := INSTR(ip_string,'.',1,2); -- second dot
  d3 := INSTR(ip_string,'.',1,3); -- third dot
  q1 := SUBSTR(ip_string, 1, d1 - 1);           -- e.g. '203'
  q2 := SUBSTR(ip_string, d1 + 1, d2 - d1 - 1); -- e.g. '30'
  q3 := SUBSTR(ip_string, d2 + 1, d3 - d2 - 1); -- e.g. '237'
  q4 := SUBSTR(ip_string, d3 + 1);              -- e.g. '2'
  -- 2. convert to a hexadecimal string
  v8 := LPAD(TO_CHAR(TO_NUMBER(q1),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q2),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q3),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q4),'FMXX'),2,'0');
  -- 3. convert to a decimal number
  RETURN TO_NUMBER(v8, 'FMXXXXXXXX');
END iptoint;
 
6

Это та функция, которая вам нужна:

 create or replace
function inttoip(ip_address integer) return varchar2
deterministic
is
begin
    return to_char(mod(trunc(ip_address/256/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256),256))
           ||'.'||to_char(mod(ip_address,256));
end;
 

(Комментарии о том, как сделать функцию детерминированной и использовать to_char приняты на вооружение - спасибо).

В Oracle 11G вы можете сделать форматированный IP-адрес виртуальным столбцом в таблице хостов:

 alter table host
add formatted_ip_address varchar2(15)
generated always as
( to_char(mod(trunc(ip_address/256/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256),256))
          ||'.'||to_char(mod(ip_address,256))
) virtual;
 

При необходимости этот столбец можно было бы индексировать для запросов.

Ваш запрос выглядит следующим образом:

 select hostname, formatted_ip_address from host;
 
  • Думаю, я бы и сам использовал явное преобразование числа в символы.

    David Aldridge06 июля 2009, 07:06
  • Вы даже можете определить MOD (… ..) как столбец в представлении над базовой таблицей (таблицами), избегая уровня PL / SQL. 11g может быть виртуальным столбцом.

    Gary Myers05 июля 2009, 23:13
  • Вы можете объявить эту функцию детерминированной. Это позволяет использовать эту функцию в индексе на основе функции (fbi). Использование ФБР может решить проблемы с производительностью.

    tuinstoel05 июля 2009, 17:17