INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本？

Question

有没有什么好的代码可以将 IPv6 地址字符串转换为整数？使用一种格式转换 IPv4 似乎相当容易。但是，IPv6 有多种不同的地址显示格式：

• XXXX:XXXX:XXXX:XXXX::
• XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX XXXX
• :XXX:XXXX:0:0:XXXX:XXX: XXXX
• XXXX:XXX:XXXX::XXXX:XXX:XXXX
• ::ffff:XXXX:XXX （v6 格式的 IPv4）
• ::ffff:###.#.#.### （也有效v6 格式的 IPv4）

我希望能够获取这些字符串之一并将其转换为 INTEGER 以进行 IP 到网络匹配，并允许使用这些格式中的任何一种作为输入。

Answer 1

最终我自己滚动了。还意识到 Oracle 的 126 位 INTEGER 对于 IPv6 的 128 位地址来说不够位。坦率地说，考虑到我从未听说过 16 字节整数，我不知道原始 C 库的 INET6_ATON （或 INET_PTON）是如何做到的。

我最终得到了一个 32 字节的十六进制字符串，这意味着我必须在 nettohex 上进行一些奇特的“半字符串”数学计算，并使用 SUBSTR 以使 FBI 正常工作。 （Blasted PL/SQL 不允许“RETURN CHAR(32)”...）

不过，总体而言，它运行良好，适用于所有格式，并且允许基于索引的字符比较来查明 IP 地址是否为在一个IP范围内。

下面是完整的代码：

CREATE OR REPLACE FUNCTION ipguess(
   ip_string IN VARCHAR2
) RETURN NATURAL
DETERMINISTIC
IS
BEGIN
   -- Short-circuit the most popular, and also catch the special case of IPv4 addresses in IPv6
   IF    REGEXP_LIKE(ip_string, '\d{1,3}(\.\d{1,3}){3}')                       THEN RETURN 4;
   ELSIF REGEXP_LIKE(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}') THEN RETURN 6;
   ELSE                                                                             RETURN NULL;
   END IF;
END ipguess;

CREATE OR REPLACE FUNCTION iptohex(
   ip_string IN VARCHAR2
) RETURN CHAR     -- INTEGER only holds 126 binary digits, IPv6 has 128
DETERMINISTIC
IS
   iptype NATURAL := ipguess(ip_string);
   ip     VARCHAR2(32);
   ipwork VARCHAR2(64);
   d      INTEGER;
   q      VARCHAR2(3);
BEGIN
   IF    iptype = 4 THEN
      -- Sanity check
      ipwork := REGEXP_SUBSTR(ip_string, '\d{1,3}(\.\d{1,3}){3}');
      IF ipwork IS NULL THEN RETURN NULL; END IF;

      -- Starting prefix
      -- NOTE: 2^48 - 2^32 = 281470681743360 = ::ffff:0.0.0.0
      --       (for compatibility with IPv4 addresses in IPv6)
      ip := '00000000000000000000ffff';

      -- Parse the input
      WHILE LENGTH(ipwork) IS NOT NULL
      LOOP
         d := INSTR(ipwork, '.');  -- find the dot
         IF d > 0 THEN             -- isolate the decimal octet
            q      := SUBSTR(ipwork, 1, d - 1);
            ipwork := SUBSTR(ipwork, d + 1);
         ELSE
            q      := ipwork;
            ipwork := '';
         END IF;

         -- convert to a hex string
         ip := ip || TO_CHAR(TO_NUMBER(q), 'FM0x');

      END LOOP;
   ELSIF iptype = 6 THEN
      -- Short-circuit "::" = 0
      IF ip_string = '::' THEN RETURN LPAD('0', 32, '0'); END IF;

      -- Sanity check
      ipwork := REGEXP_SUBSTR(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}');
      IF ipwork IS NULL THEN RETURN NULL; END IF;

      -- Replace leading zeros
      -- (add a bunch to all of the pairs, then remove only the required ones)
      ipwork := REGEXP_REPLACE(ipwork, '(^|\:)([[:xdigit:]]{1,4})', '\1000\2');
      ipwork := REGEXP_REPLACE(ipwork, '(^|\:)0+([[:xdigit:]]{4})',    '\1\2');

      -- Groups of zeroes
      -- (total length should be 32+Z, so the gap would be the zeroes)
      ipwork := REPLACE(ipwork, '::', 'Z');
      ipwork := REPLACE(ipwork, ':');
      ipwork := REPLACE(ipwork, 'Z', LPAD('0', 33 - LENGTH(ipwork), '0'));
      ip     := LOWER(ipwork);
   ELSE
      RETURN NULL;
   END IF;

   RETURN ip;

END iptohex;

CREATE OR REPLACE FUNCTION nettohex(
   ip_string IN VARCHAR2,
   cidr      IN NATURALN,
   is_end    IN SIGNTYPE DEFAULT 0
) RETURN CHAR
DETERMINISTIC
IS
   iptype   NATURAL  := ipguess(ip_string);
   iphex    CHAR(32) := iptohex(ip_string);
   iphalf1  CHAR(16) := SUBSTR(iphex, 1, 16);
   iphalf2  CHAR(16) := SUBSTR(iphex, 17);
   ipwork   CHAR(16) := iphalf2;
   cidr_exp INTEGER  := 2 ** (iptype + 1) - cidr;
   ipint    INTEGER;
   subnet   INTEGER;
   is_big   SIGNTYPE := 0;
BEGIN
   -- Sanity checks
   IF    iptype IS NULL THEN RETURN NULL;
   ELSIF iphex  IS NULL THEN RETURN NULL;
   END IF;

   IF    cidr_exp >= 64  THEN is_big := 1;
   ELSIF cidr_exp = 0    THEN RETURN iphex;  -- the exact IP, such as /32 on IPv4
   ELSIF cidr_exp <  0   THEN RETURN NULL;
   ELSIF cidr_exp >  128 THEN RETURN NULL;
   END IF;

   -- Change some variables around if we are working with the first/largest half
   IF is_big = 1 THEN
      ipwork   := iphalf1;
      iphalf2  := TO_CHAR((2 ** 64 - 1) * is_end, 'FM0xxxxxxxxxxxxxxx');  -- either all 0 or all F
      cidr_exp := cidr_exp - 64;
   END IF;

   -- Normalize IP to divisions of CIDR
   subnet := 2 ** cidr_exp;
   ipint  := TO_NUMBER(ipwork, 'FM0xxxxxxxxxxxxxxx');
   -- if is_end = 1 then add one net range (then subtract one IP) to get the ending range
   ipwork := TO_CHAR(FLOOR(ipint / subnet + is_end) * subnet - is_end, 'FM0xxxxxxxxxxxxxxx');

   -- Re-integrate
   IF is_big = 0 THEN iphalf2 := ipwork;
   ELSE               iphalf1 := ipwork;
   END IF;

   RETURN SUBSTR(iphalf1 || iphalf2, 1, 32);

END nettohex;

-- WHERE clause:
-- 1. BETWEEN compare:
--    iptohex(a.ip_addy) BETWEEN nettohex(b.net_addy, b.cidr, 0) AND nettohex(b.net_addy, b.cidr, 1)
--
--    Requires three function-based indexes, but all of them would work, as they are all inside the tables.
--
-- 2. CIDR match:
--    nettohex(a.ip_addy, b.cidr) = nettohex(b.net_addy, b.cidr)
--
--    Only two functions and uses exact match, but first one requires an outside variable.  Last one would be only function-based index.
--    An FBI of iptohex(a.ip_addy) could be implemented, but it's questionable if nettohex would use that index.
--
-- Recommended FBIs:
--
-- (SUBSTR(iptohex(a.ip_addy), 1, 32))
-- (SUBSTR(nettohex(b.ip_addy, b.cidr, 0), 1, 32), SUBSTR(nettohex(b.ip_addy, b.cidr, 1), 1, 32))
--
-- NOTE: Will need to use the SUBSTR form for the above WHERE clauses!

更新： Oracle 11g 确实允许将 SUBSTR 条目放入虚拟列。因此，您可以拥有这样的列：

ip              VARCHAR2(39),
cidr            NUMBER(2),
ip_hex          AS (SUBSTR(iptohex(ip),           1, 32)) VIRTUAL,
ip_nethex_start AS (SUBSTR(nettohex(ip, cidr, 0), 1, 32)) VIRTUAL,
ip_nethex_end   AS (SUBSTR(nettohex(ip, cidr, 1), 1, 32)) VIRTUAL,

和索引：

CREATE INDEX foobar_iphex_idx ON foobar (ip_hex);
CREATE INDEX foobar_ipnet_idx ON foobar (ip_nethex_start, ip_nethex_end);

使用 WHERE 子句，例如：

a.ip_hex BETWEEN b.ip_nethex_start AND b.ip_nethex_end
nettohex(a.ip, b.cidr) = b.ip_nethex_start  -- not as effective